Print Code Help

L

LiAD

Afternoon,

Hi I'm trying to get a code to cycle through sheets 4 to 11 to print out
certain sheets based on whether there is data in it or not, (the value in
cell AA273 is either 1 or 2). I'm trying to get the code to cycle through
the sheets automatically to print out the set ranges.

The code prints ok, but it will print sheet that I am currently on several
times rather than printing 4,5,6...11. In order to save some trees I have
shortened the range on the code I'm using as per below to try and only print
a few sheets until i get it to work. This code has printed twice sheet 11
and twice sheet 4 as I was on these sheets when i ran the code.

How do I get it to print each sheet once rather than one sheet loads of times?



Thanks
LiAD

ub PrintSheetsMacro()
'
Dim X As Long
For X = 10 To 11
Worksheets(X).Rows("111:214").EntireRow.Hidden = False
If [AA273].Value = 2 Then
Rows("267:274").EntireRow.Hidden = False
Range("C266:p325").Select
ActiveSheet.PageSetup.PrintArea = "$C$266:$P$325"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("C206:p265").Select
ActiveSheet.PageSetup.PrintArea = "$C$206:$P$265"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Rows("111:214").Select
Selection.EntireRow.Hidden = True
Rows("267:274").Select
Selection.EntireRow.Hidden = True
Range("B10").Select
Else
Range("C206:p265").Select
ActiveSheet.PageSetup.PrintArea = "$C$206:$P$265"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Rows("111:214").Select
Selection.EntireRow.Hidden = True
Range("B10").Select

End If

Next

End Sub
 
S

Sam Wilson

Change

ActiveSheet.PageSetup.PrintArea = "$C$266:$P$325"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

to

Worksheets(X).PageSetup.PrintArea = "$C$266:$P$325"

etc
 
D

Don Guillett

You need to use WITH. Notice the dots placement. I think I read your desires
properly. But, why are you un-hiding/hiding rows 111:214 when you don't
seem to use them. You may want to delete those 2 lines.

sub PrintSheetsMacroSAS()
Dim X As double
For X = 4 To 11
with Worksheets(X)

.Rows("111:214").Hidden = False'???
If .range("AA273").Value = 2 Then
.Rows("267:274").Hidden = False
.Range("C266:p325").PrintOut
.Rows("267:274").Hidden = True
End If
.range("C206:p265").PrintOut
.rows("111:214").Hidden = True'???

end with
Next
End Sub
 
D

Don Guillett

Actually, I guess you want to print the earlier section first.

sub PrintSheetsMacroSAS()
Dim X As double
For X = 4 To 11
with Worksheets(X)
.range("C206:p265").PrintOut
'AND
If .range("AA273").Value = 2 Then
.Rows("267:274").Hidden = False
.Range("C266:p325").PrintOut
.Rows("267:274").Hidden = True
End If
end with
Next
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Don Guillett said:
You need to use WITH. Notice the dots placement. I think I read your
desires properly. But, why are you un-hiding/hiding rows 111:214 when you
don't seem to use them. You may want to delete those 2 lines.

sub PrintSheetsMacroSAS()
Dim X As double
For X = 4 To 11
with Worksheets(X)

.Rows("111:214").Hidden = False'???
If .range("AA273").Value = 2 Then
.Rows("267:274").Hidden = False
.Range("C266:p325").PrintOut
.Rows("267:274").Hidden = True
End If
.range("C206:p265").PrintOut
.rows("111:214").Hidden = True'???

end with
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
LiAD said:
Afternoon,

Hi I'm trying to get a code to cycle through sheets 4 to 11 to print out
certain sheets based on whether there is data in it or not, (the value in
cell AA273 is either 1 or 2). I'm trying to get the code to cycle
through
the sheets automatically to print out the set ranges.

The code prints ok, but it will print sheet that I am currently on
several
times rather than printing 4,5,6...11. In order to save some trees I
have
shortened the range on the code I'm using as per below to try and only
print
a few sheets until i get it to work. This code has printed twice sheet
11
and twice sheet 4 as I was on these sheets when i ran the code.

How do I get it to print each sheet once rather than one sheet loads of
times?



Thanks
LiAD

ub PrintSheetsMacro()
'
Dim X As Long
For X = 10 To 11
Worksheets(X).Rows("111:214").EntireRow.Hidden = False
If [AA273].Value = 2 Then
Rows("267:274").EntireRow.Hidden = False
Range("C266:p325").Select
ActiveSheet.PageSetup.PrintArea = "$C$266:$P$325"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("C206:p265").Select
ActiveSheet.PageSetup.PrintArea = "$C$206:$P$265"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Rows("111:214").Select
Selection.EntireRow.Hidden = True
Rows("267:274").Select
Selection.EntireRow.Hidden = True
Range("B10").Select
Else
Range("C206:p265").Select
ActiveSheet.PageSetup.PrintArea = "$C$206:$P$265"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Rows("111:214").Select
Selection.EntireRow.Hidden = True
Range("B10").Select

End If

Next

End Sub
Click to expand...
Click to expand...
 
L

LiAD

Yeah seems backwards to hide/unhide but there are things that i need to hide
to make ti easier for the user to use the file but i need to print as the
printed version goes to other people.

Works perfect thanks a lot for your help

Don Guillett said:
Actually, I guess you want to print the earlier section first.

