MrExcel Publishing
Your One Stop for Excel Tips & Solutions

There's got to be a simple way to :


Posted by Looking @ the screen to long :( on June 13, 2001 1:31 PM

Help

I've got a file with 50+ tabs. @ the end of the processing
All I wanted to do is to move 2 tabs into a "new file". They are
grouped).

Since the tabs names are changing, I just can't setup
an array (What Excel does if you record a macro).
The 2 tabs grouped @ the end of the process,
so I thought it would be simple. Tried everything I could
think of - any suggestions or all sugestions would be
greatly appreciated


Sub Test()
Sheets.Move
End Sub


Posted by Joe Was on June 13, 2001 2:49 PM

This code makes a new file. You then add the copy code to or move your two sheets to the new workbook. JSW

sub makeafile()
workbooks.add
mkdir("C:\the folder you want\") ActiveWorkbook.SaveAs FileName:="C:\the folder you want\nextfile.xls", FileFormat:= xlNormal, password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
end sub

Posted by Looking @ the screen to long :( on June 13, 2001 3:16 PM

Thxs Joe This code makes a new file. You then add the copy code to or move your two sheets to the new workbook. JSW sub makeafile()

Posted by Joe Was on June 13, 2001 3:34 PM

I just thought of this if you know that you will only have a set number of sheets and the sheets you will move are always the last two you can; Reference the sheets as numbers, you do not need the name as it changes?
So the copy code will be:
Worksheets("Sheet49").Range("A1:X100").Copy destination:=Your file and Sheet object here! in the same WorkBook it would be Worksheets("MySheet").Range("A1")
You can chain the code or 3D the sheets. Note the code I gave you may not handle the Error if the Mkdir dir is already made?
You may want to use your default folder and pre build a workbook for the last two sheets? JSW

Posted by Looking @ the screen to long :( on June 13, 2001 4:04 PM

Thxs Joe for the tips, but :(

Joe :
What I really need to do is move the "2" sheets together into a new workbook (so that tab names go with them). Also, the "2" sheets may grow to 4 or 5 etc...

Any & all help is greatly appreciated.

Posted by Looking @ the screen to long :) on June 13, 2001 5:18 PM

Thxs Joe for the help - But here's the code I was looking for


Sub Test()

Dim homefile As ThisWorkbook
Dim tmpfile As Workbook
Set homefile = ActiveWorkbook

' source for code http://www.geocities.com/davemcritchie/excel/buildtoc.htm#sortallsheets
Application.ActiveWorkbook.Windows(1).SelectedSheets.Copy

Set tmpfile = ActiveWorkbook
homefile.Activate

tmpfile.Activate

End Sub