Using routines to change a number of sheets
 

bestie,
Change line
Sname.Select
to
Worksheets(Sname$).Select
James
bestie wrote:
Hi,

Im new to the whole Macro VB thing and have been having problems with the
following piece of code. What I am trying to do is make changes to the page
set up of a dozen sheets in a workbook. The sheets I want to change the set
up of are listed of a workbook sheet called Tree and are in cells G9 to G30.

I tried recording a macro selecting these sheets however when I ran it only
the first sheet changed, can someone please help me or advise me of a better
way of doing this..

Thanks

Using routines to change a number of sheets
 

Zone,

Thanks For that was a silly oversight on my part, however it still doesn't
seem to work I think it may be something to do with the fact I am trying to
change the page set up..... But it seems to snag on the Worksheet(Sname$).
Select line

If you could offer any further help it would be much appreciated.

Thanks


Sub grid()
'
' grid Macro
' Macro recorded 27/07/2006 by Windows User
'

'

Dim i As Long
Dim nRow As Long
Dim Sname$
' selects each sheet name down the list & activates that sheet
i = 9
With Sheets("TREE")
Do While Not IsEmpty(.Cells(i, "G").Value)
Sname$ = Cells(i, "G")
' passes the sheet name to a routine that executes more code on
the named sheet

Worksheet(Sname$).Select

With ActiveSheet.PageSetup
.LeftHeader = "&""Arial,Bold ""&12 JUNE" & Chr(10) & ""
.CenterHeader = _
"&""Arial,Regular""&10 Income && Expenditure FY End 2007- Trust Total
"
.RightHeader = "&""Arial,Bold ""&12 YTD To Period 004"
.LeftFooter = "&""Arial,Regular""&10 RSC - Printed 08:40 /
27/07/2006"
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.15748031496063)
.RightMargin = Application.InchesToPoints(0.15748031496063)
.TopMargin = Application.InchesToPoints(0.590551181102362)
.BottomMargin = Application.InchesToPoints(0.590551181102362)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintInPlace
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed

i = i + 1
Loop
End With



End Sub




Zone wrote:
bestie,
Change line
Sname.Select
to
Worksheets(Sname$).Select
James
Hi,

[quoted text clipped - 8 lines]

Thanks


Zone wrote:
bestie,
Change line
Sname.Select
to
Worksheets(Sname$).Select
James
Hi,

[quoted text clipped - 8 lines]

Thanks

Using routines to change a number of sheets
 

You left an s off of Worksheets.
Worksheets(Sname$).Select
Try that.
James

Zone,

Thanks For that was a silly oversight on my part, however it still doesn't
seem to work I think it may be something to do with the fact I am trying to
change the page set up..... But it seems to snag on the Worksheet(Sname$).
Select line

If you could offer any further help it would be much appreciated.

Thanks


Sub grid()
'
' grid Macro
' Macro recorded 27/07/2006 by Windows User
'

'

Dim i As Long
Dim nRow As Long
Dim Sname$
' selects each sheet name down the list & activates that sheet
i = 9
With Sheets("TREE")
Do While Not IsEmpty(.Cells(i, "G").Value)
Sname$ = Cells(i, "G")
' passes the sheet name to a routine that executes more code on
the named sheet

Worksheet(Sname$).Select

With ActiveSheet.PageSetup
.LeftHeader = "&""Arial,Bold ""&12 JUNE" & Chr(10) & ""
.CenterHeader = _
"&""Arial,Regular""&10 Income && Expenditure FY End 2007- Trust Total
"
.RightHeader = "&""Arial,Bold ""&12 YTD To Period 004"
.LeftFooter = "&""Arial,Regular""&10 RSC - Printed 08:40 /
27/07/2006"
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.15748031496063)
.RightMargin = Application.InchesToPoints(0.15748031496063)
.TopMargin = Application.InchesToPoints(0.590551181102362)
.BottomMargin = Application.InchesToPoints(0.590551181102362)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintInPlace
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed

i = i + 1
Loop
End With



End Sub




Zone wrote:
bestie,
Change line
Sname.Select
to
Worksheets(Sname$).Select
James
Hi,

[quoted text clipped - 8 lines]

Thanks


Zone wrote:
bestie,
Change line
Sname.Select
to
Worksheets(Sname$).Select
James
Hi,

[quoted text clipped - 8 lines]

Thanks

Using routines to change a number of sheets
 

Sorry James... I'm such a pain but it almost seems to be working.... It is
looking for an End With somewhere but reguardless of where I put it it seems
to be the wrong place can you ammend the code below to include the closing
when statement.

Thanks for all you help.

Sub grid()
'
' grid Macro
' Macro recorded 27/07/2006 by Windows User
'

'

Dim i As Long
Dim nRow As Long
Dim Sname$
' selects each sheet name down the list & activates that sheet
i = 9
With Sheets("TREE")
Do While Not IsEmpty(.Cells(i, "G").Value)
Sname$ = Cells(i, "G")
' passes the sheet name to a routine that executes more code on
the named sheet

Worksheets(Sname$).Select


With ActiveSheet.PageSetup
.LeftHeader = "&""Arial,Bold ""&12 JUNE" & Chr(10) & ""
.CenterHeader = _
"&""Arial,Regular""&10 Income && Expenditure FY End 2007- Trust Total
"
.RightHeader = "&""Arial,Bold ""&12 YTD To Period 004"
.LeftFooter = "&""Arial,Regular""&10 RSC - Printed 08:40 /
27/07/2006"
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.15748031496063)
.RightMargin = Application.InchesToPoints(0.15748031496063)
.TopMargin = Application.InchesToPoints(0.590551181102362)
.BottomMargin = Application.InchesToPoints(0.590551181102362)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintInPlace
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed

