Moving rng of wrkshts to new workbook
 

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?
--
Thanks,
PTweety

Moving rng of wrkshts to new workbook
 

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

On 7 Dec., 19:53, pickytweety
wrote:
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? *
--
Thanks,
PTweety

Moving rng of wrkshts to new workbook
 

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

"pickytweety" wrote:

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?
--
Thanks,
PTweety

Moving rng of wrkshts to new workbook
 

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
--
Gary''s Student - gsnu200818

Moving rng of wrkshts to new workbook
 

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
--
jb


"pickytweety" wrote:

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?
--
Thanks,
PTweety

Moving rng of wrkshts to new workbook
 

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!
--
Thanks,
PTweety


"Mike H" wrote:

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

"pickytweety" wrote:

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?
--
Thanks,
PTweety

Moving rng of wrkshts to new workbook
 

Hey thanks! This worked great.
--
Thanks,
PTweety


"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200818