Save file

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
0
Utf
4/15/2010 6:48:02 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
728 Views

Similar Articles

[PageSpeed] 7

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
'/================================================/


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



"Thanks" wrote:

> 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
0
Utf
4/15/2010 7:28:01 PM
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



"Gary Brown" wrote:

> 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
> '/================================================/
> 
> 
> -- 
> Hope this helps.  
> If it does, please click the Yes button.
> Thanks in advance for your feedback.
> Gary Brown
> 
> 
> 
> "Thanks" wrote:
> 
> > 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
0
Utf
4/15/2010 7:41:01 PM
Reply:

Similar Artilces: