Error 1004 save and close excel document

[cstuder]

I receive emails containing excel documents and they open as (excel) read only. I run two other macro's to format it, then I want to save and close it (by using the below code).
I get a 1004 error code on line ThisWorkbook.SaveAs rootDir & FileName & ".xlsx" Any suggestions on how to change the code so it saves properly?


Sub CHProIII()
'
' CHProIII Macro
' save as and close file
'
Dim rootDir As String
Dim FileName As String

rootDir = "G:\REBATES\CutlerHammer\CH Pro's\"
rootDir = Replace(rootDir, "@1", Application.UserName)

FileName = ActiveSheet.Range("J3").Value & " " & ActiveSheet.Range("J1").Value & ".xlsx"

ThisWorkbook.SaveAs rootDir & FileName & ".xlsx"

ThisWorkbook.Close

End Sub

 

[cstuder]

Well, it worked but when I open up the (saved) document it's blank. It didn't save the file I had open. Do I have something wrong? This is a shared drive. Would that have anything to do with it? Thanks for sticking with me.

 

[Joe4]

Is the workbook you are trying to save the same one where this VBA code resides?
Or do you have two workbooks open?
Are you trying to do this on a Desktop/Laptop computer or using Excel online?

 

[cstuder]

All of my macros are saved as "personal" and in a hidden workbook. So technically yes?
I only had one workbook open. I then closed excel to ensure nothing was open with the same result.
desktop

 

[Joe4]

All of my macros are saved as "personal" and in a hidden workbook. So technically yes?
I only had one workbook open. I then closed excel to ensure nothing was open with the same result.
desktop

That there is your problem then.
"ThisWorkbook" refers to the workbook this VBA procedure is found in. You do NOT want to save that one.
You want to save the other workbook with your data. That would be "ActiveWorkbook".

Try this (I am assuming that there is no VBA in your data workbook that you want to save):

Sub CHProIII()
'
' CHProIII Macro
' save as and close file
'
Dim rootDir As String
Dim FileName As String

rootDir = "G:\REBATES\CutlerHammer\CH Pro's\"
rootDir = Replace(rootDir, "@1", Application.UserName)

FileName = ActiveSheet.Range("J3").Value & " " & ActiveSheet.Range("J1").Value & ".xlsx"

ActiveWorkbook.SaveAs rootDir & FileName

ActiveWorkbook.Close

End Sub

 

[cstuder]

That did it! Made my day! I'll keep the Active Workbook and This Workbook in mind when trying to create macros. Thank you so much!

 

[Joe4]

You are welcome.

Yes, the thing to remember is:
ThisWorkbook = the workbook that the VBA module that is running is located in
ActiveWorkbook = the workbook that is active when the VBA code is running

Many times, these are "one and the same", i.e., when the VBA code exists in the workbook you are running it against.
But it is not in your case, since the VBA code is in your Personal Macros workbook.

 

[cstuder]

I'll keep this in mind and put in my notes. Thanks again!