Hi

Can someone please advise on how can I copy active workseet to a new
workbook without copying the macros along with it (the workbook I am copying
the workseet from ahas nacros in it). i.e copy just the sheet & its value but
keeping intact the formating.
All this using VBA.

Thanks a lot in advance for your help
J

Hi James,
'-------------------------------
Sub CopySheet()
Dim wshSource As Worksheet, wshDest As Worksheet
Dim wkbDest As Workbook

Set wshSource = ActiveSheet
Set wkbDest = Workbooks.Add(xlWBATWorksheet)
Set wshDest = wkbDest.Worksheets(1)

wshSource.Cells.Copy wshDest.Cells
End Sub
'-----------------------------------

Regards,
Sebastien

"James" wrote:

Hi

Can someone please advise on how can I copy active workseet to a new
workbook without copying the macros along with it (the workbook I am copying
the workseet from ahas nacros in it). i.e copy just the sheet & its value but
keeping intact the formating.
All this using VBA.

Thanks a lot in advance for your help
J

Thank you so much Sebastienm, it works.
Just one question, as my sheet which got copied it also had a Comman Button
with Macro assigned to it, as Button moved over while copying, when you click
it it tries to call the old worksheet to execute the macro. Any way to get
rid of all the Buttons on the new sheet after copy.

Thanks a million

"sebastienm" wrote:

Hi James,
'-------------------------------
Sub CopySheet()
Dim wshSource As Worksheet, wshDest As Worksheet
Dim wkbDest As Workbook

Set wshSource = ActiveSheet
Set wkbDest = Workbooks.Add(xlWBATWorksheet)
Set wshDest = wkbDest.Worksheets(1)

wshSource.Cells.Copy wshDest.Cells
End Sub
'-----------------------------------

Regards,
Sebastien

"James" wrote:

Hi

Can someone please advise on how can I copy active workseet to a new
workbook without copying the macros along with it (the workbook I am copying
the workseet from ahas nacros in it). i.e copy just the sheet & its value but
keeping intact the formating.
All this using VBA.

Thanks a lot in advance for your help
J

If the button is:
- a button from the Forms toobar:
wshDest.buttons(1).delete
- a button from the Control Toolbox toolbar
wshDest.oleobjects(1).delete
You can replace the 1 by the name of the button if you know it (should be
displayed in the Name box ie address box when you select the button). eg
wshDest.buttons("button 1").delete
wshDest.oleobjects("button 1").delete

Regards,
Sebastienm

"James" wrote:

Thank you so much Sebastienm, it works.
Just one question, as my sheet which got copied it also had a Comman Button
with Macro assigned to it, as Button moved over while copying, when you click
it it tries to call the old worksheet to execute the macro. Any way to get
rid of all the Buttons on the new sheet after copy.

Thanks a million

"sebastienm" wrote:

Hi James,
'-------------------------------
Sub CopySheet()
Dim wshSource As Worksheet, wshDest As Worksheet
Dim wkbDest As Workbook

Set wshSource = ActiveSheet
Set wkbDest = Workbooks.Add(xlWBATWorksheet)
Set wshDest = wkbDest.Worksheets(1)

wshSource.Cells.Copy wshDest.Cells
End Sub
'-----------------------------------

Regards,
Sebastien

"James" wrote:

Hi

Can someone please advise on how can I copy active workseet to a new
workbook without copying the macros along with it (the workbook I am copying
the workseet from ahas nacros in it). i.e copy just the sheet & its value but
keeping intact the formating.
All this using VBA.

Thanks a lot in advance for your help
J