Macro to print specified range of several work sheets

  • Thread starter Thread starter paul mullan
  • Start date Start date
P

paul mullan

Hi I am really struggling to create code to do the following task and
would really appreciate any help:

I have a workbook containing 52 worksheets (named 1 - 52).

I want the same range (A1:AQ104) to be printed but each worksheet
printed on a seperate page

I want to create a macro that when activated will prompt what range of
worksheets are being selected to print (eg 4-10). I was thinking that
I needed to create a userform to achieve this with two seperate spin
buttons to make reference to the "from" and "to" worksheets.

Any helps at least just to set me off in the right direction would be
greatly recieved.

Thanks all
 
Hi I am really struggling to create code to do the following task and

would really appreciate any help:



I have a workbook containing 52 worksheets (named 1 - 52).



I want the same range (A1:AQ104) to be printed but each worksheet

printed on a seperate page



I want to create a macro that when activated will prompt what range of

worksheets are being selected to print (eg 4-10). I was thinking that

I needed to create a userform to achieve this with two seperate spin

buttons to make reference to the "from" and "to" worksheets.



Any helps at least just to set me off in the right direction would be

greatly recieved.



Thanks all
Click to expand...

I am going to assume good design in that your sheet 1 is the FIRST, etc

sub printsheets()
fs=inputbox("first sheet?")
ls-inputbox("last sheet")
for i= fs to ls
sheets(i).range(A1:"AQ104").printout
next i
end sub
 
Thanks for this.

What I am after is a way to select the range of worksheets you want to
print therefore not always set to print all worksheets.

So far I have got this far by setting up a userform with a combobox
and command button that allows me to select a single worksheet (though
the code doesn't actually work as the command button doesn't bring up
the print menu!). What I would like is 2 seperate combo boxes 1) print
worksheet FROM and 2) print worksheet TO, thereby specifying a range
that can be chjanged each time.

Private Sub CommandButton1_Click()
ThisWorkbook.Sheets(Me.ComboBox1.Text).PrintOut Copies:=1,
Collate:=True
End Sub

Private Sub UserForm_Initialize()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
Me.ComboBox1.AddItem ws.Name
Next
End Sub

any pointers, as always greatly appreciated
 
Back
Top