Print macro error in Excel -- cause?

Y

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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
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.)
 
Upvote 0
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()
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
VBA Macro Not Detecting Slicer Correctly in Excel Workbook
Replies
0
Views
261
bigodudo
B
Dan Wilson
  • Solved
Help with a Run-Time Error
Replies
4
Views
207
dreid1011
dreid1011
U
  • Question
recorded macro throwing 1004 error
Replies
3
Views
359
ursua
U
R
  • Question
Macro No Longer Working- Run-time error 80010108
Replies
1
Views
319
Anthony47
A
R
  • Question
Macros in unrelated files started crashing around three weeks ago and we cannot figure out why.
Replies
4
Views
638
Royzer
R

Forum statistics

Threads
1,214,405
Messages
6,119,320
Members
448,887
Latest member
AirOliver

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