Move multiple worksheets to a new workbook

[Guest]

Good day,
I am using a macro to create new worksheets in a wkb and consequently all
these "new" worksheets move to a new workbook.
Tried to help me out with a macro recorder:the line works just exact the way I need, but I'm not able to rephrase it
with variable names of worksheets (in my macro I need to move all worksheets
with item number > 5).
Is there any easy solution for this?
Thanks in advance

 

[Guest]

Hi Jo,

Try this. It moves only those sheets with an item number greater than 5

Sub Macro1()

Dim wbFrom As Workbook
Dim wbTo As Workbook
Dim ws As Worksheet
Dim i As Single

'Ensure that new workbook is open
'Otherwise goto workbook open routine
On Error GoTo OpenWB
Windows("To WorkBook.xls").Activate
On Error GoTo 0

Set wbFrom = ThisWorkbook
Set wbTo = Workbooks("To WorkBook")

With wbFrom
For Each ws In .Worksheets
If ws.Index > 5 Then
ws.Move Before:=wbTo.Sheets(1)
End If
Next ws
End With

GoTo PastOpenWB 'When get to here skip OpenWB

OpenWB:
Workbooks.Open Filename:= _
"C:\Users\Whoever\Documents\Excel\To WorkBook.xls"
Resume Next

PastOpenWB:

End Sub

Regards,

OssieMac

 

[Guest]

sheets("Sheet1 (2)").name = newname1
sheets("Sheet1 (3)").name = newname2

 

[Guest]

Hi OssieMac,
thanks a lot. Probably I will use your advice. But to be completely honest I
was looking for some slim solution.
It quite irritates me that the dumb macro recorder managed it in one
line...;-)
Regards
Jo

 

[Guest]

sorry, probably missed the point. How can I use your aproach in case I have
indefinite number of sheets with item number >5?
Jo