Copy and Paste a set range in the next empty row_LOOP

Question

I am new to macro and I am struggling with creating macro that will allow me to copy and paste the same range of cells from all sheets in worksheet and paste them in the first sheet in the next available cell. I know that is has to be done with the combination of loop and lastrow. Unfortunately, all my attempts fail

This is the macro that I would like to run through all sheet, but the sheets name is different

Sub Macro10()
'
' Macro10 Macro
'

'
    Sheets("1449GW.WLWaterLevel.0sec").Select
    Range("H1:Y2").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    ActiveSheet.Paste
End Sub

I recommend to read: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Nov 16 '18 at 12:10

SJR · Accepted Answer · 2018-11-16T11:58:10.017

Try this. Two versions as not sure what you're asking.

If you are copying the same range from a single sheet to multiple sheets

Sub Macro10()

Dim ws As Worksheet, ws1 As Worksheet

Set ws1 = Worksheets("1449GW.WLWaterLevel.0sec")

For Each ws In Worksheets
    If ws.Name <> ws1.Name Then
        ws1.Range("H1:Y2").Copy ws.Range("A" & Rows.Count).End(xlUp)(2)
    End If
Next ws

End Sub

If you are copying the same range from multiple sheets to a single sheet

Sub Macro10()

Dim ws As Worksheet, ws1 As Worksheet

Set ws1 = Worksheets("Sheet1")

For Each ws In Worksheets
    If ws.Name <> ws1.Name Then
        ws.Range("H1:Y2").Copy ws1.Range("A" & Rows.Count).End(xlUp)(2)
    End If
Next ws

End Sub

It works only for the first sheet named '1449GW.WLWaterLevel.0sec' in the worksheet. I have 50 more in the same worksheet with different names — Marta Po, Nov 16 '18 at 12:45

Copy and Paste a set range in the next empty row_LOOP

1 Answers1