Moving unknown sheets to new workbook

[preetx2]

I want to move multiple sheets to new workbook. Currently, I am using the code posted below and it works just fine. However, its limited. Is there a way, Where I can count the sheets in the workbook first, then run the code to generate new worksheets and move only newly generated sheets to new workbook? Basically, I am trying to avoid listing the name of all sheets.

sub move()
Dim w As Workbook, ws As Worksheet, ss As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.CodeName <> "Sheet1" And ws.CodeName <> "Sheet2" And ws.CodeName <> "Sheet3" And ws.CodeName <> "Sheet4" And ws.CodeName <> _
"Sheet5" And ws.CodeName <> "Sheet6" And ws.CodeName <> "Sheet7" And ws.CodeName <> "Sheet8" And ws.CodeName <> "Sheet9" And ws.CodeName <> _
"Sheet10" And ws.CodeName <> "Sheet11" And ws.CodeName <> "Sheet12" And ws.CodeName <> "Sheet13" And ws.CodeName <> "Sheet14" And ws.CodeName <> "Sheet15" And ws.CodeName <> _
"Sheet16" And ws.CodeName <> "Sheet17" And ws.CodeName <> "Sheet18" And ws.CodeName <> "Sheet19" And ws.CodeName <> "Sheet20" And ws.CodeName <> "Sheet21" And ws.CodeName <> "Sheet22" Then


If w Is Nothing Then
ws.move


Set w = ActiveWorkbook
Else
ws.move After:=ss
End If
Set ss = ActiveSheet
End If
Next ws
Sheets(1).Select
ThisWorkbook.Activate
end sub()

 

[netuser]

Sheets.Count

wil give you total number of sheets in your active workbook. if it is what you looking for

If you wanna adda sheet at the end of last sheet :

Dim ws As Worksheet

 With ActiveWorkbook
        Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        ws.Name = "YourNewSheetName"
    End With

 

[preetx2]

Sheets.Count

wil give you total number of sheets in your active workbook. if it is what you looking for

If you wanna adda sheet at the end of last sheet :

Dim ws As Worksheet

 With ActiveWorkbook
        Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        ws.Name = "YourNewSheetName"
    End With

Thanks, but I am looking to move sheets to new workbook. I am generating about 50 new sheets from template that needs to be moved out of current workbook. Current workbook has 22 worksheet that needs to stay in the current workbook, only needs to move the new sheets generated from template.

 

[Norie]

How/when are you generating the new worksheets?

 

[Grasor]

You can reference sheets in your workbook numerically from left to right with 1 being the leftmost sheet incrementing by 1 each sheet to the right. New sheets created programmatically should appear to the right so since you are keeping the first 22 sheets you would just run a quick loop to move all sheets numbered 23 and higher to the new workbook.

Have you tried doing that?

 

[preetx2]

I am selected partner's initials and generating new sheets based on the template. The template is linked to other 20 tabs in the file and generating a formula. So i need those tabs in there, but anything that is generated after those tabs needs to be moved into new workbook.

 

[preetx2]

I did. I did an array with the sheets, but again its limited. Its a quarter based file, that 22 number could change over quarter. Hence I was looking for where I can count the sheets first in the workbook, and then anything number higher than that will be moved to new workbook.

 

[preetx2]

You can reference sheets in your workbook numerically from left to right with 1 being the leftmost sheet incrementing by 1 each sheet to the right. New sheets created programmatically should appear to the right so since you are keeping the first 22 sheets you would just run a quick loop to move all sheets numbered 23 and higher to the new workbook.

Have you tried doing that?

I did. I did an array with the sheets, but again its limited. Its a quarter based file, that 22 number could change over quarter. Hence I was looking for where I can count the sheets first in the workbook, and then anything number higher than that will be moved to new workbook.​

 

[Norie]

How is the array limited?

How did you actually use one?

 

[Grasor]

I did. I did an array with the sheets, but again its limited. Its a quarter based file, that 22 number could change over quarter. Hence I was looking for where I can count the sheets first in the workbook, and then anything number higher than that will be moved to new workbook.​

You can use the Sheets.Count method first to get your sheet count then in your loop set your control variable (i.e. i) to Sheets.Count + 1. After the new sheets are created execute Sheets.Count a second time and either set it to a variable to represent the stop point of the loop or juts put that in the call for the loop. i.e.

dim iStart, iStop as integer

iStart = ThisWorkbook.Sheets.Count + 1

'Execute Code to Create your new sheets

iStop = ThisWorkbook.Sheets.Count

for iStart to iStop 'Or "For iStart to ThisWorkbook.Sheets.Count
     'Execute code to move sheet(iStart) to new workbook
next iStart

Make sense?