Help - simple but doesn't work.

[mr tom]

Hi,

I'm a very occasional (and not very good) VBA user in a company where nobody
else seems to. I can usually muddle my way through using google and a big
stack of books, but have come unstuck on something which really should be
very simple.

My intention was to produce some code which cycles through all worksheets in
the active workbook (they're all iterations of the same thing) and gets it
all formatted for printing.

I've come up with the following code:

Sub printy_thingy()

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Select

ws.Activate


ws.PageSetup.PrintArea = "$A$1:$T$141"
With ws.PageSetup
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 3
End With
ws.ResetAllPageBreaks
Set ws.HPageBreaks(1).Location = Range("A65")
Set ActiveSheet.HPageBreaks(2).Location = Range("A114")

Next ws

End Sub


Unfortunately, the lines:
Set ws.HPageBreaks(1).Location = Range("A65")
Set ActiveSheet.HPageBreaks(2).Location = Range("A114")
....just don't work. I get error messages when I try to execute them (Excel
2002).

Anybody know why this is happening and how to fix it?

Cheers in advance.

 

[Jim Thomlinson]

For someone who is just muddling you are doing very well... Set only gets
used when you are trying to initialize an object (as opposed to a variable).
So that being said the 2 lines of code in question should not have set at the
beginning. I did not try your code but I think that should work.

 

[Jim Cone]

Contrary to what the help file says, my memory says that the "location" property
reads but doesn't write. What seems to work best for me is...
Rows(65).PageBreak = xlPageBreakManual
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"mr tom" <mr-tom at mr-tom.co.uk.(donotspam)>
wrote in message
Hi,
I'm a very occasional (and not very good) VBA user in a company where nobody
else seems to. I can usually muddle my way through using google and a big
stack of books, but have come unstuck on something which really should be
very simple.
My intention was to produce some code which cycles through all worksheets in
the active workbook (they're all iterations of the same thing) and gets it
all formatted for printing.
I've come up with the following code:

Sub printy_thingy()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Select
ws.Activate
ws.PageSetup.PrintArea = "$A$1:$T$141"
With ws.PageSetup
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 3
End With
ws.ResetAllPageBreaks
Set ws.HPageBreaks(1).Location = Range("A65")
Set ActiveSheet.HPageBreaks(2).Location = Range("A114")
Next ws
End Sub

Unfortunately, the lines:
Set ws.HPageBreaks(1).Location = Range("A65")
Set ActiveSheet.HPageBreaks(2).Location = Range("A114")
....just don't work. I get error messages when I try to execute them (Excel 2002).
Anybody know why this is happening and how to fix it?
Cheers in advance.

 

[mwam423]

dear mr. thomlinson (i've used your code in the past) and mr cone, is there
any resource that explains the difference between an object and a variable,
as well as difference between properties and methods in a quick and concise
way. i know some of the bugs i run into deal with these differences.
thanks, and have a great weekend!

 

[Jim Cone]

I'll try some quick definitions for you.

An OBJECT is a tangible thing, a building block or component of Excel.
Such as Workbook, Worksheet, Range and many more.
In the visual basic editor, type Object Model in the help box, you will find a listing of
the Excel objects (and others).

A PROPERTY is a characteristic of an object.
A METHOD is an action that can be taken.

You could consider a kitchen Stove as an object (one of the components of a kitchen).
It would have properties such as color, burners, drawers.
A method might be the On method for a burner... Stove.Burner.On = True
It can get a little confusing when you consider that the stove burner is an object in its own right. (that is the burner property
returns an object)

A VARIABLE (according to John Walkenbach) "is simply a named storage location
in your computers memory" (not much help?)...
You could consider a variable as a custom name that you use to refer to something.

I strongly suggest you buy a reference book.
I like John Walkenbach's Power Programming books.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"mwam423"
<[email protected]>
wrote in message dear mr. thomlinson (i've used your code in the past) and mr cone, is there
any resource that explains the difference between an object and a variable,
as well as difference between properties and methods in a quick and concise
way. i know some of the bugs i run into deal with these differences.
thanks, and have a great weekend!

 

[mwam423]

hi jim, appreciate your taking the time to reply. i've got walkenbach's
"excel VBA programming", will have to take another look at those sections,
thanks again!