4 Replies - 8116 Views - Last Post: 13 December 2011 - 09:15 AM Rate Topic: -----

#1 lifeboat  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 07-August 10

VBA/Excel - Looping through worksheets doesn't include last sheet

Posted 09 December 2011 - 05:37 PM

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

Is This A Good Question/Topic? 0
  • +

Replies To: VBA/Excel - Looping through worksheets doesn't include last sheet

#2 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: VBA/Excel - Looping through worksheets doesn't include last sheet

Posted 09 December 2011 - 05:50 PM

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



Was This Post Helpful? 0
  • +
  • -

#3 lifeboat  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 07-August 10

Re: VBA/Excel - Looping through worksheets doesn't include last sheet

Posted 09 December 2011 - 05:56 PM

View PostIonut, 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.
Was This Post Helpful? 0
  • +
  • -

#4 lifeboat  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 07-August 10

Re: VBA/Excel - Looping through worksheets doesn't include last sheet

Posted 09 December 2011 - 06:22 PM

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.
Was This Post Helpful? 0
  • +
  • -

#5 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: VBA/Excel - Looping through worksheets doesn't include last sheet

Posted 13 December 2011 - 09:15 AM

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

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1