VBA to loop and copy

[Ody]

Hello all,

Can someone assist me in creating a macro that loops through all my worksheets (not a static number) and pulls data from cell BY36 (from all those worksheets) onto my master sheet? It would be helpful if it also listed the sheet name next to that cell containing the data from BY36.

Thanks for any assistance and saving me from doing this by cut/paste.

 

[lenze]

Untested, but try this

Sub Copy()
Dim ws As Worksheet
Dim M As Worksheet
Dim LR As Long
Set M = Sheets("Master")
For i = 1 To Sheets.Count
LR = M.Cells(Rows.Count, "A").End(xlUp).Row
If ws(i).Name <> "Master" 
    Then ws(i).Range("BY36").Copy M.Cells(LR + 1, "A")
    M.Cells(LR + 1, "B") = ws(i).Name
End If
Next i
End Sub

lenze

 

[Ody]

Hi Lenze,

The code you provided didn't work. I received an error stating the following "End If without Block If"
Thanks

 

[klarowe]

Possibly it is supposed to be:

Sub Copy()
Dim ws As Worksheet
Dim M As Worksheet
Dim LR As Long
Set M = Sheets("Master")
For i = 1 To Sheets.Count
LR = M.Cells(Rows.Count, "A").End(xlUp).Row
If ws(i).Name <> "Master" Then
    ws(i).Range("BY36").Copy M.Cells(LR + 1, "A")
    M.Cells(LR + 1, "B") = ws(i).Name
End If
Next i
End Sub

Just a guess though.

Or you should be able to write it:

Sub Copy()
Dim ws As Worksheet
Dim M As Worksheet
Dim LR As Long
Set M = Sheets("Master")
For i = 1 To Sheets.Count
LR = M.Cells(Rows.Count, "A").End(xlUp).Row
If ws(i).Name <> "Master" Then ws(i).Range("BY36").Copy M.Cells(LR + 1, "A")M.Cells(LR + 1, "B") = ws(i).Name
Next i
End Sub

 

[Ody]

Thanks for the reply klarowe,

Sadly, I'm still getting a 'run time error 91: Object variable or With Block variable not set' using the following:

Sub Copy()
Dim ws As Worksheet
Dim M As Worksheet
Dim LR As Long
Set M = Sheets("Summary")
For i = 1 To Sheets.Count
LR = M.Cells(Rows.Count, "A").End(xlUp).Row
If ws(i).Name <> "Summary" Then
ws(i).Range("BY36").Copy M.Cells(LR + 1, "A")
M.Cells(LR + 1, "B") = ws(i).Name
End If
Next i
End Sub

Any thoughts?