Copy Contract Number to CenterFooter on multiple sheets

[Guest]

In part, I have the following that takes a Contract number generated in
workbook called QCNUM and puts it in "active" Workbook, Sheet# 1 called
"Contracts", Cell E4

' The following is "paste into"
myBook.Worksheets("Contract").Range("E4").Value = _
' The following is "copy from"
myQCNUM.Worksheets("Sheet2").Range("G6").Value

(I don't know why the above has to be entered as shown - it seems backwards
to me, BUT, it Works.)

Somehow, I need to have the Contract number inserted in the CentreFooter of
sheets: "Options", "Pricing", "Notes", Warranty_CDN", Warranty_USA"
Is it neccessary to duplicate the above 2 lines (changing only sheet name),
for each of the required sheets? or is there a handy-dandy shorter process
that can be used.

Thanks in advance for any input..............

 

[Jim Rech]

First off, your code is not a copy/paste even if it has some of the effects
of such. Think of it as what it is - programmatically setting the value of
one cell equal to another.

Your code also has nothing to do with setting a page footer, so that's a
little confusing. You would have to set them individually for each sheet
but that's easy in a loop.

Sub a()
Dim WS As Worksheet
For Each WS In Worksheets(Array("Sheet1", "Sheet2", "Sheet3"))
WS.PageSetup.CenterFooter = "abc"
Next
End Sub

--
Jim
| In part, I have the following that takes a Contract number generated in
| workbook called QCNUM and puts it in "active" Workbook, Sheet# 1 called
| "Contracts", Cell E4
|
| ' The following is "paste into"
| myBook.Worksheets("Contract").Range("E4").Value = _
| ' The following is "copy from"
| myQCNUM.Worksheets("Sheet2").Range("G6").Value
|
| (I don't know why the above has to be entered as shown - it seems
backwards
| to me, BUT, it Works.)
|
| Somehow, I need to have the Contract number inserted in the CentreFooter
of
| sheets: "Options", "Pricing", "Notes", Warranty_CDN", Warranty_USA"
| Is it neccessary to duplicate the above 2 lines (changing only sheet
name),
| for each of the required sheets? or is there a handy-dandy shorter process
| that can be used.
|
| Thanks in advance for any input..............
|
|

 

[Tom Ogilvy]

that line doesn't put it in the centerheader anyway, Unless your header
references that cell (by using code). If it does, then just group your
sheets and select E4 and do
Range("E4").Value = myQCNUM.Worksheets("Sheet2").Range("G6").Value
then run you code on each sheet.

in any event, for the most part,
you have to do the pagesetup on each page individually.

 

[Guest]

Jim:
Thanks for your comments........
have studied and tried you suggestion.
I'm stuck on how to get cell E4 from page called "contract",
to be the one and only cell that is used to set the centerfooter
on each WS in the array. I have tried a variety of approaches but
I hope I'm close with the following. However, my effort which
comes up with a compile error.
Your help would be greatly appreciated.

 

[Guest]

It would have been helpful to send the following along, as I had originally
intended.
Sorry.


Sub CentreFooter()
'
' CentreFooter Macro
' Take Quote number from Contract Cover page (Sheet 1), Cell E4,
' and enter in Center Footer on all other sheets in contract.
'

Dim WS As Worksheet

For Each WS In Worksheets(Array("Options", "Pricing", "Notes", _
"Warranty_CDN", "Warranty_USA"))

WS.PageSetup.CenterFooter = Worksheets("Contract").Range("E4").Text
Next WS
End Sub

 

[Guest]

Tried it again, today.
This time, while in edit mode, (F8), it comes up with Run Time Error 9,
Subscript out of Range.
However, when I tried the macro on an actual file for which it was designed,
it (seemed to) work fine. The CenterFooter was inserted on each of the WS's
specified in the array, and not on the other WS's.
I'd feel a lot better if the problems/errors showing during "test" stage
could be made to disappear, since I shortly have to send the new menu to my
salesforce around the world.
Looking forward to your thoughts.............