Hello All..

I am trying to get my worksheets to play nicely..but no success.

I have say 10 worksheets, and in cell "B4" I have Client Copy (across all
sheets), so it needs to print all those sheets...
Then I need to change "B4" to Delivery Copy, then print all those sheets,
etc etc

Problem is at the moment, it goes through all the sheets, and only prints
the Dock Copy sheets (the last set). I see the text at "B4" change to what it
should, but just keeps going and ignoring the print call.

Sub delivery_copy()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
With sh
Range("E4").Select
ActiveCell = "Delivery Copy"
End With
Next sh
Worksheets.PrintOut

End Sub

I am using this in four seperate modules, each called from Sub
Workbook_BeforePrint.
As always, any advice is welcomed.

Regards
Peter

Hi Peter

You missed a dot before .Range(............

Sub delivery_copy()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
With sh
.Range("E4").Value = "Delivery Copy"
End With
Next sh
Worksheets.PrintOut
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Peter" wrote in message ...
Hello All..

I am trying to get my worksheets to play nicely..but no success.

I have say 10 worksheets, and in cell "B4" I have Client Copy (across all
sheets), so it needs to print all those sheets...
Then I need to change "B4" to Delivery Copy, then print all those sheets,
etc etc

Problem is at the moment, it goes through all the sheets, and only prints
the Dock Copy sheets (the last set). I see the text at "B4" change to what it
should, but just keeps going and ignoring the print call.

Sub delivery_copy()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
With sh
Range("E4").Select
ActiveCell = "Delivery Copy"
End With
Next sh
Worksheets.PrintOut

End Sub

I am using this in four seperate modules, each called from Sub
Workbook_BeforePrint.
As always, any advice is welcomed.

Regards
Peter

Hi Ron,
Thanks for the dot =)
Unfortunatly it's still not printing the sheets the way they should be.

It dosn't seem to stop after it enters the new text into "B4" and print what
is on the sheets.
It should do this:
<=====call sub client_copy
Change B4 to Client Copy on all sheets.
Print all sheets
<=====call sub admin_copy
Change B4 to Admin Copy on all sheets
Print all sheets
<=====call sub delivery_copy
Change B4 to Delivery Copy on all sheets
Print all sheets
<=====call sub dock_copy
Change B4 to Dock Copy on all sheets
Print all sheets

Instead it gives the correct amount of pages, just all with Dock Copy on them.

Any idea's what I may have missed?

Regards
Peter


"Ron de Bruin" wrote:

Hi Peter

You missed a dot before .Range(............

Sub delivery_copy()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
With sh
.Range("E4").Value = "Delivery Copy"
End With
Next sh
Worksheets.PrintOut
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Peter" wrote in message ...
Hello All..

I am trying to get my worksheets to play nicely..but no success.

I have say 10 worksheets, and in cell "B4" I have Client Copy (across all
sheets), so it needs to print all those sheets...
Then I need to change "B4" to Delivery Copy, then print all those sheets,
etc etc

Problem is at the moment, it goes through all the sheets, and only prints
the Dock Copy sheets (the last set). I see the text at "B4" change to what it
should, but just keeps going and ignoring the print call.

Sub delivery_copy()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
With sh
Range("E4").Select
ActiveCell = "Delivery Copy"
End With
Next sh
Worksheets.PrintOut

End Sub

I am using this in four seperate modules, each called from Sub
Workbook_BeforePrint.
As always, any advice is welcomed.

Regards
Peter

Try this (all in one sub)

Sub Printtest()
Dim sh As Worksheet
Dim Astr
Dim N As Long

Astr = Array("Delivery Copy", "Client Copy", "Admin Copy", "dock_copy")
For N = LBound(Astr) To UBound(Astr)

For Each sh In ThisWorkbook.Worksheets
With sh
.Range("E4").Value = Astr(N)
End With
Next sh
Worksheets.PrintOut
Next N
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Peter" wrote in message ...
Hi Ron,
Thanks for the dot =)
Unfortunatly it's still not printing the sheets the way they should be.

It dosn't seem to stop after it enters the new text into "B4" and print what
is on the sheets.
It should do this:
<=====call sub client_copy
Change B4 to Client Copy on all sheets.
Print all sheets
<=====call sub admin_copy
Change B4 to Admin Copy on all sheets
Print all sheets
<=====call sub delivery_copy
Change B4 to Delivery Copy on all sheets
Print all sheets
<=====call sub dock_copy
Change B4 to Dock Copy on all sheets
Print all sheets

