I have some code that is working fine, however the reference to each worksheet is based on the visible sheet name in the workbook. I want to change this to the CodeName “ (Name) property” in the VBE so that the sheets are referenced regardless of what the visible sheet is called.
In this example, you will see that I have the sheets in Excel named “2011 … something” which means that when the tabs are updated to 2012, I will get an error and then need to change the code.
The first example runs correctly as follows:
SetupToPrint “2011 Agent YTD ALL Sales”
I have changed the (Name) property value to EverythingAgentOnlySalesTo
How can I pass this string so that in the private sub “SetupToPrint”, EverythingAgentOnlySalesTo
Print is activated and then the rest of the routine is ran on that worksheet?
In the old format, to activate the sheets in Sub SetupToPrint the following was used:
I have tried just passing the string as:
sh.activate but I compile error: Invalid qualifier
Also, as you can see on line 6 and 7, I tried to pass the sheet name with the addition of .Name, such as “GroupSalesToPrint.Name" however these did not work either.
In the screenshot,
the black arrows work as 1 goes into 2, however I want to make the blue arrows work by executing SetupToPrint with the active sheet being the (Name) which is called.
Code is attached below.
Also - I asked this question, which was similar, I wanted to use the (Name) instead of the sheetname, but it was for an array:
Thank you in advance!
Application.ScreenUpdating = False
SetupToPrint "EverythingAgentOnlySalesToPrint" '"2011 Agent YTD ALL Sales"
' SetupToPrint "GroupSalesToPrint.Name" '"2011 Agent YTD Sales Group"
' SetupToPrint "MyBlueSalesToPrint.Name" '"2011 Agent YTD Sales My Blue"
' SetupToPrint "MedicareSalesToPrint" '"2011 Agent YTD Sales Medicare"
' SetupToPrint "SalesByProductToPrint" '"Sales by Product"
Application.ScreenUpdating = True
Private Sub SetupToPrint(sh As String)
Private Sub SetPrintAreaToPivotTable()
lPTcells = .PivotTables("PivotTable1").DataBodyRange.Cells.Count
Set rngTopLeft = .PivotTables("PivotTable1").RowRange.Cells(1)
Set rngBotRight = .PivotTables("PivotTable1").DataBodyRange.Cells(lPTcells)
strPTAddress = rngTopLeft.Address & ":" & rngBotRight.Address 'strPT address don't exist!
.PageSetup.PrintArea = strAddress
Private Sub SetPageBreakToXNumberOfRows()
Dim LastRow As Long
Dim Row_Index As Long
Dim RW As Long
RW = 48 'How many rows do you want between each page break
.ResetAllPageBreaks 'Remove all PageBreaks
LastRow = .Cells(Rows.Count, "D").End(xlUp).Row 'Search for the last row with data in Column D
For Row_Index = RW + 2 To LastRow Step RW
.HPageBreaks.Add Before:=.Cells(Row_Index, 1)
Private Sub PageBreakUnderlining()
Dim pb As HPageBreak
For Each pb In .HPageBreaks
LastRow = pb.Location.Offset(-1, 0).Row
.LineStyle = xlContinuous
.Weight = xlThin