Ytukyg

I've seen similar problems here, I've researched in several blogs but I still can't find a solution. I'm trying to automatically create a name for a file based in some cells.

So, I created a macro which aims to do :

1. Copy / Paste as values all the info I want to save (that's because some cells are based in dynamic formulas as for instance NOW());




2. "Save as" the file, trying to use the information defined in one specific cell of the file.

My macro looks like this:

Sub SaveMyWorkbook()

  Cells.Select

  Selection.Copy

  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

  :=False, Transpose:=False

  Sheets("Parameters").Select

  Cells.Select

  Application.CutCopyMode = False

  Selection.Copy

  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

  :=False, Transpose:=False

  Sheets("Demande d'Achat").Select

  Range("S3:U3").Select

  Application.CutCopyMode = False



  Dim strPath As String

  Dim strFolderPath As String



    strFolderPath = "\camo106remoteAchat_PurchasingDemande d'achat_Purchase request2019"

    strPath = strFolderPath & _

      Parameters.Range("D1").Value & ".xlsm"



   ActiveWorkbook.SaveAs Filename:=strPath, 

   FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

End Sub

However, when I debug there is always a problem in the line in which I try to create the name:

strPath = strFolderPath & _ Parameters.Range("D1").Value & ".xlsm"

Could anyone help me? I'm not at all an advanced user of macros, and I guess that this one is far away from my knowledge.

I'm using Excel 2010.

How does your filename look before saving? If you are using formulas like NOW() in the filename it can be the case that the it contains characters that are forbidden. You can check it in the immediate window when you step through the code right before you attempt to save the file.

after researching a little bit, and thanks to the comments of all of you, I finally got to make the macro works. Some recommendations:

1. The reference cells used to "built" the name of the file need to be in the active sheet; I found easier to do a formula in the file and after make reference to only one cell in the macro, than doing the formula in the macro itself, however both ways should work, meanwhile the reference is in the active sheet.


2. If you have dynamic calculated cells in your file, copy-paste in values before to save the file (I did an independent macro, but I also copied it as values if anyone needs it);


3. Be careful with FileFormat attribute in the command SaveAs; after reading several blogs, I preferred to use the numeric code than the description, but again, both ways should work.

See here the ultra simple final code of my macro (Excel 2010):

Sub Save()

  CopyValues

    'Sub CopyValues()

      'ActiveSheet.Unprotect

      'Columns("A:U").Select

      'Selection.Copy

      'Application.CutCopyMode = False

      'Selection.Locked = True

      'Selection.FormulaHidden = True

      'Columns("A:U").Select

      'Selection.Copy

      'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

      ':=False, Transpose:=False

      'Application.CutCopyMode = False

      'Range("G7").Select

      'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

    'End Sub



  Dim strPath As String

  Dim strFolderPath As String



    strFolderPath = "\camo106remoteAchat_PurchasingDemande d'achat_Purchase request2019"

    strPath = strFolderPath & _

      Range("A1").Value & ".xlsm"



    ActiveWorkbook.SaveAs Filename:=strPath, FileFormat:=52, ReadOnlyRecommended:=True, CreateBackup:=False

    GoToMainSheet

     'Sub GoToMainSheet()

       'Sheets("Demande d'Achat").Select

       'Range("S2").Select

     'End Sub



End Sub

I hope will be useful for someone else.