How to make a macro work for multiple sheets?

Question

I have code which copies a range and transfers the values in a next blank row continuously, for a single sheet.

I want the same done in multiple worksheets in a single go.

How to assign multiple sheet names?

Option Explicit
Dim myTimer As Date

Sub GetMyData1()
    Application.ScreenUpdating = False
    Dim lastrow As Long, nextblankrow As Long
    myTimer = Now + TimeValue("00:02:00")
    Application.OnTime myTimer, "GetMyData1"
    Dim rng1 As Range
    Set rng1 = Worksheets("Adata").Range("A2:P2")
    rng1.Copy
    lastrow = Sheets("Adata").Range("A" & Rows.Count).End(xlUp).Row
    nextblankrow = lastrow + 1
    Sheets("Adata").Range("A" & nextblankrow).PasteSpecial xlPasteValues
    ActiveWorkbook.RefreshAll
    Application.ScreenUpdating = True
End Sub

Does this answer your question? [Loop through all worksheets in workbook](https://stackoverflow.com/questions/39581487/loop-through-all-worksheets-in-workbook) — braX, Dec 25 '19 at 10:28
Thanks for responding. There were no sheet names assigned, so i cant take the help of it. if the sheet names were assigned as a list then it would have been easier to modify it. Thank you. — kopsy, Dec 27 '19 at 05:36

score 1 · Accepted Answer · edited Jan 10 '21 at 14:44

1

The comments correctly point to this answer as a solution.

You can loop through worksheets in a workbook. I included some ideas that might help you organize your code so that if something changes (i.e. the range of columns or the 2 minute time) you can update.

I also included a condition in case you don't want to apply code to all worksheets.

Sub GetMyData1()
    Const timerAmount As String = "00:02:00"
    Const rng1Address As String = "A2:P2"
    
    Dim WS As Worksheet, lastrow As Long, nextblankrow As Long, myTimer As Double
    Dim rng1 As Range
    
    For Each WS In ThisWorkbook.Worksheets
    
        'if you don't want to apply to ALL sheets, use some kind of if-statement to hold it.
        If WS.Name = "Adata" Or WS.Name = "something else" Then
            Application.ScreenUpdating = False
            
            Application.OnTime Now + TimeValue(timerAmount), "GetMyData1"
            
            Set rng1 = WS.Range(rng1Address)
            rng1.Copy
            lastrow = WS.Range("A" & Rows.Count).End(xlUp).Row
            nextblankrow = lastrow + 1
            WS.Range("A" & nextblankrow).PasteSpecial xlPasteValues
                
            ActiveWorkbook.RefreshAll
            Application.ScreenUpdating = True
        End If
    Next WS
    
End Sub

edited Jan 10 '21 at 14:44

Community

1
1

answered Dec 26 '19 at 01:31

pgSystemTester

8,979
2
23
49

Thanks for responding! Was a bit in celebration mood didn't have time to test the code immediately. when i initially test to run this code manually it worked flawlessly, but later it gave a me a peculiar problem when it tried to run on its own automatically for every 2minutes. It is pasting multiple times, increasing the number of pastes for every run, soon it pastes innumerous times in rows and thus the excel gets to a freezing point, where i have to close the excel from the task manager. I just added sheet names to the code, please check – kopsy Dec 27 '19 at 06:10
If WS.Name = "Adata" Or WS.Name = "Bdata" Or WS.Name = "Cdata" Or WS.Name = "Ddata" Or WS.Name = "Edata" Then – kopsy Dec 27 '19 at 06:10
, This is the only change i have made, infact i wanted to add some more sheets too in the future. I'm using MSO365, running with i7,32gb ram, just mentioning for a better clarity of why it freezes. Kindly help, Thank you. – kopsy Dec 27 '19 at 06:26
Technically your question is how to loop through sheets, which the above answer does explain, so I'd appreciate it if you click `accept` on. Why it's expanding, is almost certainly due to the fact that you're running it every two minutes (why would you do that?). You might want to set that to run outside of the worksheets loop... not sure. – pgSystemTester Dec 27 '19 at 16:43

How to make a macro work for multiple sheets?

1 Answers1