0

So I am trying to create a journal entry template that will create the file name based on named ranges within the worksheet and save it in the current folder the template is saved in. Below is the code I have thus far but it keeps debugging at the ActiveWorkbook.SaveAs line. Also, I have confirmed that the named ranges work correctly by pulling them into the spreadsheet with a macro just to make sure that wasn't a problem.

Sub Save()


Dim Initials As String
Dim Entity As String
Dim Create_Date As String
Dim Batch As String
Dim Journal_Name As String

Initials = Range("Initials")
Entity = Range("Entity")
Create_Date = Range("Date")
Batch = Range("Batch")
Journal_Name = Range("Journal_Name")

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Initials & "-" & Entity & "-" & Create_Date & "-" & Batch & "-" & Journal_Name & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    
End Sub

I have the below code that I patterned this one after from another file that works fine so I can't figure out why this one is debugging, someone please help! The ; isn't what is causing it to debug, that is from the code I copied from that is actually working. I'm not using the ; in the code that is debugging.

Sub Format()

Dim Year As Integer
Dim Month1
Dim Month2

Year = InputBox("Enter the Year", "Year", "YYYY")
Month1 = InputBox("Enter the Month", "Month", "M")
Month2 = WorksheetFunction.Text(Month1, "00")

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Year & Month2 & "-janusus;import-actual-" & Year & "M" & Month1 & "-R.csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=False

End Sub
Jamie Walker
  • 213
  • 5
  • 10
  • 24
  • That ; is from the code that is actually working, the one I pulled the new code from, so that isn't the issue. I'm wondering if my Create_Date isn't formatted correctly and that is causing an issue. – Jamie Walker Aug 17 '22 at 14:21
  • 1
    Then you likely have a problematic character in `Create_Date`. LIkely either a backslash or a forward slash. Please remove the second code block as it's entirely irrelevant to your question. – BigBen Aug 17 '22 at 14:23
  • 1
    Debugging hint: `Debug.Print ThisWorkbook.Path & "\" & Initials & "-" & Entity & "-" & Create_Date & "-" & Batch & "-" & Journal_Name & ".xlsm"` and inspect the output in the Immediate Window. – BigBen Aug 17 '22 at 14:26
  • Ok, that might be it. I am pulling it from the sheet and it is formatted like 08172022 with the Custom cell format of mmddyyyy which is how I want formatted in my file name. I'm assuming the macro is pulling it in as 8/17/22. How do I get it formatted like the above for my macro? – Jamie Walker Aug 17 '22 at 14:30
  • 1
    Using `Format$`. `Create_Date = Format$(Range("Date"),"mmddyyyy")`. – BigBen Aug 17 '22 at 14:31
  • 1
    Side note: you don't need to "assume". You can use `Debug.Print` as commented above. Other side note. Formatting and the underlying value in a cell are two separate things. – BigBen Aug 17 '22 at 14:33
  • Yea, I'm trying to get the Debug.Print to work but I have never used it before, I imagine it would come in handy. – Jamie Walker Aug 17 '22 at 14:37
  • 1
    Yes, extremely handy. Right before the `.SaveAs` line, put `Debug.Print ThisWorkbook.Path & "\" & Initials & "-" & Entity & "-" & Create_Date & "-" & Batch & "-" & Journal_Name & ".xlsm"`. Then use Ctrl+G to open the Immediate Window. Rerun the code. You'll see the (invalid) filename in the Immediate Window. – BigBen Aug 17 '22 at 14:39
  • 1
    I appreciate all your help. I went with a workaround and used CONCATENATE to go ahead and build the file name within the worksheet and it made the code a lot simpler. That way I didn't have to worry about formatting the Create_Date. That is good to know about the Debug.Print though, I used it and saw what the problem was and just found an easier workaround. – Jamie Walker Aug 17 '22 at 15:11

0 Answers0