Excel VBA- Trying to copy values to summary sheet based on a value

Question

In my file I have a list of sheet names that correspond with values in column A in a summary page. I want to pull, from each sheet, the value in cell B6 and paste it in the corresponding row of that client in the summary sheet. I've been trying to look up which to copy and paste from based on the value in column A by copying from the sheet that is named with the value of the text of column A, but I'm getting a "Subscript out of range" error at the Worksheets(a).Range("B6").Copy issue. Code below:

Sub columnfiller()
Dim i As Integer

Dim a As String

a = Worksheets(2).Cells(7 + i, "A").Text

For i = 3 To Worksheets.Count
Worksheets(a).Range("B6").Copy
ActiveSheet.Paste Destination:=Worksheets(2).Cells(7 + i)
Next i
End Sub

7+i is the reference to the first cell in the table.

findwindow findwindow · Accepted Answer · 2016-04-29T16:18:15

Untested. I think this is what you want. Instead of looping worksheets, I looped down your column A.

Sub columnfiller()

Dim lastRow As Long
Dim summaryWs As Worksheet
Dim ws As String

Set summaryWs = WorkSheets(2)

With summaryWs

    lastRow = .Cells(.Rows.count, "A").End(xlUp).Row

    For I = 7 To lastRow 'you didn't initiate i so guessing first row is 7

        ws = .Cells(I, 1)
        .Cells(I, 2) = WorkSheets(ws).Range("B6") 'not sure if you need formatting. this does not have formatting

    Next

End With

End Sub

Excel VBA- Trying to copy values to summary sheet based on a value

2 Answers