0

Trying to write an Excel macro to find the last column with data, copy that column, then paste in the next (empty) column. My problem is, there are multiple sheets, and it only works up to the currently active sheet...After that, it doesn't work correctly:

Dim lcol as Long

For j = 1 To Worksheets.Count

Step 1 - Get last column with data in row 6

lCol = Cells(6, Worksheets(j).Columns.Count).End(xlToLeft).Column

Step 2 - Copy that column to the next column

Worksheets(j).Columns(lCol).Copy Destination:=Worksheets(j).Columns(lCol + 1)

Step 3- Change the value of row 6 in the new column to be 1+ the value of same cell in copied column

Worksheets(j).Cells(6, lCol + 1) = Worksheets(j).Cells(6, lCol) + 1

Next j

If I have 5 tabs/sheets in the workbook, and my active tab is the last one, all sheets get processed correctly. If my active tab is, say, the 3rd one, all sheets up to that tab get processed correctly, but the sheets after that don't.

Anyone know why this is or what I need to do to make sure all sheets get processed regardless of which is active?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Shaun
  • 33
  • 2
  • 7
  • 2
    Your problem is in Step 1. You haven't qualified the `Worksheet` that the `Cells` are on, so there's an implicit reference to the `ActiveSheet`. – BigBen May 10 '19 at 18:13
  • Thanks, I had to look that up ("qualifying" references) but that did the trick. – Shaun May 10 '19 at 20:28
  • Great! It's best practice to fully qualify any `Range` or `Cells` calls with the `Workbook` and `Worksheet` they belong to. Fixing that makes your code much more robust. – BigBen May 10 '19 at 20:29
  • Possible duplicate of [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Luuklag May 12 '19 at 12:16

0 Answers0