Moving multiple worksheets to new workbook in VBA
 

Hi,

I have an existing workbook called wb1 containing 3 worksheets; ws1 -
3. I currently use a macro which extracts a subset of rows from ws1-3
and creates a new worksheet for each. Each worksheet is moved to a new
workbook using Worksheet.Move but this creates a new workbook each time
which is not desirable.

Is there a way to create new sheets and move them to the same workbook?


---
Message posted from http://www.ExcelForum.com/

Moving multiple worksheets to new workbook in VBA
 

Move has a before/after argument. If you specify a workbook using this
argument, then you can move your sheets to an existing workbook.

--
Regards,
Tom Ogilvy

"dororke" wrote in message
...
Hi,

I have an existing workbook called wb1 containing 3 worksheets; ws1 -
3. I currently use a macro which extracts a subset of rows from ws1-3
and creates a new worksheet for each. Each worksheet is moved to a new
workbook using Worksheet.Move but this creates a new workbook each time
which is not desirable.

Is there a way to create new sheets and move them to the same workbook?


---
Message posted from http://www.ExcelForum.com/

Moving multiple worksheets to new workbook in VBA
 

Move has a before/after argument. If you specify a workbook using this
argument, then you can move your sheets to an existing workbook.

--
Regards,
Tom Ogilvy

"dororke" wrote in message
...
Hi,

I have an existing workbook called wb1 containing 3 worksheets; ws1 -
3. I currently use a macro which extracts a subset of rows from ws1-3
and creates a new worksheet for each. Each worksheet is moved to a new
workbook using Worksheet.Move but this creates a new workbook each time
which is not desirable.

Is there a way to create new sheets and move them to the same workbook?


---
Message posted from http://www.ExcelForum.com/

Moving multiple worksheets to new workbook in VBA
 

Hi,

Yes I'm aware it does but I have tried using these to no avail. But I
believe the before and after fields actually apply to the worksheets
you want to move before/after and not the workbook.

Can you provide some sample VBA to demonstrate?

Thanks,
Dan


---
Message posted from http://www.ExcelForum.com/

Moving multiple worksheets to new workbook in VBA
 

Assume I have two workbooks, Bk1.xls and bk2.xls, both open in excel, the
code is in Bk1.xls and I am moving worksheets(1) in bk1.xls to the end of
the tab order in bk2.xls


Workbooks("bk1.xls").Worksheets(1).Move After:= _
Workbooks("bk2.xls").Worksheets( _
Workbooks("bk2.xls").Worksheets.count)

or
With workbooks("Bk2.xls")
Workbooks("bk1.xls").Worksheets(1).Move After:= _
.Worksheets(.Worksheets.count)
End With


As you can see, it refers to a worksheet in a specific workbook.

Regards,
Tom Ogilvy


"dororke" wrote in message
...
Hi,

Yes I'm aware it does but I have tried using these to no avail. But I
believe the before and after fields actually apply to the worksheets
you want to move before/after and not the workbook.

Can you provide some sample VBA to demonstrate?

Thanks,
Dan


---
Message posted from http://www.ExcelForum.com/

Moving multiple worksheets to new workbook in VBA
 

Hi,

Thank you very much. It worked a treat.

Dan


---
Message posted from http://www.ExcelForum.com/

Moving multiple worksheets to new workbook in VBA
 

Hi,

Thank you very much. It worked a treat.

Dan


---
Message posted from http://www.ExcelForum.com/