Excel VBA cycles through multiple sheets

Question

Novice VBA here, I have a little problem with the program I'm working on.

I need to copy the data from the last cell in column B from the first sheet and paste it into column A on another xws sheet and repeat this operation for five other worksheets with data.

Here's the code, it doesn't work the way it should:

Sub exercise() Dim ws As Worksheet Dim rng As Range 'Finding last row in column B Set rng = Range("B" & Rows.Count).End(xlUp) For Each ws In ActiveWorkbook.Worksheets 'Don't copy data from xws worksheet If ws.Name <> "xws" Then 'Storing first copied data in A1 If IsEmpty(Sheets("xws").[A1]) Then rng.Copy Sheets("xws").Range("A" & Rows.Count).End(xlUp) 'Storing next copied data below previously filled cell Else rng.Copy Sheets("xws").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) End If End If Next ws End Sub 

There was a problem with ws. referring, but whenever I put it before rng in if statements or before a range (set rng = ...), I get errors.

Thanks in advance for any pointers.

Answer 1

You must declare rng for each ws inside the loop, for example:

 Sub exercise() Dim ws As Worksheet Dim rng As Range For Each ws In ActiveWorkbook.Worksheets 'Finding last row in column B Set rng = ws.Range("B" & ws.Rows.Count).End(xlUp) '<~~ Moved inside the loop 'Don't copy data from xws worksheet If ws.Name <> "xws" Then 'Storing first copied data in A1 If IsEmpty(Sheets("xws").[A1]) Then rng.Copy Sheets("xws").Range("A" & Rows.Count).End(xlUp) 'Storing next copied data below previously filled cell Else rng.Copy Sheets("xws").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) End If End If Next ws End Sub 

Since your code is now, rng will point to ActiveSheet at the time the macro runs, and your code will then copy the same cell in each iteration of the code.