this workbook before print

[Bill Kuunders]

This is a re-post with some additional info.

I have a macro to tidy up date formula's in column C on the "schedule"
sheet.
I call this macro in the this workbook - before print - module.
This part works fine as long I'm running any print macro's from the
"schedule" sheet.

It also works when I am not on the schedule sheet and I use the normal excel
print button.
I can see the system selecting the schedule sheet do its tidy up and go back
to the other current sheet. Sheets ("schedule").Select is part of the
macro.
But when I use a macro button on a different sheet with a print instruction
the system does not select the "schedule" sheet ..............and it does
only part of the tidy up macro.

Any solutions?

Regards
Bill K

 

[JulieD]

Hi Bill

have you stepped through the code (put a breakpoint at the top of the code &
use F8 to step through line by line) when initiated from another worksheet -
if it does part of the tidy up, it sounds like you've got a line in there
that causes it to "stop" ...
this happens to me when i've got a line like
ON ERROR RESUME NEXT
at the top of the code and then somewhere in it i do something silly etc etc

Alternatively post the code and we'll take a look.

Cheers
JulieD

 

[Bill Kuunders]

Julie
I have tried to run through using the f8 key and it just does not want to
select the SCHEDULE sheet. Only when the print job is not for the schedule
sheet and only if I try to print using a macro as below.

Sub PRINT_MILKORDER1()
Sheets("MILK ORDER").Select
Application.Goto Reference:="MILKORDER1"
ActiveSheet.PageSetup.PrintArea = "milkorder1"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("COVER").Select
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'tidy up date formulas after cut and paste
fixdates
End Sub

Sub fixdates()
Dim oldactive As Range
Set oldactive = Selection

Sheets("SCHEDULE").Select

Range("H6").FormulaR1C1 = "=IF(ISNUMBER(RC[1]),RC[1],R[-1]C)"
Range("H6").Select
Selection.AutoFill Destination:=Range("H6:H500"), Type:=xlFillDefault
Range("H6:H500").Select
oldactive.Parent.Parent.Activate
oldactive.Parent.Select
oldactive.Select

End Sub

I have also tried without the lines with oldactive in them.
Thank you for taking the time to check this out.
Bill K

 

[JulieD]

Hi Bill

i think i know what the problem is but short of calling the fixdates macro
from within this code i'm not sure how to fix it.

It appears that because you're using
ActiveWindow.SelectedSheets.PrintOut
(ie the SelectedSheets bit) that it won't let you select anyother sheet to
run the code on

so if you do this:

Sub PRINT_MILKORDER1()
Sheets("MILK ORDER").Select
Application.Goto Reference:="MILKORDER1"
ActiveSheet.PageSetup.PrintArea = "milkorder1" call fixdates
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("COVER").Select
End Sub

it appears to work.

However, some of the other people on this site who have better VBA skills
than me might be able to suggest an alternative.

Cheers
JulieD

Julie
I have tried to run through using the f8 key and it just does not want to
select the SCHEDULE sheet. Only when the print job is not for the schedule
sheet and only if I try to print using a macro as below.

Sub PRINT_MILKORDER1()
Sheets("MILK ORDER").Select
Application.Goto Reference:="MILKORDER1"
ActiveSheet.PageSetup.PrintArea = "milkorder1"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("COVER").Select
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'tidy up date formulas after cut and paste
fixdates
End Sub

Sub fixdates()
Dim oldactive As Range
Set oldactive = Selection

Sheets("SCHEDULE").Select

Range("H6").FormulaR1C1 = "=IF(ISNUMBER(RC[1]),RC[1],R[-1]C)"
Range("H6").Select
Selection.AutoFill Destination:=Range("H6:H500"), Type:=xlFillDefault
Range("H6:H500").Select
oldactive.Parent.Parent.Activate
oldactive.Parent.Select
oldactive.Select

End Sub

I have also tried without the lines with oldactive in them.
Thank you for taking the time to check this out.
Bill K

Hi Bill

have you stepped through the code (put a breakpoint at the top of the
code & use F8 to step through line by line) when initiated from another
worksheet - if it does part of the tidy up, it sounds like you've got a
line in there that causes it to "stop" ...
this happens to me when i've got a line like
ON ERROR RESUME NEXT
at the top of the code and then somewhere in it i do something silly etc
etc

Alternatively post the code and we'll take a look.

Cheers
JulieD

 

[Bill Kuunders]

Thanks Julie,

You are right .
It would be good to find a different macro print command, that does not
include the selected sheets bit.

At this stage I leave things as they are.
There are only two sheets involved in which the cell gets changed.
So for now that column is hidden.

To delete the before print command would mean that I would have to change
about
36 print macro's to include the fix-dates routine.

Thanks again
Bill K

Hi Bill

i think i know what the problem is but short of calling the fixdates macro
from within this code i'm not sure how to fix it.

It appears that because you're using
ActiveWindow.SelectedSheets.PrintOut
(ie the SelectedSheets bit) that it won't let you select anyother sheet to
run the code on

so if you do this:

Sub PRINT_MILKORDER1()
Sheets("MILK ORDER").Select
Application.Goto Reference:="MILKORDER1"
ActiveSheet.PageSetup.PrintArea = "milkorder1" call fixdates
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("COVER").Select
End Sub

it appears to work.

However, some of the other people on this site who have better VBA skills
than me might be able to suggest an alternative.

Cheers
JulieD

Julie
I have tried to run through using the f8 key and it just does not want to
select the SCHEDULE sheet. Only when the print job is not for the
schedule sheet and only if I try to print using a macro as below.

Sub PRINT_MILKORDER1()
Sheets("MILK ORDER").Select
Application.Goto Reference:="MILKORDER1"
ActiveSheet.PageSetup.PrintArea = "milkorder1"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("COVER").Select
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'tidy up date formulas after cut and paste
fixdates
End Sub

Sub fixdates()
Dim oldactive As Range
Set oldactive = Selection

Sheets("SCHEDULE").Select

Range("H6").FormulaR1C1 = "=IF(ISNUMBER(RC[1]),RC[1],R[-1]C)"
Range("H6").Select
Selection.AutoFill Destination:=Range("H6:H500"), Type:=xlFillDefault
Range("H6:H500").Select
oldactive.Parent.Parent.Activate
oldactive.Parent.Select
oldactive.Select

End Sub

I have also tried without the lines with oldactive in them.
Thank you for taking the time to check this out.
Bill K

Hi Bill

have you stepped through the code (put a breakpoint at the top of the
code & use F8 to step through line by line) when initiated from another
worksheet - if it does part of the tidy up, it sounds like you've got a
line in there that causes it to "stop" ...
this happens to me when i've got a line like
ON ERROR RESUME NEXT
at the top of the code and then somewhere in it i do something silly etc
etc

Alternatively post the code and we'll take a look.

Cheers
JulieD



This is a re-post with some additional info.

I have a macro to tidy up date formula's in column C on the "schedule"
sheet.
I call this macro in the this workbook - before print - module.
This part works fine as long I'm running any print macro's from the
"schedule" sheet.

It also works when I am not on the schedule sheet and I use the normal
excel print button.
I can see the system selecting the schedule sheet do its tidy up and go
back to the other current sheet. Sheets ("schedule").Select is part of
the macro.
But when I use a macro button on a different sheet with a print
instruction the system does not select the "schedule" sheet
..............and it does only part of the tidy up macro.

Any solutions?

Regards
Bill K