Sheets to new WorkBook

[Robert S]

Hey there,

What I have done:

Once the user hits the command button, a userform pops up, the users has 4
options to choose(Checkboxes). Say the user selects all 4, it will create 4
new sheets (all named) and copy data from a summary sheet to these newly
created sheets. It sets the format and print area of all 4 sheets. If the
user selects 1 sheets, it will only create one sheets with the desired
information.

Where I want to go from here.

I would like to be able to create a new workbook, copy over the sheets that
have been created then remove the sheets from the orignal workbook. Then I
would like it to promt the user to save the document (if possible, with
SaveAs name set to some I have pre-difined (="Rec Domectic Forecast -
"&summ!E3) to keep a standard naming convention.

Any ideas how this would be done? I can't seem to copy of the sheets since
the new workbook name changes (book(x)).

Thanks,
Rob

 

[Joel]

Try this


Sub test()
Initfile = Sheets("Rec Domectic Forecast - &summ").Range("E3")
Set NewBk = Workbooks.Add
With ThisWorkbook
.Sheets("Sheet1").Copy after:=NewBk.Sheets(NewBk.Sheets.Count)
.Sheets("Sheet2").Copy after:=NewBk.Sheets(NewBk.Sheets.Count)
.Sheets(3).Copy after:=NewBk.Sheets(NewBk.Sheets.Count)
End With
fileSaveName = Application.GetSaveAsFilename( _
InitialFileName:=Initfile, _
fileFilter:="Excel Files (*.xls), *.xls")
If fileSaveName <> False Then
NewBk.SaveAs Filename:=fileSaveName
End If

 

[Robert S]

Joel,

Thanks, that worked great.

I have only one issue still... if the user selects all the options, it will
create 5 new sheets and move them over perfectly, however, if the user were
to only choose one, I get an error. Is there an if statement I can use to see
if a sheet exists, if it does, copy it over, if it doesn't, move onto next if
statement.

Thanks again Joel,
Rob

 

[Joel]

there are two methods.

Method One
SearchName = "Sheet1"
Found = false
for each sht in worksheets
if sht.name = SearchName then
Found = True
exit for
end if
next sht

if Found = true then
'enter your code here
end if


or

On Error Resume Next
.Sheets("Sheet1").Copy after:=NewBk.Sheets(NewBk.Sheets.Count)
.Sheets("Sheet2").Copy after:=NewBk.Sheets(NewBk.Sheets.Count)
.Sheets(3).Copy after:=NewBk.Sheets(NewBk.Sheets.Count)

 

[Robert S]

I used the On Error method and it worked like a charm. Thanks for help, it is
much appreciated.

Rob