[lifeboat]

EXCEL 2007/VBA: Good evening, all. I have a sub that loops through all the worksheets in a workbook that contains many sheets of data. The sub first creates a new, blank sheet, then it loops through all remaining sheets to copy the data and paste it into the new sheet. It works for all but the last sheet. No matter how many sheets I try, it always stops short of that last sheet. The code:

Sub CopyPasta()

Application.ScreenUpdating = False

Worksheets.Add(Before:=Worksheets(1)).Name = "Data Sheet"

Dim i As Integer
         
   For i = 1 To ActiveWorkbook.Worksheets.Count
   
      If Worksheets(i).Name <> "Data Sheet" Then
          Range("A2").Select
          Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
          Selection.Copy
          Worksheets("Data Sheet").Activate
          Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial
          Application.CutCopyMode = False
          Worksheets(i).Activate
      End If
   Next i
   
Worksheets("Data Sheet").Activate
Rows("1").EntireRow.Delete
Range("A1").Select

Application.ScreenUpdating = True

                 
End Sub

What am I missing?

Thanks - jfp

[Ionut]

If you change the for with a for each, is it the same?

Dim ws as Worksheet

for each ws in ActiveWorkbook.Worksheets
   if ws.Name <> "Data sheet" then 
       //stuff
   end if 
next ws

[lifeboat]

Ionut, on 09 December 2011 - 05:50 PM, said:

If you change the for with a for each, is it the same?

Dim ws as Worksheet

for each ws in ActiveWorkbook.Worksheets
   if ws.Name <> "Data sheet" then 
       //stuff
   end if 
next ws

Yes, I tried that - same results.

[lifeboat]

I don't know if this helps troubleshooting any, but if add + 1 to the count...

For i = 1 To ActiveWorkbook.Worksheets.Count + 1

... it works. It throws a "subscript out of range error", but it gets all the sheets copied. With a little error handling, it goes by unnoticed and I can get it to work as needed, but I'd rather not leave it that way and I can't understand why it won't work as originally written above.

[BobRodes]

Perhaps you should check the worksheets.count before and after adding the new one. I suspect that it doesn't change until you do something like save the workbook; VB/VBA is famous for that sort of thing. In any case, if you save the workbook before going through your sheet update routines, you may find that the count gets updated and your problem is solved.

Another alternative is to populate your worksheet BEFORE adding it to the collection. (Can't you do that? I think so. )

You can avoid checking for the name "Data Sheet" every time you go through the loop by simply starting your for loop at 2 instead of 1.

This post has been edited by BobRodes: 13 December 2011 - 09:20 AM