Print pages depending on range

I hope that some one can help I am attempting to do either of the 2 functions below and neither are working. I would like to either print a number of pages depending on a cell value or print a range of cells depending on a cell value. The worksheet is made up of 40 pages and every time it still prints the 40 pages regardless of wants in the cells value.

Any help will be greatly appreciated.

Sub PRINT_PAGES()

Sheets("SHEET1").Select
 Columns("D:G").Hidden = True
 Columns("I:I").Hidden = True
   a = Range("AD1").Value
   ActiveWindow.SelectedSheets.PrintOut From:=1, To:=a, Copies:=1
 Columns("D:I").Hidden = False
Sheets("MAINSHEET").Select

End Sub

OR

SUB PRINT RANGES()

Sheets("SHEET1").Select
 Columns("D:G").Hidden = True
 Columns("I:I").Hidden = True
ActiveSheet.PageSetup.PrintArea = "A1:U" & "AD2"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
 Columns("D:I").Hidden = False
Sheets("MAINSHEET").Select

End Sub

The page is set up to 40 pages long and it does not matter what I enter it still prints the 40 pages. If i set the range to a static number it will print the range but if I have to take the value from a cell it still only prints 40 pages. I would like to be able to print the number of pages depending on cell value,

Anyone's help would be appreciated.

Thanks

For your second macro I would suggest you change the line

ActiveSheet.PageSetup.PrintArea = "A1:U" & "AD2"

to

ActiveSheet.PageSetup.PrintArea = Range("A1:U" & Range("AD2").Value)

Alf

Originally Posted by Alf

For your second macro I would suggest you change the line

ActiveSheet.PageSetup.PrintArea = "A1:U" & "AD2"

to

ActiveSheet.PageSetup.PrintArea = Range("A1:U" & Range("AD2").Value)

Alf

Thanks for that but it still comes up with an error saying range of object global failed.

Regards

Does this work?

Sub prt_range()

Dim myRange As Range

Sheets("SHEET1").Select
 Columns("D:G").Hidden = True
 Columns("I:I").Hidden = True
 Set myRange = Range("A1:U" & Range("AD2"))
ActiveSheet.PageSetup.PrintArea = myRange
ActiveWindow.SelectedSheets.PrintOut Copies:=1
 Columns("D:I").Hidden = False
Sheets("MAINSHEET").Select

End Sub

Alf

Alf

Unfortunately no

Set myRange = Range("A1:U" & Range("AD2"))

Still coming up with a fail. (range of object. global failed)

I thought that printing a number of pages depending on a cell value or printing a range depending on a cell value would be quite an easy task. This seems to be posing big problems.

Regards

Sorry, I'm out of ideas. Hopefully somebody else can help you.

Alf