Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking cells to Headers and Footers
I would like to have a worksheet (Master) which has the information
that I would like to have put into various headers in different worksheets. I'd like to link the header elements of the different worksheets to this Master so that when I want to change the name of a company, exhibit number, etc., it automatically changes the header. Is this possible? Thanks in advance, Gregory |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking cells to Headers and Footers
Gregory wrote:
I would like to have a worksheet (Master) which has the information that I would like to have put into various headers in different worksheets. I'd like to link the header elements of the different worksheets to this Master so that when I want to change the name of a company, exhibit number, etc., it automatically changes the header. Is this possible? Thanks in advance, Gregory You can make headers & c. dynamic using VBA. This code will create a header with whatever is in cell A1 of each worksheet: Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False With ActiveSheet.PageSetup For Each WKS In ActiveWorkbook.Worksheets .LeftHeader = Range("A1").Text Next WKS End With Application.ScreenUpdating = True End Sub To extend this, record a macro as you manually create an arbitrary header. The code it creates will reveal a plethora of other page setup options you can tweak in code. To automate the process of updating the headers, place something in the workbook before_print event that calls MakeHeaders. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking cells to Headers and Footers
Actually, this places the text of what's in A1 of the activesheet into the
activesheet's print header. But it does it for each worksheet in that workbook. Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False For Each WKS In ActiveWorkbook.Worksheets 'use the text in A1 of each sheet wks.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.leftheader = worksheets("Master").range("A1").text Next WKS Application.ScreenUpdating = True End Sub smartin wrote: Gregory wrote: I would like to have a worksheet (Master) which has the information that I would like to have put into various headers in different worksheets. I'd like to link the header elements of the different worksheets to this Master so that when I want to change the name of a company, exhibit number, etc., it automatically changes the header. Is this possible? Thanks in advance, Gregory You can make headers & c. dynamic using VBA. This code will create a header with whatever is in cell A1 of each worksheet: Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False With ActiveSheet.PageSetup For Each WKS In ActiveWorkbook.Worksheets .LeftHeader = Range("A1").Text Next WKS End With Application.ScreenUpdating = True End Sub To extend this, record a macro as you manually create an arbitrary header. The code it creates will reveal a plethora of other page setup options you can tweak in code. To automate the process of updating the headers, place something in the workbook before_print event that calls MakeHeaders. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking cells to Headers and Footers
Eh? Thought that's what I said. This would have been clearer:
"...create a header /for each worksheet/ with whatever is in cell A1 /of that worksheet/" I thought that's what the OP wanted, but I might have misunderstood. Regards, Smartin. Dave Peterson wrote: Actually, this places the text of what's in A1 of the activesheet into the activesheet's print header. But it does it for each worksheet in that workbook. Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False For Each WKS In ActiveWorkbook.Worksheets 'use the text in A1 of each sheet wks.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.leftheader = worksheets("Master").range("A1").text Next WKS Application.ScreenUpdating = True End Sub smartin wrote: Gregory wrote: I would like to have a worksheet (Master) which has the information that I would like to have put into various headers in different worksheets. I'd like to link the header elements of the different worksheets to this Master so that when I want to change the name of a company, exhibit number, etc., it automatically changes the header. Is this possible? Thanks in advance, Gregory You can make headers & c. dynamic using VBA. This code will create a header with whatever is in cell A1 of each worksheet: Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False With ActiveSheet.PageSetup For Each WKS In ActiveWorkbook.Worksheets .LeftHeader = Range("A1").Text Next WKS End With Application.ScreenUpdating = True End Sub To extend this, record a macro as you manually create an arbitrary header. The code it creates will reveal a plethora of other page setup options you can tweak in code. To automate the process of updating the headers, place something in the workbook before_print event that calls MakeHeaders. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking cells to Headers and Footers
But that's not what your code did.
Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False With ActiveSheet.PageSetup For Each WKS In ActiveWorkbook.Worksheets .LeftHeader = Range("A1").Text Next WKS End With Application.ScreenUpdating = True End Sub The .leftheader refers to the activesheet.pagesetup. And the unqualified Range("A1") refers to the activesheet. Neither of those things change. So you're plopping the text in A1 of the activesheet into the activesheet's header. And you're doing it as many times as there are worksheets in the activeworkbook. That's why I suggested dropping the "with/end with" stuff and qualifying the ranges. But my code did have a typo in it: wks.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.leftheader = worksheets("Master").range("A1").text Should have been: wks.pagesetup.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.pagesetup.leftheader = worksheets("Master").range("A1").text The second line (with the "master" reference) was because I read the original post as wanting to take something from that sheet and put it on each worksheet's header. smartin wrote: Eh? Thought that's what I said. This would have been clearer: "...create a header /for each worksheet/ with whatever is in cell A1 /of that worksheet/" I thought that's what the OP wanted, but I might have misunderstood. Regards, Smartin. Dave Peterson wrote: Actually, this places the text of what's in A1 of the activesheet into the activesheet's print header. But it does it for each worksheet in that workbook. Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False For Each WKS In ActiveWorkbook.Worksheets 'use the text in A1 of each sheet wks.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.leftheader = worksheets("Master").range("A1").text Next WKS Application.ScreenUpdating = True End Sub smartin wrote: Gregory wrote: I would like to have a worksheet (Master) which has the information that I would like to have put into various headers in different worksheets. I'd like to link the header elements of the different worksheets to this Master so that when I want to change the name of a company, exhibit number, etc., it automatically changes the header. Is this possible? Thanks in advance, Gregory You can make headers & c. dynamic using VBA. This code will create a header with whatever is in cell A1 of each worksheet: Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False With ActiveSheet.PageSetup For Each WKS In ActiveWorkbook.Worksheets .LeftHeader = Range("A1").Text Next WKS End With Application.ScreenUpdating = True End Sub To extend this, record a macro as you manually create an arbitrary header. The code it creates will reveal a plethora of other page setup options you can tweak in code. To automate the process of updating the headers, place something in the workbook before_print event that calls MakeHeaders. -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking cells to Headers and Footers
Dave, either I disagree, or misunderstand.
Using Excel 2003 create 2+ worksheets, each with a different value in A1, and run my code in a module. Print [Preview]. QED. (Works for me.) Having said that, I confess I am now a little fuzzy /why/ this works, and in retrospect I think indeed it should not work for the reasons you cite. And yet, it works. I will admit though, my style may be sloppy. Apparently I have engaged some kind of default property/event that is not intuitive (or should not be intuitive, even though my sloppy brain conceived it). To wit, it seems I assumed (or discovered) With Each WKS ... somehow makes WKS the ActiveSheet in each pass? What do you think? Dave Peterson wrote: But that's not what your code did. Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False With ActiveSheet.PageSetup For Each WKS In ActiveWorkbook.Worksheets .LeftHeader = Range("A1").Text Next WKS End With Application.ScreenUpdating = True End Sub The .leftheader refers to the activesheet.pagesetup. And the unqualified Range("A1") refers to the activesheet. Neither of those things change. So you're plopping the text in A1 of the activesheet into the activesheet's header. And you're doing it as many times as there are worksheets in the activeworkbook. That's why I suggested dropping the "with/end with" stuff and qualifying the ranges. But my code did have a typo in it: wks.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.leftheader = worksheets("Master").range("A1").text Should have been: wks.pagesetup.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.pagesetup.leftheader = worksheets("Master").range("A1").text The second line (with the "master" reference) was because I read the original post as wanting to take something from that sheet and put it on each worksheet's header. smartin wrote: Eh? Thought that's what I said. This would have been clearer: "...create a header /for each worksheet/ with whatever is in cell A1 /of that worksheet/" I thought that's what the OP wanted, but I might have misunderstood. Regards, Smartin. Dave Peterson wrote: Actually, this places the text of what's in A1 of the activesheet into the activesheet's print header. But it does it for each worksheet in that workbook. Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False For Each WKS In ActiveWorkbook.Worksheets 'use the text in A1 of each sheet wks.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.leftheader = worksheets("Master").range("A1").text Next WKS Application.ScreenUpdating = True End Sub smartin wrote: Gregory wrote: I would like to have a worksheet (Master) which has the information that I would like to have put into various headers in different worksheets. I'd like to link the header elements of the different worksheets to this Master so that when I want to change the name of a company, exhibit number, etc., it automatically changes the header. Is this possible? Thanks in advance, Gregory You can make headers & c. dynamic using VBA. This code will create a header with whatever is in cell A1 of each worksheet: Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False With ActiveSheet.PageSetup For Each WKS In ActiveWorkbook.Worksheets .LeftHeader = Range("A1").Text Next WKS End With Application.ScreenUpdating = True End Sub To extend this, record a macro as you manually create an arbitrary header. The code it creates will reveal a plethora of other page setup options you can tweak in code. To automate the process of updating the headers, place something in the workbook before_print event that calls MakeHeaders. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking cells to Headers and Footers
Sorry to follow up my own post so quickly.
Clearly, ActiveSheet is not changed in my loop. Yet, with my code the header for each WKS is tied to each WKS, not ActiveSheet. I am confused. Is this a bug? smartin wrote: Dave, either I disagree, or misunderstand. Using Excel 2003 create 2+ worksheets, each with a different value in A1, and run my code in a module. Print [Preview]. QED. (Works for me.) Having said that, I confess I am now a little fuzzy /why/ this works, and in retrospect I think indeed it should not work for the reasons you cite. And yet, it works. I will admit though, my style may be sloppy. Apparently I have engaged some kind of default property/event that is not intuitive (or should not be intuitive, even though my sloppy brain conceived it). To wit, it seems I assumed (or discovered) With Each WKS ... somehow makes WKS the ActiveSheet in each pass? What do you think? Dave Peterson wrote: But that's not what your code did. Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False With ActiveSheet.PageSetup For Each WKS In ActiveWorkbook.Worksheets .LeftHeader = Range("A1").Text Next WKS End With Application.ScreenUpdating = True End Sub The .leftheader refers to the activesheet.pagesetup. And the unqualified Range("A1") refers to the activesheet. Neither of those things change. So you're plopping the text in A1 of the activesheet into the activesheet's header. And you're doing it as many times as there are worksheets in the activeworkbook. That's why I suggested dropping the "with/end with" stuff and qualifying the ranges. But my code did have a typo in it: wks.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.leftheader = worksheets("Master").range("A1").text Should have been: wks.pagesetup.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.pagesetup.leftheader = worksheets("Master").range("A1").text The second line (with the "master" reference) was because I read the original post as wanting to take something from that sheet and put it on each worksheet's header. smartin wrote: Eh? Thought that's what I said. This would have been clearer: "...create a header /for each worksheet/ with whatever is in cell A1 /of that worksheet/" I thought that's what the OP wanted, but I might have misunderstood. Regards, Smartin. Dave Peterson wrote: Actually, this places the text of what's in A1 of the activesheet into the activesheet's print header. But it does it for each worksheet in that workbook. Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False For Each WKS In ActiveWorkbook.Worksheets 'use the text in A1 of each sheet wks.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.leftheader = worksheets("Master").range("A1").text Next WKS Application.ScreenUpdating = True End Sub smartin wrote: Gregory wrote: I would like to have a worksheet (Master) which has the information that I would like to have put into various headers in different worksheets. I'd like to link the header elements of the different worksheets to this Master so that when I want to change the name of a company, exhibit number, etc., it automatically changes the header. Is this possible? Thanks in advance, Gregory You can make headers & c. dynamic using VBA. This code will create a header with whatever is in cell A1 of each worksheet: Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False With ActiveSheet.PageSetup For Each WKS In ActiveWorkbook.Worksheets .LeftHeader = Range("A1").Text Next WKS End With Application.ScreenUpdating = True End Sub To extend this, record a macro as you manually create an arbitrary header. The code it creates will reveal a plethora of other page setup options you can tweak in code. To automate the process of updating the headers, place something in the workbook before_print event that calls MakeHeaders. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking cells to Headers and Footers
It didn't work for me.
This is a guess. I bet you created a test workbook with a few worksheets. And put something in A1 of each sheet. Then you ran the code. And when you did print preview, you saw the stuff that was in A1 at the top of each sheet--except for a single page--and that had it twice. (That's what I did.) The sheet where it showed up twice had the header and the data (the print range included A1). The other sheets didn't have any headers and only showed A1 as part of the print range. If you add a fill color to A1 of each sheet, it may make it easier to see. smartin wrote: Sorry to follow up my own post so quickly. Clearly, ActiveSheet is not changed in my loop. Yet, with my code the header for each WKS is tied to each WKS, not ActiveSheet. I am confused. Is this a bug? smartin wrote: Dave, either I disagree, or misunderstand. Using Excel 2003 create 2+ worksheets, each with a different value in A1, and run my code in a module. Print [Preview]. QED. (Works for me.) Having said that, I confess I am now a little fuzzy /why/ this works, and in retrospect I think indeed it should not work for the reasons you cite. And yet, it works. I will admit though, my style may be sloppy. Apparently I have engaged some kind of default property/event that is not intuitive (or should not be intuitive, even though my sloppy brain conceived it). To wit, it seems I assumed (or discovered) With Each WKS ... somehow makes WKS the ActiveSheet in each pass? What do you think? Dave Peterson wrote: But that's not what your code did. Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False With ActiveSheet.PageSetup For Each WKS In ActiveWorkbook.Worksheets .LeftHeader = Range("A1").Text Next WKS End With Application.ScreenUpdating = True End Sub The .leftheader refers to the activesheet.pagesetup. And the unqualified Range("A1") refers to the activesheet. Neither of those things change. So you're plopping the text in A1 of the activesheet into the activesheet's header. And you're doing it as many times as there are worksheets in the activeworkbook. That's why I suggested dropping the "with/end with" stuff and qualifying the ranges. But my code did have a typo in it: wks.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.leftheader = worksheets("Master").range("A1").text Should have been: wks.pagesetup.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.pagesetup.leftheader = worksheets("Master").range("A1").text The second line (with the "master" reference) was because I read the original post as wanting to take something from that sheet and put it on each worksheet's header. smartin wrote: Eh? Thought that's what I said. This would have been clearer: "...create a header /for each worksheet/ with whatever is in cell A1 /of that worksheet/" I thought that's what the OP wanted, but I might have misunderstood. Regards, Smartin. Dave Peterson wrote: Actually, this places the text of what's in A1 of the activesheet into the activesheet's print header. But it does it for each worksheet in that workbook. Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False For Each WKS In ActiveWorkbook.Worksheets 'use the text in A1 of each sheet wks.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.leftheader = worksheets("Master").range("A1").text Next WKS Application.ScreenUpdating = True End Sub smartin wrote: Gregory wrote: I would like to have a worksheet (Master) which has the information that I would like to have put into various headers in different worksheets. I'd like to link the header elements of the different worksheets to this Master so that when I want to change the name of a company, exhibit number, etc., it automatically changes the header. Is this possible? Thanks in advance, Gregory You can make headers & c. dynamic using VBA. This code will create a header with whatever is in cell A1 of each worksheet: Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False With ActiveSheet.PageSetup For Each WKS In ActiveWorkbook.Worksheets .LeftHeader = Range("A1").Text Next WKS End With Application.ScreenUpdating = True End Sub To extend this, record a macro as you manually create an arbitrary header. The code it creates will reveal a plethora of other page setup options you can tweak in code. To automate the process of updating the headers, place something in the workbook before_print event that calls MakeHeaders. -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking cells to Headers and Footers
You are spot-on, Dave. I should have noticed.
Actually it was a little tricker to see the error of my ways than you describe. I started with one worksheet (my default configuration). Run the code, looks fine. Add a second worksheet to "make sure" it works on multiple sheets, run code (you guessed it, with the new sheet active) -- looks fine. Add a third sheet later, "just to be really sure" -- you get the idea. So my testing was bunk. I am now making omelettes with the egg on my face (^: There's plenty to go around -- everyone is invited! Dave Peterson wrote: It didn't work for me. This is a guess. I bet you created a test workbook with a few worksheets. And put something in A1 of each sheet. Then you ran the code. And when you did print preview, you saw the stuff that was in A1 at the top of each sheet--except for a single page--and that had it twice. (That's what I did.) The sheet where it showed up twice had the header and the data (the print range included A1). The other sheets didn't have any headers and only showed A1 as part of the print range. If you add a fill color to A1 of each sheet, it may make it easier to see. smartin wrote: Sorry to follow up my own post so quickly. Clearly, ActiveSheet is not changed in my loop. Yet, with my code the header for each WKS is tied to each WKS, not ActiveSheet. I am confused. Is this a bug? smartin wrote: Dave, either I disagree, or misunderstand. Using Excel 2003 create 2+ worksheets, each with a different value in A1, and run my code in a module. Print [Preview]. QED. (Works for me.) Having said that, I confess I am now a little fuzzy /why/ this works, and in retrospect I think indeed it should not work for the reasons you cite. And yet, it works. I will admit though, my style may be sloppy. Apparently I have engaged some kind of default property/event that is not intuitive (or should not be intuitive, even though my sloppy brain conceived it). To wit, it seems I assumed (or discovered) With Each WKS ... somehow makes WKS the ActiveSheet in each pass? What do you think? Dave Peterson wrote: But that's not what your code did. Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False With ActiveSheet.PageSetup For Each WKS In ActiveWorkbook.Worksheets .LeftHeader = Range("A1").Text Next WKS End With Application.ScreenUpdating = True End Sub The .leftheader refers to the activesheet.pagesetup. And the unqualified Range("A1") refers to the activesheet. Neither of those things change. So you're plopping the text in A1 of the activesheet into the activesheet's header. And you're doing it as many times as there are worksheets in the activeworkbook. That's why I suggested dropping the "with/end with" stuff and qualifying the ranges. But my code did have a typo in it: wks.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.leftheader = worksheets("Master").range("A1").text Should have been: wks.pagesetup.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.pagesetup.leftheader = worksheets("Master").range("A1").text The second line (with the "master" reference) was because I read the original post as wanting to take something from that sheet and put it on each worksheet's header. smartin wrote: Eh? Thought that's what I said. This would have been clearer: "...create a header /for each worksheet/ with whatever is in cell A1 /of that worksheet/" I thought that's what the OP wanted, but I might have misunderstood. Regards, Smartin. Dave Peterson wrote: Actually, this places the text of what's in A1 of the activesheet into the activesheet's print header. But it does it for each worksheet in that workbook. Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False For Each WKS In ActiveWorkbook.Worksheets 'use the text in A1 of each sheet wks.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.leftheader = worksheets("Master").range("A1").text Next WKS Application.ScreenUpdating = True End Sub smartin wrote: Gregory wrote: I would like to have a worksheet (Master) which has the information that I would like to have put into various headers in different worksheets. I'd like to link the header elements of the different worksheets to this Master so that when I want to change the name of a company, exhibit number, etc., it automatically changes the header. Is this possible? Thanks in advance, Gregory You can make headers & c. dynamic using VBA. This code will create a header with whatever is in cell A1 of each worksheet: Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False With ActiveSheet.PageSetup For Each WKS In ActiveWorkbook.Worksheets .LeftHeader = Range("A1").Text Next WKS End With Application.ScreenUpdating = True End Sub To extend this, record a macro as you manually create an arbitrary header. The code it creates will reveal a plethora of other page setup options you can tweak in code. To automate the process of updating the headers, place something in the workbook before_print event that calls MakeHeaders. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking cells to Headers and Footers
But we worked it out in the end--and that's what matters!
smartin wrote: You are spot-on, Dave. I should have noticed. Actually it was a little tricker to see the error of my ways than you describe. I started with one worksheet (my default configuration). Run the code, looks fine. Add a second worksheet to "make sure" it works on multiple sheets, run code (you guessed it, with the new sheet active) -- looks fine. Add a third sheet later, "just to be really sure" -- you get the idea. So my testing was bunk. I am now making omelettes with the egg on my face (^: There's plenty to go around -- everyone is invited! Dave Peterson wrote: It didn't work for me. This is a guess. I bet you created a test workbook with a few worksheets. And put something in A1 of each sheet. Then you ran the code. And when you did print preview, you saw the stuff that was in A1 at the top of each sheet--except for a single page--and that had it twice. (That's what I did.) The sheet where it showed up twice had the header and the data (the print range included A1). The other sheets didn't have any headers and only showed A1 as part of the print range. If you add a fill color to A1 of each sheet, it may make it easier to see. smartin wrote: Sorry to follow up my own post so quickly. Clearly, ActiveSheet is not changed in my loop. Yet, with my code the header for each WKS is tied to each WKS, not ActiveSheet. I am confused. Is this a bug? smartin wrote: Dave, either I disagree, or misunderstand. Using Excel 2003 create 2+ worksheets, each with a different value in A1, and run my code in a module. Print [Preview]. QED. (Works for me.) Having said that, I confess I am now a little fuzzy /why/ this works, and in retrospect I think indeed it should not work for the reasons you cite. And yet, it works. I will admit though, my style may be sloppy. Apparently I have engaged some kind of default property/event that is not intuitive (or should not be intuitive, even though my sloppy brain conceived it). To wit, it seems I assumed (or discovered) With Each WKS ... somehow makes WKS the ActiveSheet in each pass? What do you think? Dave Peterson wrote: But that's not what your code did. Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False With ActiveSheet.PageSetup For Each WKS In ActiveWorkbook.Worksheets .LeftHeader = Range("A1").Text Next WKS End With Application.ScreenUpdating = True End Sub The .leftheader refers to the activesheet.pagesetup. And the unqualified Range("A1") refers to the activesheet. Neither of those things change. So you're plopping the text in A1 of the activesheet into the activesheet's header. And you're doing it as many times as there are worksheets in the activeworkbook. That's why I suggested dropping the "with/end with" stuff and qualifying the ranges. But my code did have a typo in it: wks.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.leftheader = worksheets("Master").range("A1").text Should have been: wks.pagesetup.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.pagesetup.leftheader = worksheets("Master").range("A1").text The second line (with the "master" reference) was because I read the original post as wanting to take something from that sheet and put it on each worksheet's header. smartin wrote: Eh? Thought that's what I said. This would have been clearer: "...create a header /for each worksheet/ with whatever is in cell A1 /of that worksheet/" I thought that's what the OP wanted, but I might have misunderstood. Regards, Smartin. Dave Peterson wrote: Actually, this places the text of what's in A1 of the activesheet into the activesheet's print header. But it does it for each worksheet in that workbook. Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False For Each WKS In ActiveWorkbook.Worksheets 'use the text in A1 of each sheet wks.LeftHeader = wks.Range("A1").Text 'or use the text from A1 of the Master sheet wks.leftheader = worksheets("Master").range("A1").text Next WKS Application.ScreenUpdating = True End Sub smartin wrote: Gregory wrote: I would like to have a worksheet (Master) which has the information that I would like to have put into various headers in different worksheets. I'd like to link the header elements of the different worksheets to this Master so that when I want to change the name of a company, exhibit number, etc., it automatically changes the header. Is this possible? Thanks in advance, Gregory You can make headers & c. dynamic using VBA. This code will create a header with whatever is in cell A1 of each worksheet: Sub MakeHeaders() Dim WKS As Worksheet Application.ScreenUpdating = False With ActiveSheet.PageSetup For Each WKS In ActiveWorkbook.Worksheets .LeftHeader = Range("A1").Text Next WKS End With Application.ScreenUpdating = True End Sub To extend this, record a macro as you manually create an arbitrary header. The code it creates will reveal a plethora of other page setup options you can tweak in code. To automate the process of updating the headers, place something in the workbook before_print event that calls MakeHeaders. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Headers & Footers | New Users to Excel | |||
Headers/Footers from a named range of cells in a worksheet | Excel Discussion (Misc queries) | |||
headers and footers | Excel Discussion (Misc queries) | |||
Headers and Footers in VBA | Excel Discussion (Misc queries) | |||
headers footers | Excel Discussion (Misc queries) |