Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving Data between sheets in the same workbook and moving data between Workbooks. | Excel Worksheet Functions | |||
copy cell a1 to 1 of 2 wrkshts depending on the value of other cel | Excel Programming | |||
Moving Cell Information from one workbook into another workbook | Excel Worksheet Functions | |||
Split into 2 wrkshts, now function doesn't work | Excel Worksheet Functions | |||
pull data for a company with data in diff cells multiple wrkshts | Excel Worksheet Functions |