Looping through specific sheets in VBA and copy/paste

Question

I am trying to loop through specific sheets in Excel and have the formula in A1 paste through the last row of data. The code below works for the first sheet that is listed, however, it does not carry over to subsequent worksheets.

Sub Refresh_ActivesheetB36()

Dim lastrow As Long
Dim MyArray As Variant
Dim i As Integer

Application.ScreenUpdating = False

Sheets("GroupInfo").Select
    Range("B36").Select
    Selection.Formula = "=COUNTIF('TAX INFO'!E15:E1499,"">0"")"
    
MyArray = Array("DATA Member", "DATA Sch A")

With Worksheets(MyArray)
    lastrow = Cells(Rows.Count, "D").End(xlUp).Row
End With

On Error Resume Next
For i = LBound(MyArray) To UBound(MyArray)
    With Worksheets(MyArray(i))
        Range("A1").Select
        Range("A1:A" & lastrow).PasteSpecial
    End With
    Next i
On Error GoTo 0
            
Application.ScreenUpdating = True
  Worksheets("GroupInfo").Select
    
End Sub

Add a period (`.`) before `Cells` and `Range` in the `With` statements. Side note: it's usually a good idea to [avoid using Select in your code](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — cybernetic.nomad, Jan 10 '22 at 16:21
Adding a . cause an error, select method of range class failed — TDorman, Jan 10 '22 at 16:26
It's erroring because you can't select a range on a non-active worksheet. You're going to want to remove all `Select` from your code. As an aside your `With Worksheets(MyArray)` isn't going to work, you need to iterate through the array (it isn't throwing an error now because the last row equation is using active not the with). You also aren't copying anything, just pasting whatever happens to be in the clipboard. — Warcupine, Jan 10 '22 at 16:40

score 2 · Accepted Answer · answered Jan 10 '22 at 19:08

Copy Formula in Multiple Worksheets

Qualify the objects: the ranges (dws.Range..., gws.Range...) and the worksheets (wb.Worksheets...).

Option Explicit

Sub Refresh_ActivesheetB36()

    Dim dwsNames As Variant: dwsNames = Array("DATA Member", "DATA Sch A")

    Application.ScreenUpdating = False

    Dim wb As Workbook: Set wb = ThisWorkbook
    
    Dim gws As Worksheet: Set gws = wb.Worksheets("GroupInfo")
    gws.Range("B36").Formula = "=COUNTIF('TAX INFO'!E15:E1499,"">0"")"

    Dim dws As Worksheet
    Dim dlRow As Long
    Dim d As Long
    
    For d = LBound(dwsNames) To UBound(dwsNames)
        On Error Resume Next
        Set dws = wb.Worksheets(dwsNames(d))
        On Error GoTo 0
        If Not dws Is Nothing Then
            dlRow = dws.Range("D" & dws.Rows.Count).End(xlUp).Row
            dws.Range("A1").Copy dws.Range("A1:A" & dlRow)
            Set dws = Nothing
        End If
    Next d
    
    Application.ScreenUpdating = True
    gws.Activate

End Sub

Looping through specific sheets in VBA and copy/paste

1 Answers1

Copy Formula in Multiple Worksheets