"Save As" through VBA

[dotsent]

Hi!

Just recorded my very first macro (yay!). It's a simple one doing mainly sorting and copying. I'm missing some functionality though that might not be possible to record, however could perhaps be modified within the VBA code?

The file is template.xlsm - given it's a template in nature, I want to minimize the risk users would make and save changes to it, so I want to include the "Save As" process in the macro. I did not try recording a "Save As" function in the macro as I don't know how to choose a proper file name, as the template file itself should not be overwritten. I have 2 options for file name selection:

1. Use a timestamp for file name (doesn't really matter on the date format)
2. Use contents of a pre-defined cell in the file (e.g. "C2")
3. IDEALLY BOTH

Can the same VBA code I recorded be adjusted to also include this? The folder path for the new file should be the same with template.

Thanks a bunch in advance!

Sub Template()
'
' Template Macro
'
' Keyboard Shortcut: Ctrl+q
'
    Columns("E:E").Select
    Selection.EntireColumn.Hidden = False
    Columns("D:D").Select
    Selection.EntireColumn.Hidden = False
    ActiveSheet.Range("$A$3:$H$289").AutoFilter Field:=6, Criteria1:="<>"
    Application.Goto Reference:="Template"
    Selection.Copy
    Columns("D:D").Select
    Selection.EntireColumn.Hidden = True
    Columns("E:E").Select
    Selection.EntireColumn.Hidden = True
End Sub

 

[iliace]

Hi there dotsent! Does your user name mean post-doctorate?

Congratulations on recording your first macro. Hope it works for you.

Try using this code. Note that I made slight modifications to what the recorder did, primarily on the account that you do not need to select columns (or really anything) before taking macro actions on them. Therefore, you can improve your code by avoiding using Select/Selection as much as possible.

I highlighted in red the lines that I added. At the top of your code, I added a String type variable called FileName. Then, below your code, I create a string based on the parameters you provided. To make it easier to digest, I broke them out into separate lines using the underscore _ operator. So, your new file name will consist of:

* Current date, formatted as yyyy-mm mmm, which means four-digit year, zero-padded month number, shorthand three-character month (January=Jan, May=May, July=Jul, etc)
* The value of C2 in the active sheet
* Current time, formatted as hhmmss, which means military time two-digit hour, minute, and second
* The file extension, which is anything to the right of the last period in the current file name.

Then, we save a copy of the file in the current path, using the newly created file name. By using SaveCopyAs, the user is returned to the template.xlsm file, rather than the newly created file.

Give it a shot, post back with what happens and any questions. And welcome, once again, to the world of macro writing!

Sub Template()
'
' Template Macro
'
' Keyboard Shortcut: Ctrl+q
'
    Dim FileName As String

    Columns("E:E").EntireColumn.Hidden = False
    Columns("D:D").EntireColumn.Hidden = False
    ActiveSheet.Range("$A$3:$H$289").AutoFilter Field:=6, Criteria1:="<>"
    Application.Goto Reference:="Template"
    Selection.Copy
    Columns("D:D").EntireColumn.Hidden = True
    Columns("E:E").EntireColumn.Hidden = True
    
    
    With ActiveWorkbook
      FileName = Format(Now, "yyyy-mm mmm") & _
            " " & Range("C2").Value & _
            "_" & Format(Now, "hhmmss") & _
            "." & Right(.Name, Len(.Name) - InStrRev(.Name, "."))

      .SaveCopyAs .Path & "\" & FileName
    End With
End Sub

 

[dotsent]

Thanks a lot for the effort and valued feedback! Now as for the username - you would be correct! Some wishful thinking on by part though as I did not pursue anything academic past masters.

The code works nicely, however I'm missing the clipboard copy function after saving the new file. I guess it's that part of the code:

Selection.Copy

I did minor tweaks on the ranges and new code with the "Save As" function is below. When I take away the file naming and SaveCopyAs functionality, the copy function works again. When I add the SaveCopyAs function, the sorting works as before though, but I do not retain anything for pasting on the clipboard.

Obviously I guess I could keep them in 2 separate macros and add 2 different macro-launching buttons on the worksheet, however maybe there is still some more intelligent way to handle this within the same macro and single button?

Sub Template()
'
' Template Macro
'
' Keyboard Shortcut: Ctrl+q
'
    
    Dim FileName As String
    
    Columns("E:E").EntireColumn.Hidden = False
    Columns("D:D").EntireColumn.Hidden = False
    Columns("C:C").EntireColumn.Hidden = False
    ActiveSheet.Range("$A$4:$I$290").AutoFilter Field:=7, Criteria1:="<>"
    Application.Goto Reference:="Table"
    Selection.Copy
    Columns("E:E").EntireColumn.Hidden = True
    Columns("D:D").EntireColumn.Hidden = True
    Columns("C:C").EntireColumn.Hidden = True
    
    With ActiveWorkbook
      FileName = Format(Now, "yyyy-mm mmm") & _
            " " & Range("F3").Value & _
            "_" & Format(Now, "hhmmss") & _
            "." & Right(.Name, Len(.Name) - InStrRev(.Name, "."))
      .SaveCopyAs .Path & "\" & FileName
    End With
End Sub

 

[LFKim2018]

Hi!

Just recorded my very first macro (yay!). It's a simple one doing mainly sorting and copying. I'm missing some functionality though that might not be possible to record, however could perhaps be modified within the VBA code?

The file is template.xlsm - given it's a template in nature, I want to minimize the risk users would make and save changes to it, so I want to include the "Save As" process in the macro. I did not try recording a "Save As" function in the macro as I don't know how to choose a proper file name, as the template file itself should not be overwritten. I have 2 options for file name selection:

Hi there
I had a file similar to yours, a template which I do not want users to alter - I change its attribute to Read Only and it does the trick - they can only save their work as different filename! Hope this helps

 

[iliace]

You can something like this in your Workbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Dim sNewName As String
  Dim sOldName As String

  Cancel = True

  sOldName = ThisWorkbook.FullName

  Do
    sNewName = ""
    sNewName = Application.GetSaveAsFilename
  Loop Until sNewName = "False" Or StrComp(sNewName, sOldName) <> 0
  

  If sNewName <> "False" Then
    Application.EnableEvents = False
      ThisWorkbook.SaveAs sNewName
    Application.EnableEvents = True
  End If
End Sub