I'm a very very basic macro user, i usually record and then try to manipulate
the macro. Question is how do you make a global change to all work sheets
that are being created by a macro.

I have 117 worksheets being created by a macro and need all the sheets to be
formatted a certain way. When the data is copied in i have each sheet
changing the refrence # in cell D6. This creates different data in each
sheet. when i recorded the format of the page. it came up with the following.
I have to copy it 117 times to get it to work. Can this formatting be looped?
Each sheet has a different name.


Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Investor #1"
ActiveSheet.PageSetup.PrintArea = "$B$2:$Z$57"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With

This is repeated for each sheet to change from sheet # to Investor name. I
keep getting the message, function too long, so i have to break it up into
three macro's and then they and call the 3 macro's but it still stops after
the 1st one. I have to maulaly start the 2nd and 3rd one even though i have a
buttion assigned to the call all three macros.

First, delete all those lines in the print setup that you don't need to touch.
You'll make the code run faster (accessing the printer is very slow!).

Second you could loop through all the worksheets with something like:

dim wCtr as long
dim wks as worksheet
for wctr = 1 to worksheets.count
set wks = worksheets(wctr)
with wks
on error resume next
.name = "Investor #" & format(wctr, "000")
if err.number < 0 then
err.clear
msgbox .name & " wasn't named correctly!"
end if
on error goto 0

.PageSetup.PrintArea = "$B$2:$Z$57"
With .PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
end with
next wctr

I used "investor #001" in the rename. It'll make sheets easier to sort -- if
you ever have to.

(untested, uncompiled--watch for typos.)



Goaliemenace wrote:

I'm a very very basic macro user, i usually record and then try to manipulate
the macro. Question is how do you make a global change to all work sheets
that are being created by a macro.

I have 117 worksheets being created by a macro and need all the sheets to be
formatted a certain way. When the data is copied in i have each sheet
changing the refrence # in cell D6. This creates different data in each
sheet. when i recorded the format of the page. it came up with the following.
I have to copy it 117 times to get it to work. Can this formatting be looped?
Each sheet has a different name.


Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Investor #1"
ActiveSheet.PageSetup.PrintArea = "$B$2:$Z$57"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With

This is repeated for each sheet to change from sheet # to Investor name. I
keep getting the message, function too long, so i have to break it up into
three macro's and then they and call the 3 macro's but it still stops after
the 1st one. I have to maulaly start the 2nd and 3rd one even though i have a
buttion assigned to the call all three macros.

--

Dave Peterson

Excellent - A Huge Help, Managed to tweak it some more with this.

Appreciate your help

Cheers

"Dave Peterson" wrote:

First, delete all those lines in the print setup that you don't need to touch.
You'll make the code run faster (accessing the printer is very slow!).

Second you could loop through all the worksheets with something like:

dim wCtr as long
dim wks as worksheet
for wctr = 1 to worksheets.count
set wks = worksheets(wctr)
with wks
on error resume next
.name = "Investor #" & format(wctr, "000")
if err.number < 0 then
err.clear
msgbox .name & " wasn't named correctly!"
end if
on error goto 0

.PageSetup.PrintArea = "$B$2:$Z$57"
With .PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
end with
next wctr

I used "investor #001" in the rename. It'll make sheets easier to sort -- if
you ever have to.

(untested, uncompiled--watch for typos.)



Goaliemenace wrote:

I'm a very very basic macro user, i usually record and then try to manipulate
the macro. Question is how do you make a global change to all work sheets
that are being created by a macro.

I have 117 worksheets being created by a macro and need all the sheets to be
formatted a certain way. When the data is copied in i have each sheet
changing the refrence # in cell D6. This creates different data in each
sheet. when i recorded the format of the page. it came up with the following.
I have to copy it 117 times to get it to work. Can this formatting be looped?
Each sheet has a different name.


Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Investor #1"
ActiveSheet.PageSetup.PrintArea = "$B$2:$Z$57"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With

This is repeated for each sheet to change from sheet # to Investor name. I
keep getting the message, function too long, so i have to break it up into
three macro's and then they and call the 3 macro's but it still stops after
the 1st one. I have to maulaly start the 2nd and 3rd one even though i have a
buttion assigned to the call all three macros.

--

Dave Peterson
.