sub PrintSheetsMacroSAS()
Dim X As double
For X = 4 To 11
with Worksheets(X)
.range("C206:p265").PrintOut
'AND
If .range("AA273").Value = 2 Then
.Rows("267:274").Hidden = False
.Range("C266:p325").PrintOut
.Rows("267:274").Hidden = True
End If
end with
Next
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Don Guillett said:
You need to use WITH. Notice the dots placement. I think I read your
desires properly. But, why are you un-hiding/hiding rows 111:214 when you
don't seem to use them. You may want to delete those 2 lines.

sub PrintSheetsMacroSAS()
Dim X As double
For X = 4 To 11
with Worksheets(X)

.Rows("111:214").Hidden = False'???
If .range("AA273").Value = 2 Then
.Rows("267:274").Hidden = False
.Range("C266:p325").PrintOut
.Rows("267:274").Hidden = True
End If
.range("C206:p265").PrintOut
.rows("111:214").Hidden = True'???

end with
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
LiAD said:
Afternoon,

Hi I'm trying to get a code to cycle through sheets 4 to 11 to print out
certain sheets based on whether there is data in it or not, (the value in
cell AA273 is either 1 or 2). I'm trying to get the code to cycle
through
the sheets automatically to print out the set ranges.

The code prints ok, but it will print sheet that I am currently on
several
times rather than printing 4,5,6...11. In order to save some trees I
have
shortened the range on the code I'm using as per below to try and only
print
a few sheets until i get it to work. This code has printed twice sheet
11
and twice sheet 4 as I was on these sheets when i ran the code.

How do I get it to print each sheet once rather than one sheet loads of
times?



Thanks
LiAD

ub PrintSheetsMacro()
'
Dim X As Long
For X = 10 To 11
Worksheets(X).Rows("111:214").EntireRow.Hidden = False
If [AA273].Value = 2 Then
Rows("267:274").EntireRow.Hidden = False
Range("C266:p325").Select
ActiveSheet.PageSetup.PrintArea = "$C$266:$P$325"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("C206:p265").Select
ActiveSheet.PageSetup.PrintArea = "$C$206:$P$265"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Rows("111:214").Select
Selection.EntireRow.Hidden = True
Rows("267:274").Select
Selection.EntireRow.Hidden = True
Range("B10").Select
Else
Range("C206:p265").Select
ActiveSheet.PageSetup.PrintArea = "$C$206:$P$265"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Rows("111:214").Select
Selection.EntireRow.Hidden = True
Range("B10").Select

End If

Next

End Sub
Click to expand...
Click to expand...
Click to expand...
 
D

Don Guillett

Why unhide>print>hide if there are NO selections for the user to even see
and you are not printing that section???

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
LiAD said:
Yeah seems backwards to hide/unhide but there are things that i need to
hide
to make ti easier for the user to use the file but i need to print as the
printed version goes to other people.

Works perfect thanks a lot for your help

Don Guillett said:
Actually, I guess you want to print the earlier section first.

sub PrintSheetsMacroSAS()
Dim X As double
For X = 4 To 11
with Worksheets(X)
.range("C206:p265").PrintOut
'AND
If .range("AA273").Value = 2 Then
.Rows("267:274").Hidden = False
.Range("C266:p325").PrintOut
.Rows("267:274").Hidden = True
End If
end with
Next
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Don Guillett said:
You need to use WITH. Notice the dots placement. I think I read your
desires properly. But, why are you un-hiding/hiding rows 111:214 when
you
don't seem to use them. You may want to delete those 2 lines.

sub PrintSheetsMacroSAS()
Dim X As double
For X = 4 To 11
with Worksheets(X)

.Rows("111:214").Hidden = False'???
If .range("AA273").Value = 2 Then
.Rows("267:274").Hidden = False
.Range("C266:p325").PrintOut
.Rows("267:274").Hidden = True
End If
.range("C206:p265").PrintOut
.rows("111:214").Hidden = True'???

end with
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Afternoon,

Hi I'm trying to get a code to cycle through sheets 4 to 11 to print
out
certain sheets based on whether there is data in it or not, (the value
in
cell AA273 is either 1 or 2). I'm trying to get the code to cycle
through
the sheets automatically to print out the set ranges.

The code prints ok, but it will print sheet that I am currently on
several
times rather than printing 4,5,6...11. In order to save some trees I
have
shortened the range on the code I'm using as per below to try and only
print
a few sheets until i get it to work. This code has printed twice
sheet
11
and twice sheet 4 as I was on these sheets when i ran the code.

How do I get it to print each sheet once rather than one sheet loads
of
times?



Thanks
LiAD

ub PrintSheetsMacro()
'
Dim X As Long
For X = 10 To 11
Worksheets(X).Rows("111:214").EntireRow.Hidden = False
If [AA273].Value = 2 Then
Rows("267:274").EntireRow.Hidden = False
Range("C266:p325").Select
ActiveSheet.PageSetup.PrintArea = "$C$266:$P$325"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("C206:p265").Select
ActiveSheet.PageSetup.PrintArea = "$C$206:$P$265"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Rows("111:214").Select
Selection.EntireRow.Hidden = True
Rows("267:274").Select
Selection.EntireRow.Hidden = True
Range("B10").Select
Else
Range("C206:p265").Select
ActiveSheet.PageSetup.PrintArea = "$C$206:$P$265"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Rows("111:214").Select
Selection.EntireRow.Hidden = True
Range("B10").Select

End If

Next

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Page Breaks in VBA Q 6
Print two different areas on a worksheet 5
Printing Macro 4
Option Box on user from to update workbook 6
Help adding a condition to a print code 1
Command Button 6
VBA ERROR 2
macro to print a sheet if a cell is a certain value 1

Top