this workbook before print
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 |
this workbook before print
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" wrote in message ... 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 |
this workbook before print
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" wrote in message ... 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" wrote in message ... 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 |
this workbook before print
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 "Bill Kuunders" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
this workbook before print
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 "JulieD" wrote in message ... 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 "Bill Kuunders" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
All times are GMT +1. The time now is 03:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com