[RESOLVED] Active Workbook name Reference error

[fjm0614@yahoo.com]

I have created a template named “Stock Quantity Calculator 2007 Template 10K a2.xltm” with many macro in it. When opening a new workbook with this template I am getting errors because some of the code is looking for the template name not the newly created workbook name.

What would be the code to replace the original workbook name with the current\active workbook name? As you can see it occurs (2) times in this one macro.



Sub O_All_Button()
'
' O_All_Button Macro
'

'
Application.Run _
"'Stock Quantity Calculator 2007 Template 10K a2.xltm'!UnHide_all"

Sheets("0 Usage Data By Sto-Loc").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Branch Numbers").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Format Data MRP ").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Upload MRP").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Upload MRP RDC").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Format Data RDC").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("0 Stock Cost").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Input Cost Data").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("STO Cost").Select
ActiveWindow.SelectedSheets.Visible = False

Application.Run _
"'Stock Quantity Calculator 2007 Template 10K a2.xltm' O_Usage_All_SLoc"

Sheets("0 Usage Data ALL Sto-Loc").Select
ActiveWindow.SelectedSheets.Visible = False
End Sub

[westconn1]

when working with multiple workbooks or as this case a workbook created from a template, you should avoid all references to select, activesheet or active anything else,
use fully qualified paths to the required ranges or sheets

most of the above code can be converted like

sheets("some sheet").visible = false

if working with multiple workbooks the workbook should also be specified
if the code applies to a workbook opened from a template, possibly use

thisworkbook.sheets("some sheet").visible = false

recording of macros is great to generate sample code, but it needs some conversion for real applications

[fjm0614@yahoo.com]

Thanks for your reply.

The fix to the name issue was to remove this code

Application.Run _
"'Stock Quantity Calculator 2007 Template 10K a2.xltm'!UnHide_all"

And replace with

Call UnHide_all

Worked Great!!!