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
[email protected]

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
> [email protected]

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
> > [email protected]

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?

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?
>

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
> > > [email protected]