Instead it gives the correct amount of pages, just all with Dock Copy on them.

Any idea's what I may have missed?

Regards
Peter


"Ron de Bruin" wrote:

Hi Peter

You missed a dot before .Range(............

Sub delivery_copy()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
With sh
.Range("E4").Value = "Delivery Copy"
End With
Next sh
Worksheets.PrintOut
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Peter" wrote in message ...
Hello All..

I am trying to get my worksheets to play nicely..but no success.

I have say 10 worksheets, and in cell "B4" I have Client Copy (across all
sheets), so it needs to print all those sheets...
Then I need to change "B4" to Delivery Copy, then print all those sheets,
etc etc

Problem is at the moment, it goes through all the sheets, and only prints
the Dock Copy sheets (the last set). I see the text at "B4" change to what it
should, but just keeps going and ignoring the print call.

Sub delivery_copy()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
With sh
Range("E4").Select
ActiveCell = "Delivery Copy"
End With
Next sh
Worksheets.PrintOut

End Sub

I am using this in four seperate modules, each called from Sub
Workbook_BeforePrint.
As always, any advice is welcomed.

Regards
Peter

How about a single routine that does all 4 prints. Not tested, but

Sub delivery_copy()

Dim sh As Worksheet
Dim myval As String

For x = 1 To 4
Select Case x
Case 1: myval = "Client Copy"
Case 2: myval = "Delivery Copy"
Case 3: myval = "Filing Copy"
Case 4: myval = "Dock Copy"
End Select

For Each sh In ThisWorkbook.Worksheets
sh.Range("E4").Value = myval
Next sh
Worksheets.PrintOut
Next x

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Peter" wrote in message
...
Hello All..

I am trying to get my worksheets to play nicely..but no success.

I have say 10 worksheets, and in cell "B4" I have Client Copy (across all
sheets), so it needs to print all those sheets...
Then I need to change "B4" to Delivery Copy, then print all those sheets,
etc etc

Problem is at the moment, it goes through all the sheets, and only prints
the Dock Copy sheets (the last set). I see the text at "B4" change to what
it
should, but just keeps going and ignoring the print call.

Sub delivery_copy()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
With sh
Range("E4").Select
ActiveCell = "Delivery Copy"
End With
Next sh
Worksheets.PrintOut

End Sub

I am using this in four seperate modules, each called from Sub
Workbook_BeforePrint.
As always, any advice is welcomed.

Regards
Peter

Thanks Again Ron,
Still doing exactly the same thing though.

It cycles through all the page names on each page then prints only the last
one.

The page about to be printed pop's up on screen, then the B4 range changes
really fast from Client Copy through to Dock Copy, then prints the Dock Copy.

Regards
Peter

"Ron de Bruin" wrote:

Try this (all in one sub)

Sub Printtest()
Dim sh As Worksheet
Dim Astr
Dim N As Long

Astr = Array("Delivery Copy", "Client Copy", "Admin Copy", "dock_copy")
For N = LBound(Astr) To UBound(Astr)

For Each sh In ThisWorkbook.Worksheets
With sh
.Range("E4").Value = Astr(N)
End With
Next sh
Worksheets.PrintOut
Next N
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Peter" wrote in message ...
Hi Ron,
Thanks for the dot =)
Unfortunatly it's still not printing the sheets the way they should be.

It dosn't seem to stop after it enters the new text into "B4" and print what
is on the sheets.
It should do this:
<=====call sub client_copy
Change B4 to Client Copy on all sheets.
Print all sheets
<=====call sub admin_copy
Change B4 to Admin Copy on all sheets
Print all sheets
<=====call sub delivery_copy
Change B4 to Delivery Copy on all sheets
Print all sheets
<=====call sub dock_copy
Change B4 to Dock Copy on all sheets
Print all sheets

Instead it gives the correct amount of pages, just all with Dock Copy on them.

Any idea's what I may have missed?

Regards
Peter


"Ron de Bruin" wrote:

Hi Peter

