Loop Through Sheets Not Working

[pliskers]

I'm trying to write code that will loop through all the sheets in a workbook and, on each sheet, copy the contents of cells A1:B1 on that sheet to from row 4 through the last row on that sheet (as defined by column C). The macro does not move through the sheets. Rather, it's performing the copy paste from each sheet but staying on the first tab. I must be missing a step. Can someone please have a look and let me know?

Thank you!

Sub Loop_()

Dim wb As ThisWorkbook
Dim ws As Worksheet
Dim TableName As String
Dim LstObj As ListObjects

For Each ws In ThisWorkbook.Worksheets
With ws

lastrow = Range("C1000000").End(xlUp).Row
ws.Range("A1:B1").Copy
Range("A4:B" & lastrow).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
lastrow = vbNullString


End With
Next ws

End Sub

 

[Fluff]

Try it like

Sub Loop_()

    Dim wb As ThisWorkbook
    Dim ws As Worksheet
    Dim TableName As String
    Dim LstObj As ListObjects

For Each ws In ThisWorkbook.Worksheets
       
        lastRow = ws.Range("C1000000").End(xlUp).Row
         ws.Range("A1:B1").Copy
         ws.Range("A4:B" & lastRow).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
Next ws

End Sub

In future please use code tags when posting code How to Post Your VBA Code it makes your code easier to read & copy, thereby increasing you chances of getting help.

 

[Joe4]

When referrring to ranges within a "With" statement, if you want them to refer to the sheet in your "With" statement (i.e. "ws"), you need to preface them with a ".", i.e.

lastrow = Range("C1000000").End(xlUp).Row

should be:

lastrow = .Range("C1000000").End(xlUp).Row

and

Range("A4:B" & lastrow).Select

should be:

.Range("A4:B" & lastrow).Select

Edit:
Fluff's shows you how you can make your code more concise, but I just wanted to explain to you the problems with the code you posted, so you hopefully do not make the same mistake again in the future, when using "With".

 

[Fluff]

One problem with

.Range("A4:B" & lastrow).Select

is that you can't select cells on a non-active sheet.

 

[Joe4]

One problem with

.Range("A4:B" & lastrow).Select

is that you can't select cells on a non-active sheet.

Ah, excellent point!

 

[pliskers]

Try it like

Sub Loop_()

    Dim wb As ThisWorkbook
    Dim ws As Worksheet
    Dim TableName As String
    Dim LstObj As ListObjects

For Each ws In ThisWorkbook.Worksheets
      
        lastRow = ws.Range("C1000000").End(xlUp).Row
         ws.Range("A1:B1").Copy
         ws.Range("A4:B" & lastRow).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
Next ws

End Sub

In future please use code tags when posting code How to Post Your VBA Code it makes your code easier to read & copy, thereby increasing you chances of getting help.

Nope, I pasted your code verbatim and it still doesn't move off the worksheet on which I kick off the code. It executes the copy/paste as though it were moving through, so I end up with the ranges from all the other tabs being pasted in sequence into the defined range on the first tab. Any other suggestions?

 

[Fluff]

That is not possible with the code I posted, unless you have something else going on.

 

[pliskers]

Got it now! I used the last version posted and it's working now. Thanks, folks!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[pliskers]

Hate to trouble you again, but there's one step I missed. I need the code to first paste the the formulas in A1:B1 into the range captured and THEN copy/paste the values. I've got other code that I've tried substituting but am getting errors. Could you tweak that last version of the code with an extra step or two please?