Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does macro change print range?
I am using Excel 2002 SP3 and have a spreadsheet with some columns I do not
want to print on the printout for my customers. If I manually select the columns and hide them and then use Print all is fine ... but when I record what I do in a macro the printout omits 4 columns prior to the 3 that I hide and prints out a meaningless report. I have looked at the code and it appears to hide the correct columns. The only other thing I can think of is that I am using the 'Scale to fit one page wide' to make the report fit ... the printout from the macro looks like it could be at 100% scaling. Help! what (if anything) am I doing wrong? -- Rob Harrison |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does macro change print range?
Post your code so we can take a look.
"robertmharrison" wrote: I am using Excel 2002 SP3 and have a spreadsheet with some columns I do not want to print on the printout for my customers. If I manually select the columns and hide them and then use Print all is fine ... but when I record what I do in a macro the printout omits 4 columns prior to the 3 that I hide and prints out a meaningless report. I have looked at the code and it appears to hide the correct columns. The only other thing I can think of is that I am using the 'Scale to fit one page wide' to make the report fit ... the printout from the macro looks like it could be at 100% scaling. Help! what (if anything) am I doing wrong? -- Rob Harrison |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does macro change print range?
Here is latest version ... its pretty simple - though using the macro record
function means there are spurious bits where I was moving back and forth across columns to select the ones I wanted to hide. What it should do is move from the front worksheet (which is like a control panel where you can select the client whose data you want to print by clicking on the relevant button) .... then filter the data to show only that client's data (uses autofilter on a column with client's name) ... then add a title with the client's name to cells F2:I2 (which are merged) ... select and hide columns I:K (which contain account management charges the customer should not see) ... print the sheet (hopefully using the Page Setup information already on the sheet) ... select colummns H:L to unhide columns I:K ... delete the title with the client's name ... reset the autofilter to show all records ... go back to the front panel worksheet. When I step through the macro it shows columns E:K appear to be hidden, rather than I:K and I can't see why ... VB code below. Best Regards Rob Sub Client() ' ' Client Macro ' Macro recorded 09/03/2005 by Your User Name ' ' Sheets("Input Data").Select Selection.AutoFilter Field:=3, Criteria1:="Client1" Range("F2:I2").Select Selection.ClearContents ActiveCell.FormulaR1C1 = "Client1 (Client Expenses)" Range("F3").Select ActiveWindow.SmallScroll ToRight:=6 Columns("I:K").Select Selection.EntireColumn.Hidden = True ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Columns("H:L").Select Selection.EntireColumn.Hidden = False ActiveWindow.SmallScroll ToRight:=-1 Range("F2:I2").Select Selection.ClearContents ActiveWindow.LargeScroll ToRight:=-1 Selection.AutoFilter Field:=3 Sheets("Control Panel").Select End Sub "Jim Thomlinson" wrote: Post your code so we can take a look. "robertmharrison" wrote: I am using Excel 2002 SP3 and have a spreadsheet with some columns I do not want to print on the printout for my customers. If I manually select the columns and hide them and then use Print all is fine ... but when I record what I do in a macro the printout omits 4 columns prior to the 3 that I hide and prints out a meaningless report. I have looked at the code and it appears to hide the correct columns. The only other thing I can think of is that I am using the 'Scale to fit one page wide' to make the report fit ... the printout from the macro looks like it could be at 100% scaling. Help! what (if anything) am I doing wrong? -- Rob Harrison |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does macro change print range?
Excel has problems with the combination of Hiding Columns and FitToPage. HOWEVER: instead of hiding a column set its width to 0.0001 and your layout s/b as expected. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam robertmharrison wrote : I am using Excel 2002 SP3 and have a spreadsheet with some columns I do not want to print on the printout for my customers. If I manually select the columns and hide them and then use Print all is fine ... but when I record what I do in a macro the printout omits 4 columns prior to the 3 that I hide and prints out a meaningless report. I have looked at the code and it appears to hide the correct columns. The only other thing I can think of is that I am using the 'Scale to fit one page wide' to make the report fit ... the printout from the macro looks like it could be at 100% scaling. Help! what (if anything) am I doing wrong? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does macro change print range?
Thanks for your reply. In this case the problem was even more stupid ... the
macro put the report title in a merged cell. The hide column included one element of the merged cell so the macro hid all the columns included in the merged report title cell. I demerged the cell and remerged it with the cell in the column to be hidden omitted and it works! The stupid thing is that if you do the same thing manually it works even if the meged cell has an element from the column to be hidden! Best regards Rob Harrison "keepITcool" wrote: Excel has problems with the combination of Hiding Columns and FitToPage. HOWEVER: instead of hiding a column set its width to 0.0001 and your layout s/b as expected. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam robertmharrison wrote : I am using Excel 2002 SP3 and have a spreadsheet with some columns I do not want to print on the printout for my customers. If I manually select the columns and hide them and then use Print all is fine ... but when I record what I do in a macro the printout omits 4 columns prior to the 3 that I hide and prints out a meaningless report. I have looked at the code and it appears to hide the correct columns. The only other thing I can think of is that I am using the 'Scale to fit one page wide' to make the report fit ... the printout from the macro looks like it could be at 100% scaling. Help! what (if anything) am I doing wrong? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does macro change print range? CURED IT!
CURED THE PROBLEM ... see response to KeepItCool.
This is a funny one .... when running the macro it objects to the fact the merged cell for the report title includes a cell from the column to be hidden and hides all the columns in the merged cell. The quirk is that when I do it manually this does not happen. Demerged the report title cell and remerged it without the cell from the column to be hidden and it works fine! Best regards Rob Harrison "robertmharrison" wrote: Here is latest version ... its pretty simple - though using the macro record function means there are spurious bits where I was moving back and forth across columns to select the ones I wanted to hide. What it should do is move from the front worksheet (which is like a control panel where you can select the client whose data you want to print by clicking on the relevant button) .... then filter the data to show only that client's data (uses autofilter on a column with client's name) ... then add a title with the client's name to cells F2:I2 (which are merged) ... select and hide columns I:K (which contain account management charges the customer should not see) ... print the sheet (hopefully using the Page Setup information already on the sheet) ... select colummns H:L to unhide columns I:K ... delete the title with the client's name ... reset the autofilter to show all records ... go back to the front panel worksheet. When I step through the macro it shows columns E:K appear to be hidden, rather than I:K and I can't see why ... VB code below. Best Regards Rob Sub Client() ' ' Client Macro ' Macro recorded 09/03/2005 by Your User Name ' ' Sheets("Input Data").Select Selection.AutoFilter Field:=3, Criteria1:="Client1" Range("F2:I2").Select Selection.ClearContents ActiveCell.FormulaR1C1 = "Client1 (Client Expenses)" Range("F3").Select ActiveWindow.SmallScroll ToRight:=6 Columns("I:K").Select Selection.EntireColumn.Hidden = True ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Columns("H:L").Select Selection.EntireColumn.Hidden = False ActiveWindow.SmallScroll ToRight:=-1 Range("F2:I2").Select Selection.ClearContents ActiveWindow.LargeScroll ToRight:=-1 Selection.AutoFilter Field:=3 Sheets("Control Panel").Select End Sub "Jim Thomlinson" wrote: Post your code so we can take a look. "robertmharrison" wrote: I am using Excel 2002 SP3 and have a spreadsheet with some columns I do not want to print on the printout for my customers. If I manually select the columns and hide them and then use Print all is fine ... but when I record what I do in a macro the printout omits 4 columns prior to the 3 that I hide and prints out a meaningless report. I have looked at the code and it appears to hide the correct columns. The only other thing I can think of is that I am using the 'Scale to fit one page wide' to make the report fit ... the printout from the macro looks like it could be at 100% scaling. Help! what (if anything) am I doing wrong? -- Rob Harrison |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
print range is gray and i cannot change | Excel Worksheet Functions | |||
print out specified range in macro | Excel Discussion (Misc queries) | |||
Print range macro | Excel Discussion (Misc queries) | |||
Print Range Macro | Excel Programming | |||
Macro print range | Excel Programming |