Subroutine Works in ThisWorkbook Module, Not in Worksheet Module

[ptd001]

Hello,

I have a subroutine (see below) that works perfectly well in the "ThisWorkbook" module; however, when I place it in a worksheet module, I receive an "Method 'Select' of object'_Worksheet' failed" message. I expect there's an obvious reason that escapes me. For various reasons, I need to be able to run this subroutine (or one that accomplishes the same thing) in a worksheet. Any help would be much appreciated.

Sub ROICPrints()
On Error GoTo errcatch
With ThisWorkbook

'Remove page breaks & add page breaks
Sheets("ROIC").ResetAllPageBreaks

Sheets("ROIC").Rows(55).PageBreak = xlPageBreakManual
Sheets("ROIC").Rows(85).PageBreak = xlPageBreakManual


'PRINT FORMAT SUMMARY TAB
Sheets("ROIC").PageSetup.LeftHeader = "&B" & Range("Summary!K2") & " " & "ROIC Summary" & "&B"
Sheets("ROIC").PageSetup.CenterHeader = ""
Sheets("ROIC").PageSetup.RightHeader = ""
Sheets("ROIC").PageSetup.LeftFooter = "© " & Format(Now, "yyyy") & " Morningstar, Inc. All Rights Reserved."
Sheets("ROIC").PageSetup.CenterFooter = "Page " & "&P" & "/" & "&N"
Sheets("ROIC").PageSetup.RightFooter = Format(Now(), "mmmm dd yyyy")
Sheets("ROIC").PageSetup.LeftMargin = Application.InchesToPoints(0.75)
Sheets("ROIC").PageSetup.RightMargin = Application.InchesToPoints(0.75)
Sheets("ROIC").PageSetup.TopMargin = Application.InchesToPoints(1)
Sheets("ROIC").PageSetup.BottomMargin = Application.InchesToPoints(1)
Sheets("ROIC").PageSetup.HeaderMargin = Application.InchesToPoints(0.5)
Sheets("ROIC").PageSetup.FooterMargin = Application.InchesToPoints(0.5)
Sheets("ROIC").PageSetup.PrintTitleRows = "$1:$9"
Sheets("ROIC").PageSetup.PrintTitleColumns = ""
Sheets("ROIC").PageSetup.PrintArea = "$A$11:$T$54, $A$55:$T$84"
Sheets("ROIC").PageSetup.Orientation = xlPortrait
Sheets("ROIC").PageSetup.Zoom = 80
Sheets("ROIC").PageSetup.FitToPagesWide = 1
Sheets("ROIC").PageSetup.FitToPagesTall = 1
Sheets("ROIC").PageSetup.CenterHorizontally = True
Sheets("ROIC").PageSetup.CenterVertically = False


End With

ThisWorkbook.Worksheets("ROIC").PrintPreview

Exit Sub

errcatch:
MsgBox Err.Description

End Sub

 

[Norie]

You aren't selecting anything in that code.

Is it definitely this code that's causing the error?

By the way, this isn't doing anything.

With ThisWorkbook

 

[ptd001]

Not sure what to say. I copied and pasted the code. When in ThisWorkbook, it works fine and generates a print preview window. When in a worksheet module, it errors out. Thoughts?

 

[JLGWhiz]

Hello,

I have a subroutine (see below) that works perfectly well in the "ThisWorkbook" module; however, when I place it in a worksheet module, I receive an "Method 'Select' of object'_Worksheet' failed" message. I expect there's an obvious reason that escapes me. For various reasons, I need to be able to run this subroutine (or one that accomplishes the same thing) in a worksheet. Any help would be much appreciated.

Sub ROICPrints()
On Error GoTo errcatch
With ThisWorkbook

'Remove page breaks & add page breaks
Sheets("ROIC").ResetAllPageBreaks

Sheets("ROIC").Rows(55).PageBreak = xlPageBreakManual
Sheets("ROIC").Rows(85).PageBreak = xlPageBreakManual


'PRINT FORMAT SUMMARY TAB
Sheets("ROIC").PageSetup.LeftHeader = "&B" & Range("Summary!K2") & " " & "ROIC Summary" & "&B"
Sheets("ROIC").PageSetup.CenterHeader = ""
Sheets("ROIC").PageSetup.RightHeader = ""
Sheets("ROIC").PageSetup.LeftFooter = "© " & Format(Now, "yyyy") & " Morningstar, Inc. All Rights Reserved."
Sheets("ROIC").PageSetup.CenterFooter = "Page " & "&P" & "/" & "&N"
Sheets("ROIC").PageSetup.RightFooter = Format(Now(), "mmmm dd yyyy")
Sheets("ROIC").PageSetup.LeftMargin = Application.InchesToPoints(0.75)
Sheets("ROIC").PageSetup.RightMargin = Application.InchesToPoints(0.75)
Sheets("ROIC").PageSetup.TopMargin = Application.InchesToPoints(1)
Sheets("ROIC").PageSetup.BottomMargin = Application.InchesToPoints(1)
Sheets("ROIC").PageSetup.HeaderMargin = Application.InchesToPoints(0.5)
Sheets("ROIC").PageSetup.FooterMargin = Application.InchesToPoints(0.5)
Sheets("ROIC").PageSetup.PrintTitleRows = "$1:$9"
Sheets("ROIC").PageSetup.PrintTitleColumns = ""
Sheets("ROIC").PageSetup.PrintArea = "$A$11:$T$54, $A$55:$T$84"
Sheets("ROIC").PageSetup.Orientation = xlPortrait
Sheets("ROIC").PageSetup.Zoom = 80
Sheets("ROIC").PageSetup.FitToPagesWide = 1
Sheets("ROIC").PageSetup.FitToPagesTall = 1
Sheets("ROIC").PageSetup.CenterHorizontally = True
Sheets("ROIC").PageSetup.CenterVertically = False


End With

ThisWorkbook.Worksheets("ROIC").PrintPreview

Exit Sub

errcatch:
MsgBox Err.Description

End Sub

I would put it in the standard code module 1 or any of the numbered modules if you are using more than one. All the numbered modules are public and regular Sub procedures that are listed in those will show up in the macro listing when you call up the macro dialog box. If you put regular subs in the ThisWorkbook, Worksheet or UserForm code modules, they do not appear in the macro list, because Excel considers procedures in those modules as Private. The Private procedures genereally apply to event code and specilized functions that relate to events of controls or workbook objects and sheets. You are probably getting the message because you do not have a period in front of the Sheet to tie it to the With ThisWorkbook statement.

With ThisWorkbook
    .Sheets("Sheet1").blah blah, etc.
End With

 

[ptd001]

Thanks, but I do need to place it in a worksheet module... Any thoughts as to why it works in ThisWorkbook but not in a worksheet module? Like I said, I've tested the exact same code...

 

[JLGWhiz]

Thanks, but I do need to place it in a worksheet module... Any thoughts as to why it works in ThisWorkbook but not in a worksheet module? Like I said, I've tested the exact same code...

See my edited statement. Last sentence.

 

[Norie]

Why does it need to be in a worksheet module?

 

[ptd001]

Adding a period before the Sheets statements did not change anything. I need to add the code to a worksheet module, because (the abbreviated version) I need to add code to files that have their vb locked. The only way I know to do so (without using sendkeys) is to import a spreadsheet with the code in it.

 

[ptd001]

FWIW - it seems that the problem is the PageSetup object, i.e. it works in "ThisWorkbook" but fails in any worksheet module. Any tips?