i = i + 1

End With

Loop




End Sub

Zone wrote:
You left an s off of Worksheets.
Worksheets(Sname$).Select
Try that.
James

Zone,

[quoted text clipped - 90 lines]

Thanks

Using routines to change a number of sheets
 

You are missing an 'End With' statement. You need an 'End With'
following your 'Loop' statement. If you properly indent your
code, the missing 'End With' is obvious.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"bestie" <u24576@uwe wrote in message news:63dfb30b9b2bb@uwe...
Sorry James... I'm such a pain but it almost seems to be
working.... It is
looking for an End With somewhere but reguardless of where I
put it it seems
to be the wrong place can you ammend the code below to include
the closing
when statement.

Thanks for all you help.

Sub grid()
'
' grid Macro
' Macro recorded 27/07/2006 by Windows User
'

'

Dim i As Long
Dim nRow As Long
Dim Sname$
' selects each sheet name down the list & activates that sheet
i = 9
With Sheets("TREE")
Do While Not IsEmpty(.Cells(i, "G").Value)
Sname$ = Cells(i, "G")
' passes the sheet name to a routine that executes
more code on
the named sheet

Worksheets(Sname$).Select


With ActiveSheet.PageSetup
.LeftHeader = "&""Arial,Bold ""&12 JUNE" & Chr(10) & ""
.CenterHeader = _
"&""Arial,Regular""&10 Income && Expenditure FY End
2007- Trust Total
"
.RightHeader = "&""Arial,Bold ""&12 YTD To Period 004"
.LeftFooter = "&""Arial,Regular""&10 RSC - Printed 08:40
/
27/07/2006"
.CenterFooter = ""
.RightFooter = ""
.LeftMargin =
Application.InchesToPoints(0.15748031496063)
.RightMargin =
Application.InchesToPoints(0.15748031496063)
.TopMargin =
Application.InchesToPoints(0.590551181102362)
.BottomMargin =
Application.InchesToPoints(0.590551181102362)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintInPlace
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed

i = i + 1

End With

Loop




End Sub

Zone wrote:
You left an s off of Worksheets.
Worksheets(Sname$).Select
Try that.
James

Zone,

[quoted text clipped - 90 lines]

Thanks

Using routines to change a number of sheets
 

bestie,
You have two With statements, one inside the other. I do not use
nested Withs. Although I understand this can be done (I think), it's
just too confusing. I'd get rid of the first With and just use the
TEST sheet's name to get the info, as I've changed your code. Notice
the changes in the lines after the i=9 line. I haven't tested it. Let
me know if this works.
James

bestie wrote:
Sorry James... I'm such a pain but it almost seems to be working.... It is
looking for an End With somewhere but reguardless of where I put it it seems
to be the wrong place can you ammend the code below to include the closing
when statement.

Thanks for all you help.

Sub grid()
'
' grid Macro
' Macro recorded 27/07/2006 by Windows User
'

'

Dim i As Long
Dim nRow As Long
Dim Sname$
' selects each sheet name down the list & activates that sheet
i = 9
Do While Not IsEmpty(Worksheets("TREE").Cells(i, "G").Value)
Sname$ = Worksheets("TREE").Cells(i, "G")
' passes the sheet name to a routine that executes more code on
the named sheet

Worksheets(Sname$).Select


With ActiveSheet.PageSetup
.LeftHeader = "&""Arial,Bold ""&12 JUNE" & Chr(10) & ""
.CenterHeader = _
"&""Arial,Regular""&10 Income && Expenditure FY End 2007- Trust Total
"
.RightHeader = "&""Arial,Bold ""&12 YTD To Period 004"
.LeftFooter = "&""Arial,Regular""&10 RSC - Printed 08:40 /
27/07/2006"
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.15748031496063)
.RightMargin = Application.InchesToPoints(0.15748031496063)
.TopMargin = Application.InchesToPoints(0.590551181102362)
.BottomMargin = Application.InchesToPoints(0.590551181102362)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintInPlace
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed

i = i + 1

End With

Loop



End Sub

Using routines to change a number of sheets
 

James,

Finally got it to work, thanks for all your help.

Sam

Zone wrote:
bestie,
You have two With statements, one inside the other. I do not use
nested Withs. Although I understand this can be done (I think), it's
just too confusing. I'd get rid of the first With and just use the
TEST sheet's name to get the info, as I've changed your code. Notice
the changes in the lines after the i=9 line. I haven't tested it. Let
me know if this works.
James

Sorry James... I'm such a pain but it almost seems to be working.... It is
looking for an End With somewhere but reguardless of where I put it it seems
[quoted text clipped - 65 lines]

End Sub

Using routines to change a number of sheets
 

Sam,
Glad to help. I should have noticed that other with statement earlier.
James
bestie wrote:
James,

Finally got it to work, thanks for all your help.

Sam

Zone wrote:
bestie,
You have two With statements, one inside the other. I do not use
nested Withs. Although I understand this can be done (I think), it's
just too confusing. I'd get rid of the first With and just use the
TEST sheet's name to get the info, as I've changed your code. Notice
the changes in the lines after the i=9 line. I haven't tested it. Let
me know if this works.
James

Sorry James... I'm such a pain but it almost seems to be working.... It is
looking for an End With somewhere but reguardless of where I put it it seems
[quoted text clipped - 65 lines]

End Sub