You missed a dot before .Range(............

Sub delivery_copy()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
With sh
.Range("E4").Value = "Delivery Copy"
End With
Next sh
Worksheets.PrintOut
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Peter" wrote in message ...
Hello All..

I am trying to get my worksheets to play nicely..but no success.

I have say 10 worksheets, and in cell "B4" I have Client Copy (across all
sheets), so it needs to print all those sheets...
Then I need to change "B4" to Delivery Copy, then print all those sheets,
etc etc

Problem is at the moment, it goes through all the sheets, and only prints
the Dock Copy sheets (the last set). I see the text at "B4" change to what it
should, but just keeps going and ignoring the print call.

Sub delivery_copy()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
With sh
Range("E4").Select
ActiveCell = "Delivery Copy"
End With
Next sh
Worksheets.PrintOut

End Sub

I am using this in four seperate modules, each called from Sub
Workbook_BeforePrint.
As always, any advice is welcomed.

Regards
Peter

Hi Ken,
Sorry, still doing it with your suggestion as well...
Is it possible that another macro is interfering with it?
I have run all these attempts from the macro run command as well as in the
Workbook module. Same result each time though.

Regards
Peter (with slightly less but more grey hair)

"Ken Wright" wrote:

How about a single routine that does all 4 prints. Not tested, but

Sub delivery_copy()

Dim sh As Worksheet
Dim myval As String

For x = 1 To 4
Select Case x
Case 1: myval = "Client Copy"
Case 2: myval = "Delivery Copy"
Case 3: myval = "Filing Copy"
Case 4: myval = "Dock Copy"
End Select

For Each sh In ThisWorkbook.Worksheets
sh.Range("E4").Value = myval
Next sh
Worksheets.PrintOut
Next x

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Peter" wrote in message
...
Hello All..

I am trying to get my worksheets to play nicely..but no success.

I have say 10 worksheets, and in cell "B4" I have Client Copy (across all
sheets), so it needs to print all those sheets...
Then I need to change "B4" to Delivery Copy, then print all those sheets,
etc etc

Problem is at the moment, it goes through all the sheets, and only prints
the Dock Copy sheets (the last set). I see the text at "B4" change to what
it
should, but just keeps going and ignoring the print call.

Sub delivery_copy()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
With sh
Range("E4").Select
ActiveCell = "Delivery Copy"
End With
Next sh
Worksheets.PrintOut

End Sub

I am using this in four seperate modules, each called from Sub
Workbook_BeforePrint.
As always, any advice is welcomed.

Regards
Peter

Hi Peter

It is working for me.
Send me your test workbook private and I take a look at it.



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Peter" wrote in message ...
Thanks Again Ron,
Still doing exactly the same thing though.

It cycles through all the page names on each page then prints only the last
one.

The page about to be printed pop's up on screen, then the B4 range changes
really fast from Client Copy through to Dock Copy, then prints the Dock Copy.

Regards
Peter

"Ron de Bruin" wrote:

Try this (all in one sub)

Sub Printtest()
Dim sh As Worksheet
Dim Astr
Dim N As Long

Astr = Array("Delivery Copy", "Client Copy", "Admin Copy", "dock_copy")
For N = LBound(Astr) To UBound(Astr)

For Each sh In ThisWorkbook.Worksheets
With sh
.Range("E4").Value = Astr(N)
End With
Next sh
Worksheets.PrintOut
Next N
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Peter" wrote in message ...
Hi Ron,
Thanks for the dot =)
Unfortunatly it's still not printing the sheets the way they should be.

It dosn't seem to stop after it enters the new text into "B4" and print what
is on the sheets.
It should do this:
<=====call sub client_copy
Change B4 to Client Copy on all sheets.
Print all sheets
<=====call sub admin_copy
Change B4 to Admin Copy on all sheets
Print all sheets
<=====call sub delivery_copy
Change B4 to Delivery Copy on all sheets
Print all sheets
<=====call sub dock_copy
Change B4 to Dock Copy on all sheets
Print all sheets

Instead it gives the correct amount of pages, just all with Dock Copy on them.

Any idea's what I may have missed?

Regards
Peter


"Ron de Bruin" wrote:

Hi Peter

