Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

collection of sheets 1

Status
Not open for further replies.

rgandy

Technical User
Nov 17, 2005
38
US
hi all,
i would like to take a collection of sheets
ie
sheets(array("sheet1","sheet2")).select

and change all of their page setup properties simultaneously

i am having difficulty figuring out how to do this.
each time i try, it seems to only change the attributes of the currently selected sheet rather than the entire array

my exact current code is

Sheets(Array("Portfolio", "Portfolio Summary", "Trade 1", "Trade 2", "Trade 3", "Query 1", "Query 2", "Query 3", "Query 4", "Query 5", "Query 6", "Query 7", "Vol - Summary", "Vol - Individual")).Select
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.75)
.CenterFooter = "Page &P"
.RightFooter = "&Z&F"
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 100
End With

do you see where i am going wrong? thanks
 


Hi,

Code:
With Sheets(Array("Portfolio", "Portfolio Summary", "Trade 1", "Trade 2", "Trade 3", "Query 1", "Query 2", "Query 3", "Query 4", "Query 5", "Query 6", "Query 7", "Vol - Summary", "Vol - Individual)).PageSetup
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.25)
        .BottomMargin = Application.InchesToPoints(0.75)
        .CenterFooter = "Page &P"
        .RightFooter = "&Z&F"
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Orientation = xlLandscape
        .PaperSize = xlPaperLegal
        .FirstPageNumber = xlAutomatic
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 100
    End With

Skip,

[glasses] [red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![tongue]
 
skip,
thanks for the reply
this code is returning an error message
"object doesnt support this property or method"
any other suggestions?
thanks
 

Well I went back to your original code and found the it does, indeed, change the properties of all the sheets in the array.

Skip,

[glasses] [red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![tongue]
 
skip,
that is odd...it is not achieving this on my computer
are you saying that it is my original code, or the code you provided, that should work?
is there a particular reason this may not be working for me?? thanks
 

Try this
Code:
dim ws as worksheet
For each ws in Sheets(Array("Portfolio", "Portfolio Summary", "Trade 1", "Trade 2", "Trade 3", "Query 1", "Query 2", "Query 3", "Query 4", "Query 5", "Query 6", "Query 7", "Vol - Summary", "Vol - Individual"))
    With ws.PageSetup
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.25)
        .BottomMargin = Application.InchesToPoints(0.75)
        .CenterFooter = "Page &P"
        .RightFooter = "&Z&F"
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Orientation = xlLandscape
        .PaperSize = xlPaperLegal
        .FirstPageNumber = xlAutomatic
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 100
    End With
next


Skip,

[glasses] [red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![tongue]
 
skip,
i appreciate your efforts..the thing is that the looping would not be a problem...my goal here is to eliminate the time it is taknig to set these attributes across all of these sheets...it is fairly time consuming to do so

on your computer, does the initial code i had sent seem to apply those characteristics to all of the selected sheets?
 
rgandy, I was looking at your original code where you select the sheets then apply the changes.

I think it doesn't work because you have selected the sheets and then you apply changes to the active sheet, a selected sheet is not always an active sheet.

I haven't tried this but maybe you could activate the array of sheets first then apply changes.

Alternatively:

The error "object doesnt support this property or method" that Skip's code generates may only arise from one property change. Try to use Skip's code with only one change at a time. If it is only one property then you only need to loop for that property.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top