Using a Button Object to move a sheet into a new Workbook

I have an application I have built into Excel where the last page is
either printed or should be saved to the users desktop. I have the
print function working without a problem. However, when I use the
following code;

Private Sub CommandButton2_Click()
If CommandButton2.Caption = "Click Here to Save This Page" Then
ActiveWorkbook.SaveCopyAs "C:\My_Development_Plan.xls"
Else
CommandButton2.Caption = "Click Here to Save This Page"
End If
End Sub

I can only figure out how to save the workbook, I need to save the
single sheet (the active sheet) in a new workbook on their desktop. Is
there a simple way to copy the active sheet into a New Workbook and
save that new workbook to their desktop (or C drive if it has to?)

Good start. You need to copy the sheet first. So something like the
following should work.

Private Sub CommandButton2_Click()
If CommandButton2.Caption = "Click Here to Save This Page" Then
'this copies the sheet, which in turn makes a new workbook
Sheets("Sheet to copy").copy
'this saves the workbook
'continue to name where you want it like below.
'Can also be changed to desktop location just need the path
entered.
ActiveWorkbook.SaveAs "C:\My_Development_Plan.xls"
Else
CommandButton2.Caption = "Click Here to Save This Page"
End If
End Sub

"MK@Hartford" wrote:

> I have an application I have built into Excel where the last page is
> either printed or should be saved to the users desktop. I have the
> print function working without a problem. However, when I use the
> following code;
>
> Private Sub CommandButton2_Click()
> If CommandButton2.Caption = "Click Here to Save This Page" Then
> ActiveWorkbook.SaveCopyAs "C:\My_Development_Plan.xls"
> Else
> CommandButton2.Caption = "Click Here to Save This Page"
> End If
> End Sub
>
> I can only figure out how to save the workbook, I need to save the
> single sheet (the active sheet) in a new workbook on their desktop. Is
> there a simple way to copy the active sheet into a New Workbook and
> save that new workbook to their desktop (or C drive if it has to?)
>
>

Perfect! It works quite well. It seems the customer is perfectly fine
with setting their location to the C-Drive.