VBA Macro to copy only ACTIVE Sheet to new workbook as xslm and new location

[dinkss]

Hi there,

I have a macro like this which saves workbook to new location, and works fine. But I need to change this code to save only ACTIVE Sheet, not all of them. Basically I need macro to do 1 to 1 copy of active sheet in different location and will rename the file to original workbook name with date and time. Can anyone help me with this?

Sub SAVE_TO_ARCHIVE()
' Saves active file to current plus backup location, appends system date and time in front of file name in backup locations.

Dim datim As String
datim = Format(CStr(Now), "yyyy_mm_dd_hh_mm_ss_")

ActiveWorkbook.SaveCopyAs "E:\****pack\KAROL_KITPACK_DOWNTIME_TEMP\ARCHIVE\" & datim & ActiveWorkbook.Name
ActiveWorkbook.Save

MsgBox "You can find archived files in E:\****pack\KAROL_KITPACK_DOWNTIME_TEMP\ARCHIVE\ " & FolderName
Application.ScreenUpdating = True
End Sub

 

[HaHoBe]

Hi dinkiss,

where does foldername in your macro get it`s value?

Code for the ActiveSsheet may look like this

Sub SAVE_ACTIOVESHEET_TO_ARCHIVE()
' Saves active sheet to current plus backup location, appends system date and time in front of file name in backup locations.
'https://www.mrexcel.com/board/threads/vba-macro-to-copy-only-active-sheet-to-new-workbook-as-xslm-and-new-location.1171883/

Dim datim As String
Dim strWbName As String

Const cstrPATH As String = "E:\****pack\KAROL_KITPACK_DOWNTIME_TEMP\ARCHIVE\"
datim = Format(Now, "yyyy_mm_dd_hh_mm_ss_")
strWbName = ActiveWorkbook.Name

ActiveSheet.Copy
ActiveWorkbook.SaveAs cstrPATH & datim & strWbName, FileFormat:=51
ActiveWorkbook.Close False

MsgBox "You can find archived worksheet in " & cstrPATH & datim & strWbName
'Application.ScreenUpdating = True
End Sub

Ciao,
Holger

 

[dinkss]

Hi dinkiss,

where does foldername in your macro get it`s value?

Code for the ActiveSsheet may look like this

Sub SAVE_ACTIOVESHEET_TO_ARCHIVE()
' Saves active sheet to current plus backup location, appends system date and time in front of file name in backup locations.
'https://www.mrexcel.com/board/threads/vba-macro-to-copy-only-active-sheet-to-new-workbook-as-xslm-and-new-location.1171883/

Dim datim As String
Dim strWbName As String

Const cstrPATH As String = "E:\****pack\KAROL_KITPACK_DOWNTIME_TEMP\ARCHIVE\"
datim = Format(Now, "yyyy_mm_dd_hh_mm_ss_")
strWbName = ActiveWorkbook.Name

ActiveSheet.Copy
ActiveWorkbook.SaveAs cstrPATH & datim & strWbName, FileFormat:=51
ActiveWorkbook.Close False

MsgBox "You can find archived worksheet in " & cstrPATH & datim & strWbName
'Application.ScreenUpdating = True
End Sub

Ciao,
Holger

I'll try that and will update you!

What do you mean by saying: where foldername in your macro get it`s value?

 

[HaHoBe]

Hi dinkss,

inside the procedure you posted the variable foldername is only mentioned with the message box. As it is not referenced inside the procedure it should get a value elsewhere as a module wide or global variable or it will be empty (and have no effect on the display of MsgBox). That´s all.

Ciao,
Holger

 

[dinkss]

Hi dinkss,

inside the procedure you posted the variable foldername is only mentioned with the message box. As it is not referenced inside the procedure it should get a value elsewhere as a module wide or global variable or it will be empty (and have no effect on the display of MsgBox). That´s all.

Ciao,
Holger

Oh OK. Thank you buddy! I will test your macro and will let you know! Thanks for the help!

 

[dinkss]

Oh OK. Thank you buddy! I will test your macro and will let you know! Thanks for the help!

Hi buddy, I tried your macro but it gives me an error, have a look at these screenshots.
Any help with this?

 

[HaHoBe]

Hi dinkss,

my bad - FileFormat 51 stands for xlOpenXMLWorkbook (without macro's in 2007-2016, xlsx), it must be 52 (xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2016, xlsm)).

Ciao,
Holger

 

[dinkss]

Hi dinkss,

my bad - FileFormat 51 stands for xlOpenXMLWorkbook (without macro's in 2007-2016, xlsx), it must be 52 (xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2016, xlsm)).

Ciao,
Holger

It works perfectly buddy! WOW, thanks so much!

Can you tell me please - if I use this macro:

Sub BBK_AUTO_SAVE()
Application.OnTime Now + TimeValue("01:00:00"), "BBK_AUTO_SAVE"

ActiveWorkbook.Save

End Sub

Do I need to create a button for it and assign macro or once inserted as a module will it save active workbook every 1 hour?

 

[HaHoBe]

Hi dinkss,

I would call the macro from ThisWorkbook and Workbook_Open and get the timer started once the workbook is opened. I´d also make sure that the timer will be deactivated when the workbook is closed.

Ciao,
Holger

 

[dinkss]

Hi dinkss,

I would call the macro from ThisWorkbook and Workbook_Open and get the timer started once the workbook is opened. I´d also make sure that the timer will be deactivated when the workbook is closed.

Ciao,
Holger

OMG, that's too complicated for me could you help me with this?

What MACRO exacly I have to insert, and where?

I would love to have your knowledge!