You missed a dot before .Range(............

Sub delivery_copy()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
With sh
.Range("E4").Value = "Delivery Copy"
End With
Next sh
Worksheets.PrintOut
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Peter" wrote in message ...
Hello All..

I am trying to get my worksheets to play nicely..but no success.

I have say 10 worksheets, and in cell "B4" I have Client Copy (across all
sheets), so it needs to print all those sheets...
Then I need to change "B4" to Delivery Copy, then print all those sheets,
etc etc

Problem is at the moment, it goes through all the sheets, and only prints
the Dock Copy sheets (the last set). I see the text at "B4" change to what it
should, but just keeps going and ignoring the print call.

Sub delivery_copy()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
With sh
Range("E4").Select
ActiveCell = "Delivery Copy"
End With
Next sh
Worksheets.PrintOut

End Sub

I am using this in four seperate modules, each called from Sub
Workbook_BeforePrint.
As always, any advice is welcomed.

Regards
Peter

Please let us know Ron, because I'm curious now as well :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip

Hi Ken

The problem was that Peter have a sheet event running on the workbook

This is working

Sub Printtest()
Dim sh As Worksheet
Dim Astr
Dim N As Long
Application.EnableEvents = False
Astr = Array("Delivery Copy", "Client Copy", "Admin Copy", "dock_copy")
For N = LBound(Astr) To UBound(Astr)

For Each sh In ThisWorkbook.Worksheets
With sh
.Range("E4").Value = Astr(N)
End With
Next sh
Worksheets.PrintOut
Next N
Application.EnableEvents = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ken Wright" wrote in message ...
Please let us know Ron, because I'm curious now as well :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip

Cheers Ron :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip

Thankyou Ron for the excellent detective work...
I never even realised it was running in the background.
All working like a charm =)

Regards
Peter

"Peter" wrote:

Hi Ken,
Sorry, still doing it with your suggestion as well...
Is it possible that another macro is interfering with it?
I have run all these attempts from the macro run command as well as in the
Workbook module. Same result each time though.

Regards
Peter (with slightly less but more grey hair)

"Ken Wright" wrote:

How about a single routine that does all 4 prints. Not tested, but

Sub delivery_copy()

Dim sh As Worksheet
Dim myval As String

For x = 1 To 4
Select Case x
Case 1: myval = "Client Copy"
Case 2: myval = "Delivery Copy"
Case 3: myval = "Filing Copy"
Case 4: myval = "Dock Copy"
End Select

For Each sh In ThisWorkbook.Worksheets
sh.Range("E4").Value = myval
Next sh
Worksheets.PrintOut
Next x

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Peter" wrote in message
...
Hello All..

I am trying to get my worksheets to play nicely..but no success.

I have say 10 worksheets, and in cell "B4" I have Client Copy (across all
sheets), so it needs to print all those sheets...
Then I need to change "B4" to Delivery Copy, then print all those sheets,
etc etc

Problem is at the moment, it goes through all the sheets, and only prints
the Dock Copy sheets (the last set). I see the text at "B4" change to what
it
should, but just keeps going and ignoring the print call.

Sub delivery_copy()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
With sh
Range("E4").Select
ActiveCell = "Delivery Copy"
End With
Next sh
Worksheets.PrintOut

End Sub

I am using this in four seperate modules, each called from Sub
Workbook_BeforePrint.
As always, any advice is welcomed.

Regards
Peter

Thanks for the feedback Peter

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Peter" wrote in message ...
Thankyou Ron for the excellent detective work...
I never even realised it was running in the background.
All working like a charm =)

Regards
Peter

"Peter" wrote:

Hi Ken,
Sorry, still doing it with your suggestion as well...
Is it possible that another macro is interfering with it?
I have run all these attempts from the macro run command as well as in the
Workbook module. Same result each time though.

Regards
Peter (with slightly less but more grey hair)

"Ken Wright" wrote:

How about a single routine that does all 4 prints. Not tested, but

Sub delivery_copy()

Dim sh As Worksheet
Dim myval As String

For x = 1 To 4
Select Case x
Case 1: myval = "Client Copy"
Case 2: myval = "Delivery Copy"
Case 3: myval = "Filing Copy"
Case 4: myval = "Dock Copy"
End Select

For Each sh In ThisWorkbook.Worksheets
sh.Range("E4").Value = myval
Next sh
Worksheets.PrintOut
Next x

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Peter" wrote in message
...
Hello All..

I am trying to get my worksheets to play nicely..but no success.

I have say 10 worksheets, and in cell "B4" I have Client Copy (across all
sheets), so it needs to print all those sheets...
Then I need to change "B4" to Delivery Copy, then print all those sheets,
etc etc

Problem is at the moment, it goes through all the sheets, and only prints
the Dock Copy sheets (the last set). I see the text at "B4" change to what
it
should, but just keeps going and ignoring the print call.

Sub delivery_copy()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
With sh
Range("E4").Select
ActiveCell = "Delivery Copy"
End With
Next sh
Worksheets.PrintOut

End Sub

I am using this in four seperate modules, each called from Sub
Workbook_BeforePrint.
As always, any advice is welcomed.

Regards
Peter