VBA Code to save copy worksheet not entire workbook

[njohnn]

Hi,

I am using the below code to autosave a copy of a spreadsheet when clicking a button on the worksheet.

I actually only want to save a copy of the worksheet displayed and not all the sheets from the workbook as this is quite a large spreadsheet. Can anyone offer an amended the code for me so I can make the amendments to the save function I require please.

Cheers,
Nick

[njohnn]

Doh!, here's the code I'm currently using:


Public Sub CommandButton1_Click()
Dim nom As String
nom = Day(Date) & "-" & Month(Date) & "-" & Year(Date) & "_" & ActiveWorkbook.Name
ActiveWorkbook.SaveCopyAs ActiveWorkbook.Path & "\" & nom
rep = MsgBox("You database has been saved : " & Name, vbYes + vbInformation, "Copy of spreadsheet")
End Sub

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[njohnn]

Thanks for the reply John.

Your code creates the copy of the individual worksheet but fails to save it. I'm getting the following error messages:

Firstly an error msg in Excel saying it cannot save the file type, need to select a macro enabled file type.

Following this, whether you select Yes or No, I get another error msg come up in the VBA page with a big red cross with 400 written next to it.

I am able to manually intervene and save the copy of the spreadsheet but its not very slick with all the error messages and still needs manual saving.

Any suggestions on how to remedy this?

Cheers,
Nick

[JOHN H. DAVIS]

Hi Nick:

Can you attach a sample file which errors?

Try:

Please Login or Register  to view this content.

[njohnn]

Here's the workbook.
It'll open on the worksheet I am trying to save the copy of. Essentially it is for a user to record data on a daily basis and then have a copy of that data with a record of the date on which the record was taken, ideally in the same folder as the original workbook.

The sheet the workbook opens on has the code you gave me assigned to the button called 'Autosave a Copy'

Cheers,
NickDraft EMS.xlsm

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

[njohnn]

That's got round the initial msg which comes up regarding saving as a macro enabled workbook, but I still get the 400 error msg in VBA and the copy sheet does not save anywhere.

[JOHN H. DAVIS]

I can't duplicate the error you are getting. On the sample provided, I tested it and it did not error?

[JOHN H. DAVIS]

Try to error trap it and see what the error says?

Please Login or Register  to view this content.

[njohnn]

Could you attach the sample and email back to me please?

[JOHN H. DAVIS]

Could you attach the sample and email back to me please?

Go to Post #6 to retrieve. I deleted the copy I tested on.