0

I really liked the solution here that provided VBA that to force save as an xlsm file. Keeping in mind that I barely know anything about VBA, how could I modify this code to do the following:

  • continue to force save as xlsm
  • file name equals the value of cell C1 in a sheet named "REPORT" + the text "BSC" + the date in YYYY.MM.DD format

Any help would be greatly appreciated.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
David W Harrington
  • 1
  • 1
  • 1
  • 1
    so I was feeling generous today however on that note, you really should at least try to learn instead of wanting someone to do it for you...again I was feeling generous so you should be feeling lucky. `Dim sFileName As String sFileName = Sheets("REPORT").Range("C1").Value & " BCS " & Format(DateTime.Now, "yyyy.mm.dd") & ".xlsm" ActiveWorkbook.SaveAs sFileName` – Sorceri Apr 22 '16 at 19:31
  • I agree with Sorceri, and was still editing when that comment was posted. I just saw it. This is right on the border of what should or should not be answered. But something about the way you asked it made it seem like helping would take you to the next level.. My answer is giving you some of that, but you will have to piece it together to complete the puzzle. Good luck. You can do it. – peege Apr 22 '16 at 19:38

1 Answers1

1

If you take the line where it says:

ThisWorkbook.SaveAs Filename:=FileNameVal & ".xlsm", FileFormat:=ThisWorkbook.FileFormat 
        Application.EnableEvents = True

You are saving the file as whatever value is saved under FileNameVal

Using some basic variables, you can set strings and replace the value of FileNameVal

Dim newFileName As String, nameDate As String, sheet As String, tempVal As String

    sheet = "REPORT"
    tempVal = Sheets(sheet).Range("C1").Text
    nameDate = Format(Date, "yyyy.mm.dd")
    newFileName = tempVal & "BSC" & nameDate

Look at the Locals Window and you can see the values change as you step through the code using F8.

Locals Window

REPORT Sheet

peege
  • 2,467
  • 1
  • 10
  • 24
  • This is meant to give you some of the missing elements. You should be able to finish from here. – peege Apr 22 '16 at 19:40
  • Thanks for the help, peege and Sorceri. This is the first time I have really needed to try my hand at any kind of VBA, and I am far from a coder in most of my life, but if I get a few pieces of very specific advice related to a problem I am working on, I tend to be able to make myself competent enough to ask really good questions later on about, say, HTML, CSS, or PHP. So this will help. – David W Harrington Apr 25 '16 at 13:39