HoosierDaddy311
New Member
- Joined
- Jul 29, 2013
- Messages
- 2
Hello,
I am posting this question to the forum after reviewing several other posts trying to find a solution with no success.
OS - Windows XP
Office version - Office 2010
What the issue is - I have developed an Excel 2010 template with macro's (.xltm) that has some sales data pasted into it, and then that data is processed through a "workflow" within the template. Everything works just fine until the very final macro called "Finalize_And_Save_For_PDC_to_Upload" that dynamically saves the file using a filename that is created in another cell (I1) as a variable (FilenameValue) which is a formula that grabs text portions from other cells and strings them together to create a standard file-naming convention. Also used in the path to be saved in is another
week number variable that is in cell (I3) which is basically a =MID formula pulling the 2 digit week # from another cell. The final piece of this is when the macro below is run.
What the issue is - only SOMETIMES the Run-time error '1004' Method 'SaveAs' of object '_Workbook' failed error is thrown, but NOT ALL THE TIME. This is the point of confusion as if it failed ALL THE TIME, I can understand there is something inherently wrong with the code, but as I mentioned sometimes it works just fine, while other times it fails with the '1004' error code. I have 40+ users using this template and this morning I had a user that was able to run it once just fine, but the next one run threw the error and there is nothing I can see that is different (aside from the filename).
Here is complete VB code being used with the portion of the code bold & italicized that is highlighted in yellow upon clicking the Debug button on the error code window:
Sub Finalize_And_Save_For_PDC_to_Upload()
'
' Finalize_And_Save_For_PDC_to_Upload Macro
'
Dim FilenameValue As String
Dim FilenameWeekNo As String
Sheets("Data").Select
ActiveSheet.ShowAllData
Application.DisplayAlerts = False
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets(Array("DND List", "40% Reduced Allocation Stores", "Store ROG", "CIC's", _
"Store's", "$5 Friday DND List", "$5 Friday Store List", "New Store & Model Store" _
)).Select
Sheets("DND List").Activate
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets(Array("DND List", "40% Reduced Allocation Stores", "Store ROG", "CIC's", _
"Store's", "$5 Friday DND List", "$5 Friday Store List", "New Store & Model Store" _
)).Select
Sheets("$5 Friday Store List").Activate
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
Sheets("Cover Sheet").Select
Range("F2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
FilenameValue = Range("I1")
FilenameWeekNo = Range("I3")
ActiveWorkbook.SaveAs Filename:= _
"\\pleefs01\Data\Orion\Business Tracker Scan\2013\WK " & FilenameWeekNo & "\" & FilenameValue & ".xlsx", FileFormat _
:=xlOpenXMLWorkbook, CreateBackup:=False
Sheets("Pivot Summary").Select
ActiveWindow.SelectedSheets.Visible = False
Application.DisplayAlerts = True
End Sub
When it errors out and before the user clicks the Debug button, I can see the window name of the file contains the filename the macro created, however upon clicking Debug and seeing the bold & italicized portion of the macro highlighted in yellow, once the user closes the VBA window, the filename reverts back to the name it was upon opening the template (which is MEDS Allocation Template1 - Microsoft Excel)
Why would this VBA work sometimes but not others? Is there something I need to change to ensure it WORKS EVERY TIME it's run? Any help would be greatly appreciated!
Thanks for reviewing this post and I look forward to any possible solutions out there!
I am posting this question to the forum after reviewing several other posts trying to find a solution with no success.
OS - Windows XP
Office version - Office 2010
What the issue is - I have developed an Excel 2010 template with macro's (.xltm) that has some sales data pasted into it, and then that data is processed through a "workflow" within the template. Everything works just fine until the very final macro called "Finalize_And_Save_For_PDC_to_Upload" that dynamically saves the file using a filename that is created in another cell (I1) as a variable (FilenameValue) which is a formula that grabs text portions from other cells and strings them together to create a standard file-naming convention. Also used in the path to be saved in is another
week number variable that is in cell (I3) which is basically a =MID formula pulling the 2 digit week # from another cell. The final piece of this is when the macro below is run.
What the issue is - only SOMETIMES the Run-time error '1004' Method 'SaveAs' of object '_Workbook' failed error is thrown, but NOT ALL THE TIME. This is the point of confusion as if it failed ALL THE TIME, I can understand there is something inherently wrong with the code, but as I mentioned sometimes it works just fine, while other times it fails with the '1004' error code. I have 40+ users using this template and this morning I had a user that was able to run it once just fine, but the next one run threw the error and there is nothing I can see that is different (aside from the filename).
Here is complete VB code being used with the portion of the code bold & italicized that is highlighted in yellow upon clicking the Debug button on the error code window:
Sub Finalize_And_Save_For_PDC_to_Upload()
'
' Finalize_And_Save_For_PDC_to_Upload Macro
'
Dim FilenameValue As String
Dim FilenameWeekNo As String
Sheets("Data").Select
ActiveSheet.ShowAllData
Application.DisplayAlerts = False
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets(Array("DND List", "40% Reduced Allocation Stores", "Store ROG", "CIC's", _
"Store's", "$5 Friday DND List", "$5 Friday Store List", "New Store & Model Store" _
)).Select
Sheets("DND List").Activate
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets(Array("DND List", "40% Reduced Allocation Stores", "Store ROG", "CIC's", _
"Store's", "$5 Friday DND List", "$5 Friday Store List", "New Store & Model Store" _
)).Select
Sheets("$5 Friday Store List").Activate
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
Sheets("Cover Sheet").Select
Range("F2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
FilenameValue = Range("I1")
FilenameWeekNo = Range("I3")
ActiveWorkbook.SaveAs Filename:= _
"\\pleefs01\Data\Orion\Business Tracker Scan\2013\WK " & FilenameWeekNo & "\" & FilenameValue & ".xlsx", FileFormat _
:=xlOpenXMLWorkbook, CreateBackup:=False
Sheets("Pivot Summary").Select
ActiveWindow.SelectedSheets.Visible = False
Application.DisplayAlerts = True
End Sub
When it errors out and before the user clicks the Debug button, I can see the window name of the file contains the filename the macro created, however upon clicking Debug and seeing the bold & italicized portion of the macro highlighted in yellow, once the user closes the VBA window, the filename reverts back to the name it was upon opening the template (which is MEDS Allocation Template1 - Microsoft Excel)
Why would this VBA work sometimes but not others? Is there something I need to change to ensure it WORKS EVERY TIME it's run? Any help would be greatly appreciated!
Thanks for reviewing this post and I look forward to any possible solutions out there!