Workbook name Reference error

J

Javi

Active Member
Joined
May 26, 2011
Messages
440
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.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Sub O_All_Button()
'
' O_All_Button Macro
'
<o:p></o:p>
'
Application.Run _
"'Stock Quantity Calculator 2007 Template 10K a2.xltm'!UnHide_all"
<o:p></o:p>
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Code: 
Sub O_All_Button()
'
' O_All_Button Macro
'

'
[COLOR="Red"]Call UnHide_all[/COLOR]

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

[COLOR="Red"]Call O_Usage_All_SLoc[/COLOR]

Sheets("0 Usage Data ALL Sto-Loc").Select
ActiveWindow.SelectedSheets.Visible = False
End Sub
 
Upvote 0
AlphaFrog said:
Code: 
Sub O_All_Button()
'
' O_All_Button Macro
'

'
[COLOR="Red"]Call UnHide_all[/COLOR]

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

[COLOR="Red"]Call O_Usage_All_SLoc[/COLOR]

Sheets("0 Usage Data ALL Sto-Loc").Select
ActiveWindow.SelectedSheets.Visible = False
End Sub
Click to expand...
Worked Great!! Thank you!!!
 
Upvote 0
You're welcome.

Tip:

This...
Code: 
Sheets(Array("0 Usage Data By Sto-Loc", "Branch Numbers", "Format Data MRP ", _
             "Upload MRP RDC", "Format Data RDC", "0 Stock Cost", _
             "Input Cost Data", "STO Cost")).Visible = False

Can replace this...
Code: 
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
 
Upvote 0
Nice way to clean things up (Thank You)!.

I just posted a sort ? Maybe you can look at it!!!

Excel auto filter VBA code help needed
I have a sheet with data in columns A thur F and the auto filter set on. In Column E (field 5) the sort is working well it filter all values as needed (I only wanted 0 selected). My problem is the next 2 sorts won’t work.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
I would like to have everything selected but 0 and blanks to be excluded. I need to order to be Column D (Field 4) then Column F (Field 6). Below is what I tried and failed.
<o:p> </o:p>
<o:p> </o:p>
Sub O_Use_StoLoc_Sheet_Macro()
'
' O_Use_StoLoc_Sheet_Macro Macro
'
ActiveSheet.Unprotect

Columns("A:F").Select
Selection.AutoFilter

ActiveSheet.Range("$A$1:$F$40001").AutoFilter Field:=5, Criteria1:="0"
ActiveSheet.Range("$A$1:$F$40001").AutoFilter Field:=4, Criteria1:="<>"0, _
ActiveSheet.Range("$A$1:$F$40001").AutoFilter Field:=6, Criteria1:="<>"0, _
<o:p> </o:p>
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
<o:p> </o:p>
End Sub

 
Upvote 0
Column E = 0 and (Columns D and F <> 0 and <> Blanks)

Code: 
Sub O_Use_StoLoc_Sheet_Macro()
'
' O_Use_StoLoc_Sheet_Macro Macro
'
    ActiveSheet.Unprotect

    With Range("$A$1:$F$40001")
        .AutoFilter
        .AutoFilter Field:=5, Criteria1:="0"
        .AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlAnd, Criteria2:="<>"
        .AutoFilter Field:=6, Criteria1:="<>0", Operator:=xlAnd, Criteria2:="<>"
    End With

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
                        AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True

End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
VBA code to export specific variably named tabs to new workbook?
Replies
2
Views
636
thebigejr
T
E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P
M
  • Question Question
Create new workbook for every sheet in active workbook, starting from Sheet(3) and saving them to a user specified folder location.
Replies
0
Views
979
Mars84
M
M
  • Question Question
Rename Workbook Name with Entered value
Replies
1
Views
574
Joe4
Joe4
G
  • Question Question
VBA. Save sheets as values in separate workbooks. The problem is, all data in original file is saved as metadata.
Replies
0
Views
818
Gloffo
G

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top