Save file

  • Thread starter Thread starter Thanks
  • Start date Start date
T

Thanks

Hello
I am opening a file myfile.xls run a macro and then I would like to copy the
sheet "DATA" and place in a new file and then save as myfileupload.xls and
also as myfileuploadmmddyyyy.xls. This process may occur several time in one
day. How do I modify my macro to accomplish the save? I would like to put
an alpha suffix on the later file and increment each time it is run during
the day. Such as myfileuploadmmddyyyy_A.xls, myfileuploadmmddyyyy_B.xls, etc.
All help is appreciated
 
How about something like this, using TIME (Hours / Minutes / Seconds) as the
final file name...

'/================================================/
' Sub Purpose: copy worksheet with new name and
' date/time
'/================================================/
'
Public Sub Copy_Data_Worksheet()
Dim strWksheet As String
Dim strPath As String
Dim strFileName As String

On Error GoTo err_Sub

' - - V A R I A B L E S - - - - -
strWksheet = "DATA" 'Worksheet to be copied
strPath = "C:\Temp\" 'Path where wksht is 2 b copied 2
strFileName = "MyFileUpload_" 'new name for worksheet
' - - - - - - - - - - - - - - - -

Sheets("Sheet1").Copy
ActiveWorkbook.SaveAs Filename:=strPath & strFileName & _
Format(Now(), "yyyy-mm-dd_hhmmss") & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'close the new worksheet
ActiveWindow.Close

exit_Sub:
On Error Resume Next
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: Copy_Data_Worksheet - " & Now()
GoTo exit_Sub

End Sub
'/================================================/
 
Had to make a minor change...

'/================================================/
' Sub Purpose: copy worksheet with new name and
' date/time
'/================================================/
'
Public Sub Copy_Data_Worksheet()
Dim strWksheet As String
Dim strPath As String
Dim strFileName As String

On Error GoTo err_Sub

' - - V A R I A B L E S - - - - -
strWksheet = "DATA" 'Worksheet to be copied
strPath = "C:\Temp\" 'Path where wksht is 2 b copied 2
strFileName = "MyFileUpload_" 'new name for worksheet
' - - - - - - - - - - - - - - - -

Sheets(strWksheet).Copy
ActiveWorkbook.SaveAs Filename:=strPath & strFileName & _
Format(Now(), "yyyy-mm-dd_hhmmss") & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'close the new worksheet
ActiveWindow.Close

exit_Sub:
On Error Resume Next
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: Copy_Data_Worksheet - " & Now()
GoTo exit_Sub

End Sub
'/================================================/

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown
 
Back
Top