collection of sheets 1

[rgandy]

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

 

[SkipVought]

Hi,

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,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[rgandy]

skip,
thanks for the reply
this code is returning an error message
"object doesnt support this property or method"
any other suggestions?
thanks

 

[SkipVought]

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,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[rgandy]

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

 

[SkipVought]

Try this

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,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[rgandy]

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?

 

[SkipVought]

No. My test was not set up correctly.


BTW, this solution is suggested on

http://support.microsoft.com/default.aspx?scid=kb;en-us;124934

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[rgandy]

thanks very much

 

[bpeirson]

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.