Print macro error in Excel -- cause?

Yepper

Board Regular
Joined
Apr 18, 2002
Messages
68
Hello. In Excel 97 (SR-2), I am suddenly experiencing a problem with a print macro that I had recorded several years ago. In general, the macro simply hides certain columns, prints the worksheet, and then unhides the column. The error I am now receiving when I run the macro is: "Run-time error '1004': Unable to set the Hidden property of the range class." When I select the Debug button, the following line in the macro is highlighted: Selection.EntireColumn.Hidden = True.

What does this mean and how do I correct this? I can't figure out what change I made to the worksheet that would cause this error. I am very much a novice when it comes to macros (I can record them, but I don't know how to interpret the underlying codes), so any suggestions in real simple terms would be much appreciated.

Thanks!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Yepper

Board Regular
Joined
Apr 18, 2002
Messages
68
The worksheet may or may not be protected. The macro is designed to select unprotect before hiding the columns. (I just double-checked this right now by unprotecting the worksheet first. I still received the same error message when running the macro.)
 

Yepper

Board Regular
Joined
Apr 18, 2002
Messages
68

ADVERTISEMENT

Here is the code. The line that was highlighted is about the 14th line:

Columns("J:R").Select
ActiveWindow.SmallScroll ToRight:=10
Range("J:R,W:AE").Select
Range("W1").Activate
ActiveWindow.SmallScroll ToRight:=12
Range("J:R,W:AE,AJ:AR").Select
Range("AJ1").Activate
ActiveWindow.SmallScroll ToRight:=15
Range("J:R,W:AE,AJ:AR,AW:BE").Select
Range("AW1").Activate
ActiveWindow.SmallScroll ToRight:=15
Range("J:R,W:AE,AJ:AR,AW:BE,BN:CD").Select
Range("BN1").Activate
Selection.EntireColumn.Hidden = True
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&A"
.RightFooter = "&D &T &F"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.4)
.BottomMargin = Application.InchesToPoints(0.4)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Cells.Select
Selection.EntireColumn.Hidden = False
Range("J6").Select
Application.Goto Reference:="JulyCol"
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=ActiveCell
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = "$A:$I"
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&A"
.RightFooter = "&D &T &F"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.4)
.BottomMargin = Application.InchesToPoints(0.4)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 38
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("J6").Select
End Sub
Sub Print_All()
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I can't see a line that says:

ActiveSheet.Unprotect

before the line which hides the column.
 

Yepper

Board Regular
Joined
Apr 18, 2002
Messages
68
Hmmm. You're absolutely right. I have posted below, the what should be the same macro from a back-up file (a file where the macro does work). It appears that the first 3 lines of the code are missing from the file that is generating the error. Do you have any idea how the first 3 lines could have been erased? Is there anything that I could have accidentally -- yet easily -- done to do this? Do I just type the first 3 lines back into the code to fix it? Thanks...

ActiveSheet.Unprotect
Cells.Select
ActiveSheet.ResetAllPageBreaks
Columns("J:R").Select
ActiveWindow.SmallScroll ToRight:=10
Range("J:R,W:AE").Select
Range("W1").Activate
ActiveWindow.SmallScroll ToRight:=12
Range("J:R,W:AE,AJ:AR").Select
Range("AJ1").Activate
ActiveWindow.SmallScroll ToRight:=15
Range("J:R,W:AE,AJ:AR,AW:BE").Select
Range("AW1").Activate
ActiveWindow.SmallScroll ToRight:=15
Range("J:R,W:AE,AJ:AR,AW:BE,BN:CD").Select
Range("BN1").Activate
Selection.EntireColumn.Hidden = True
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&A"
.RightFooter = "&D &T &F"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.4)
.BottomMargin = Application.InchesToPoints(0.4)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Cells.Select
Selection.EntireColumn.Hidden = False
Range("J6").Select
Application.Goto Reference:="JulyCol"
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=ActiveCell
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = "$A:$I"
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&A"
.RightFooter = "&D &T &F"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.4)
.BottomMargin = Application.InchesToPoints(0.4)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 38
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("J6").Select
End Sub
Sub Print_All()
'
' Print_All Macro
' Macro recorded 6/15/00 by John Yep
'

'
ActiveSheet.Unprotect
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$1:$AI$106"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&A"
.RightFooter = "&D &T &F"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.4)
.BottomMargin = Application.InchesToPoints(0.4)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = "$A:$I"
End With
ActiveSheet.PageSetup.PrintArea = "$AJ$1:$BR$106"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&A"
.RightFooter = "&D &T &F"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.4)
.BottomMargin = Application.InchesToPoints(0.4)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:="JulyCol"
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = "$A:$I"
End With
ActiveSheet.PageSetup.PrintArea = "$AJ$1:$BR$106"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&A"
.RightFooter = "&D &T &F"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.4)
.BottomMargin = Application.InchesToPoints(0.4)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 38
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("J6").Select
End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Just copy the missing lines back in.

I expect some careless person inadvertently deleted them. Don't know who that could be though, except that I'm sure it wasn't me.
 

Forum statistics

Threads
1,144,310
Messages
5,723,640
Members
422,506
Latest member
mdindas

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
Top