Updating sheets.count value when adding worksheets

Question

I am writing a macro to copy a sheet and add it to a specific location within the workbook. This macro will be used for workbooks with different numbers of sheets, so I want it to continue to copy and add worksheets until there are no more sheets left.

Sub Macro()

Dim x As Integer

For x = 3 To Sheets.Count Step 3
    Sheets(x).Select
    Sheets(x).Copy Before:=Sheets(x + 3) 

A bunch more code...

Next

The macro obviously runs from 3 to the total number of worksheets, but because the total number of worksheets changes after each step (due to copying/adding a worksheet), the macro stops before it gets to the end of the workbook (I'm guessing because it stops running when the counter reaches the start value of sheets.count). Any suggestions on how to make the macro continue through to the end? This is my first experience writing code so please be gentle :)

Why are you copying sheets instead of moving them? This seems like it would just bloat the file for no real purpose... — tigeravatar
I can not test it now (typing from tablet). You may be able to use a recursive function or call count again inside a while loop or use a GOTO statement. I would try a while loop with while x<nrOfSheets. — surfmuggle

NullDev NullDev · Accepted Answer · 2014-01-28T22:18:45

Let's assume you want to add x sheets so you can reach sheet count y. However, you want all these sheets to be created before the last sheet in your workbook called End. One approach is below:

Sub AddMoreSheets()

    ShCount = ThisWorkbook.Sheets.Count
    TargetCount = 7
    SheetsToAdd = TargetCount - ShCount

    Do Until SheetsToAdd = 0
        ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Sheets("End")
        SheetsToAdd = SheetsToAdd - 1
    Loop

End Sub

But what if you're not always sure if the name of your last sheet is End? One approach is the following:

Sub AddMoreSheets2()

    ShCount = ThisWorkbook.Sheets.Count
    TargetCount = 7
    SheetsToAdd = TargetCount - ShCount

    Do Until SheetsToAdd = 0
        ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Sheets(Sheets.Count)
        SheetsToAdd = SheetsToAdd - 1
    Loop

End Sub

However, for some reason, you really want to use a For Loop for this. Here's the catch: you want to name them based on a list in a sheet called Start! Here's one way to do it:

Sub AddMoreSheets3()

    Dim ListOfNames As Variant, ShName As Variant

    ListOfNames = Sheets("Start").Range("A1:A5").Value 'Modify accordingly.

    For Each ShName In ListOfNames
        Set NewSht = ThisWorkbook.Sheets.Add(Before:=Sheets(Sheets.Count))
        NewSht.Name = ShName
    Next ShName

End Sub

Hope this helps. ;)

Updating sheets.count value when adding worksheets

1 Answers