Moving rng of wrkshts to new workbook

[pickytweety]

Hi,
I have a worksheet named "Start" and a worksheet named "End". In between
these two worksheets there are a series of other worksheets that a macro
builds. The number of sheets and the names of the sheets change depending on
a macro that is run. How do I select all the sheets between "Start" and
"End" and move them (not copy, but move) to a new workbook?

 

[Per Jessen]

Hi

Sub MoveSheets()
Dim wb As Workbook
Dim NewWb As Workbook

Set wb = ThisWorkbook
Set NewWb = Workbooks.Add

wb.Activate
For Each sh In wb.Sheets
If sh.Name <> "Start" And sh.Name <> "End" Then
sh.Move after:=NewWb.Sheets(Sheets.Count)
End If
Next
End Sub

Regards,
Per

 

[Mike H]

Hi,

I may be missing something here but if you want to copy them to a new
workbook why don't you simply save the existing workbook under a new name and
then delete sheets start/end. the original workbook will remain intact.

Mike

 

[Gary''s Student]

Sub picky()
Set wbold = Workbooks("Book1.xls")
Set wbnew = Workbooks("Book2.xls")
wbold.Activate
Sheets("Start").Activate
i = 1
Do
ActiveSheet.Move after:=wbnew.Sheets(i)
i = i + 1
wbold.Activate
If ActiveSheet.Name = "End" Then
ActiveSheet.Move after:=wbnew.Sheets(i)
Exit Sub
End If
Loop
End Sub

 

[john]

another way maybe:

Sub MoveSheets()
Dim Arr() As String
'Create an Array
'of all Sheets
Dim N As Long
With ThisWorkbook.Worksheets
If .Count < 3 Then Exit Sub
ReDim Arr(2 To .Count - 1)
For N = 2 To .Count - 1
Arr(N) = .Item(N).Name
Next N
End With
Worksheets(Arr).Move
End Sub

 

[pickytweety]

There are sheets outside of the Start/End sheets that have lots of data,
array formulas, and other stuff. I was thinking it would be faster time wise
to move the sheets and save instead of saving the whole file. That may not
be true, but that was the way my thoughts were working. This is certainly a
good idea that I may end up using, if not now, in the future. Thanks!

 

[pickytweety]

Hey thanks! This worked great.