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:
Sheets(Array("Sheet1 (2)", "Sheet1 (3)")).Move
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

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 Befo=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


"jo" wrote:

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:
Sheets(Array("Sheet1 (2)", "Sheet1 (3)")).Move
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

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

"OssieMac" wrote:

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 Befo=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


"jo" wrote:

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:
Sheets(Array("Sheet1 (2)", "Sheet1 (3)")).Move
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

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

"jo" wrote:

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:
Sheets(Array("Sheet1 (2)", "Sheet1 (3)")).Move
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

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

"Joel" wrote:

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

"jo" wrote:

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:
Sheets(Array("Sheet1 (2)", "Sheet1 (3)")).Move
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