How to create a copy of an activeworksheet to a new workbook

[PorscheGT2]

Ok, here's the code I did last night:

Private Sub Create_Copy_Click()

Unload UserOptForm
SrvArea = Range("d5&quot
svname = SrvArea & " - BOBCopy.xls"
Workbooks.Add.SaveAs FileName:=svname
Windows("The 2004 Book of Business Report.xls&quot.Activate
ActiveSheet.Select
ActiveSheet.Copy Before:=Workbooks(svname).Sheets(1)
Windows(svname).Activate
User_Options.Visible = False
About_Button_SA.Visible = False
Exit_Bob_SA.Visible = False

End Sub

This code works in Excel 2000 also saved as 97, but when I ran the macro this morning on Excel 97 it gave me an error. Is there a better way to do this? TIA!

 

[xlbo]

Windows("The 2004 Book of Business Report.xls&quot.activesheet.copy

will create a new workbook with one sheet - your copied sheet - then just name it...

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[PorscheGT2]

Hello Geoff,

Thanks for your reply. For some reason, when it executes the line:

ActiveSheet.Copy Before:=Workbooks(svname).Sheets(1)

it gives me the error "Copy Method of Worksheet Class Failed."

Though when I execute it as a macro in a module, it works just fine. My co worker told me it may have something to do with the fact that it's not global. Here's the whole subroutine for your reference:

Private Sub Create_Copy_Click()

Unload UserOptForm
Application.ScreenUpdating = False
srvarea = Range("d5&quot
svname = srvarea & " - BOBCopy.xls"
Workbooks.Add.SaveAs FileName:=svname
Windows("Copy of The 2004 Book of Business Report v1.0b.xls&quot.Activate
Worksheets(ActiveSheet.Name).Select
Worksheets(ActiveSheet.Name).Copy Before:=Workbooks(svname).Sheets(1)
Windows(svname).Activate
ActiveSheet.Shapes("commandbutton1&quot.Visible = False
ActiveSheet.Shapes("commandbutton2&quot.Visible = False
Workbooks(svname).Save


End Sub

I am executing this from a commandbutton by the way. It works ok in Excel 2000, though it's giving me problems in 97. Do you know what's wrong or missing in my code? TIA!

 

[xlbo]

What I'm suggesting is that you don't need to create a new workbook specifically. this line will create a new one on its own:

Windows("The 2004 Book of Business Report.xls&quot.activesheet.copy

And this line will name it:
Activeworkbook.SaveAs FileName:=svname

easier - yes ?

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[PHV]

Just a though, if the Create_Copy control is in the UserOptForm form, you may consider to place this line "Unload UserOptForm" nearer the End Sub.


Hope This Help
PH.

 

[stillwillyboy]

Geoff,

I am have a similar situation. I am using:

Workbooks.Add
newbook = ActiveWorkbook.Name
'save sheets from Prop1Compute to newbook
Workbooks("Prop1Compute.xls&quot _
.Sheets(Array("MAIN-PT", "WYND-PT", "SATL-PT", "NOVA-PT", _
"InvoicePage1", "InvoicePage2", "InvoicePage3&quot).Copy _
Before:=Workbooks(newbook).Sheets(1)
'save to Prop1Months directory.
ActiveWorkbook.SaveAs _
Filename:="C:\MSOffice\Prop1Months\" & "Prop1" & _ ReportMonth & ".xls"

But the line: Windows("NewBook.xls&quot.ActiveSheet.Copy
generates "Subscript out of range". Changing .Copy to
.Add generates the same error.

Bill

 

[xlbo]

I don't think you can do Activesheet.copy - I think you have to specify either the name or the index

This should create a new workbook with the sheets in:

Workbooks("Prop1Compute.xls&quot.Sheets(Array("MAIN-PT", "WYND-PT", "SATL-PT", "NOVA-PT", _
"InvoicePage1", "InvoicePage2", "InvoicePage3&quot).Copy

and this would save it:
ActiveWorkbook.SaveAs _
Filename:="C:\MSOffice\Prop1Months\" & "Prop1" & _ ReportMonth & ".xls"


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244