CODEWITHSUNDEEP
vbaexcel
camelCaseCowboy
camelCaseCowboy

Reputation: 986

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

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.

Upvotes: 4

Views: 317

Answers (2)

findwindow
findwindow

Reputation: 3153

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

Upvotes: 4

Sean Meyer
Sean Meyer

Reputation: 113

You're defining a outside of the loop, so i is set to 0. I'm guessing that is not what you intended, because in that case doing 7 + i is pointless. I would add Debug.Print a to make sure it's being set to the right thing.

Upvotes: 2

Related Questions