Command Button Macro Sheet>New Workbook

Hello everybody.

Hopefully someone can help me with this as I am stuck.(As usual)

I want to add a command button to a report that when clicked will copy the sheet into a new book.

I assume that this can be acheived throuch a Macro, but everything I've tried thus far has failed.

Thanks for your help!

Hi Jgp_2,

Try assigning code like the following to a button:

'=============>>
Public Sub Tester04()
ActiveSheet.Copy
ActiveWorkbook.SaveAs _
Filename:=("NewWorkbookName.xls"), _
FileFormat:=xlWorkbookNormal
End Sub
'<<=============


---
Regards,
Norman



"jgp_2" <[email protected]> wrote in message
news:[email protected]...
>
> Hello everybody.
>
> Hopefully someone can help me with this as I am stuck.(As usual)
>
> I want to add a command button to a report that when clicked will copy
> the sheet into a new book.
>
> I assume that this can be acheived throuch a Macro, but everything I've
> tried thus far has failed.
>
> Thanks for your help!
>
>
> --
> jgp_2
> ------------------------------------------------------------------------
> jgp_2's Profile:
> http://www.excelforum.com/member.php...o&userid=27624
> View this thread: http://www.excelforum.com/showthread...hreadid=489219
>

Norman -

Thanks for your help. It is working somewhat, but everytime it sends the sheet to a new workbook I receive a message that says: "A File Named NewWorkBookName.xls already exists in this location. Do You want to replace it?"

I changed the name of "NewWorkbookName.xls". It always works the first time, but if you close the new workbook and try it again the error pops up.

Any suggestions you have would be appreciated.

Thanks again!!!

Originally Posted by Norman Jones

Hi Jgp_2,

Try assigning code like the following to a button:

'=============>>
Public Sub Tester04()
ActiveSheet.Copy
ActiveWorkbook.SaveAs _
Filename:=("NewWorkbookName.xls"), _
FileFormat:=xlWorkbookNormal
End Sub
'<<=============


---
Regards,
Norman



"jgp_2" <[email protected]> wrote in message
news:[email protected]...
>
> Hello everybody.
>
> Hopefully someone can help me with this as I am stuck.(As usual)
>
> I want to add a command button to a report that when clicked will copy
> the sheet into a new book.
>
> I assume that this can be acheived throuch a Macro, but everything I've
> tried thus far has failed.
>
> Thanks for your help!
>
>
> --
> jgp_2
> ------------------------------------------------------------------------
> jgp_2's Profile:
> http://www.excelforum.com/member.php...o&userid=27624
> View this thread: http://www.excelforum.com/showthread...hreadid=489219
>

Hi Jpg_2,

Try:

'=============>>
Public Sub Tester04A()
Dim SStr As String
Const sName As String = "ABCD" '<<==== CHANGE

SStr = sName & Format(Now, "yyyymmdd hh-mm") & ".xls"

ActiveSheet.Copy
ActiveWorkbook.SaveAs _
Filename:=SStr, _
FileFormat:=xlWorkbookNormal
End Sub
'<<=============

Change ABCD to a name which suits your purpose.

Each file created, will bear this name with an appended date and time.

As written, only one file could be created in any given minute If this is a
problem, extend the time format to encompass seconds.
..
---
Regards,
Norman


"jgp_2" <[email protected]> wrote in
message news:[email protected]...
>
> Norman -
>
> Thanks for your help. It is working somewhat, but everytime it sends
> the sheet to a new workbook I receive a message that says: "A File
> Named NewWorkBookName.xls already exists in this location. Do You want
> to replace it?"
>
> I changed the name of "NewWorkbookName.xls". It always works the first
> time, but if you close the new workbook and try it again the error pops
> up.
>
> Any suggestions you have would be appreciated.
>
> Thanks again!!!
>
>
>
> Norman Jones Wrote:
>> Hi Jgp_2,
>>
>> Try assigning code like the following to a button:
>>
>> '=============>>
>> Public Sub Tester04()
>> ActiveSheet.Copy
>> ActiveWorkbook.SaveAs _
>> Filename:=("NewWorkbookName.xls"), _
>> FileFormat:=xlWorkbookNormal
>> End Sub
>> '<<=============
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "jgp_2" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> >
>> > Hello everybody.
>> >
>> > Hopefully someone can help me with this as I am stuck.(As usual)
>> >
>> > I want to add a command button to a report that when clicked will
>> copy
>> > the sheet into a new book.
>> >
>> > I assume that this can be acheived throuch a Macro, but everything
>> I've
>> > tried thus far has failed.
>> >
>> > Thanks for your help!
>> >
>> >
>> > --
>> > jgp_2
>> >
>> ------------------------------------------------------------------------
>> > jgp_2's Profile:
>> > http://www.excelforum.com/member.php...o&userid=27624
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=489219
>> >
>
>
> --
> jgp_2
> ------------------------------------------------------------------------
> jgp_2's Profile:
> http://www.excelforum.com/member.php...o&userid=27624
> View this thread: http://www.excelforum.com/showthread...hreadid=489219
>