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]

What do you mean by the "last version of the code"?

 

[pliskers]

The one you posted "Try it like" 49 minutes ago - 5 comments up. Just need the copy/paste of the contents of A1:B1 (which are formulas) before the paste values step.

 

[Fluff]

Ok, try

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)
         ws.Range("A4:B" & lastRow).Value = ws.Range("A4:B" & lastRow).Value
         
Next ws

End Sub

 

[pliskers]

Ok, try

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)
         ws.Range("A4:B" & lastRow).Value = ws.Range("A4:B" & lastRow).Value
        
Next ws

End Sub

Perfect! I'm so grateful for your help!

 

[Fluff]

You're welcome & thanks for the feedback.