Solved: Copy entire sheet to a new workbook

[makako]

Im trying to make a toolbar with an option that when being selected should copy the activesheet to a new workbook. I tried using the record button but the code of the paste part is lost.

I thought it would be something simple like

activesheet.copy (application.workbooks.add)

please help
meanwhile Im copying the whole cells

activeworksheet.cells.copy
newsheetonnewbook.paste

but some properties are lost. thanks

[Oorang]

Option Explicit
Public Sub CloneActiveSheet()
Excel.ActiveSheet.Copy
End Sub

[paulked]

This seems to work:


Sub CopyThisSheet()
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
ActiveSheet.Paste
End Sub


Regards

Paul Ked

[makako]

thanks Oorang, this is the first time i see "Excel" in any code

[Oorang]

Hi Makako,
It's not really necessary , I just happen to work with projects that use Access & Excel simultaneously and/or run multiple Excel processes at once. Since there are a few naming overlaps between Access/Excel (notably "Application") and if you are using multiple processes you need to be explicit about which one, I just got in the habit. Plus it's easier to remember syntax when you have to nice intellisense prompting you. It's mostly just a style thing. ActiveSheet.Copy Works just as well:)