If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
can I use a cell content to auto-populate to a header in excel?
any help on this? I would like to use the contents of a cell to auto-populate
the header. Thanks for any assit. |
#2
|
|||
|
|||
can I use a cell content to auto-populate to a header in excel?
Hi,
You need to use code for this: Private Sub Workbook_BeforePrint() ActiveSheet.PageSetup.LeftHeader = Sheets("Sheet1").Range("A1") End sub -- If this helps, please click the Yes button. Cheers, Shane Devenshire "DaveC" wrote: any help on this? I would like to use the contents of a cell to auto-populate the header. Thanks for any assit. |
#3
|
|||
|
|||
can I use a cell content to auto-populate to a header in excel?
Sub CellInHeader()
With ActiveSheet .PageSetup.CenterHeader = .Range("A1").Value End With End Sub Sub CellInHeaderr22() With ActiveSheet.PageSetup .LeftHeader = "&""Algerian,Regular""&16" & Range("A1").Value End With End Sub Sub Cell_In_All_Headers() Dim ws As Worksheet For Each ws In ActiveWorkbook.Sheets ws.PageSetup.CenterHeader = ws.Range("A1").Value Next End Sub Gord Dibben MS Excel MVP On Wed, 17 Jun 2009 12:53:01 -0700, DaveC wrote: any help on this? I would like to use the contents of a cell to auto-populate the header. Thanks for any assit. |
#4
|
|||
|
|||
can I use a cell content to auto-populate to a header in excel
Shane,
I am somewhat new at this, can you provide a little more explantion? I tried to enter the string below into the page setup, custom header field with no success. I'm sure I am doing something wrong. Can you simplfy? When I get the the header set-up box what should I enter? Thanks for your help it is really appreceated. "Shane Devenshire" wrote: Hi, You need to use code for this: Private Sub Workbook_BeforePrint() ActiveSheet.PageSetup.LeftHeader = Sheets("Sheet1").Range("A1") End sub -- If this helps, please click the Yes button. Cheers, Shane Devenshire "DaveC" wrote: any help on this? I would like to use the contents of a cell to auto-populate the header. Thanks for any assit. |
#5
|
|||
|
|||
can I use a cell content to auto-populate to a header in excel
Gord,
You seem to be the wizard of excel and I appreceate the help. I have tried to type "ws.Range("A1").Value" exactly into the header box with no success. This line just appears as the header. Can you tell me what I'm doing wrong or what exact steps I need to take to make this work. Thanks again. Dave "Gord Dibben" wrote: Sub CellInHeader() With ActiveSheet .PageSetup.CenterHeader = .Range("A1").Value End With End Sub Sub CellInHeaderr22() With ActiveSheet.PageSetup .LeftHeader = "&""Algerian,Regular""&16" & Range("A1").Value End With End Sub Sub Cell_In_All_Headers() Dim ws As Worksheet For Each ws In ActiveWorkbook.Sheets ws.PageSetup.CenterHeader = ws.Range("A1").Value Next End Sub Gord Dibben MS Excel MVP On Wed, 17 Jun 2009 12:53:01 -0700, DaveC wrote: any help on this? I would like to use the contents of a cell to auto-populate the header. Thanks for any assit. |
#6
|
|||
|
|||
can I use a cell content to auto-populate to a header in excel
You don't type anything into the header.
I provided macros. You choose which one you want to use and run it. If you're not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm or Ron de De Bruin's site on where to store macros. http://www.rondebruin.nl/code.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + r to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run or edit the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord On Thu, 18 Jun 2009 06:45:01 -0700, DaveC wrote: Gord, You seem to be the wizard of excel and I appreceate the help. I have tried to type "ws.Range("A1").Value" exactly into the header box with no success. This line just appears as the header. Can you tell me what I'm doing wrong or what exact steps I need to take to make this work. Thanks again. Dave "Gord Dibben" wrote: Sub CellInHeader() With ActiveSheet .PageSetup.CenterHeader = .Range("A1").Value End With End Sub Sub CellInHeaderr22() With ActiveSheet.PageSetup .LeftHeader = "&""Algerian,Regular""&16" & Range("A1").Value End With End Sub Sub Cell_In_All_Headers() Dim ws As Worksheet For Each ws In ActiveWorkbook.Sheets ws.PageSetup.CenterHeader = ws.Range("A1").Value Next End Sub Gord Dibben MS Excel MVP On Wed, 17 Jun 2009 12:53:01 -0700, DaveC wrote: any help on this? I would like to use the contents of a cell to auto-populate the header. Thanks for any assit. |
#7
|
|||
|
|||
can I use a cell content to auto-populate to a header in excel
Thanks a million, I actually made this work. Feeling good. another question
if I may. Is there a way to change and save the default font settings in the header and footer? I can manually do this but want it to be automatic in a larger font when it is populated from a cell referance that I just ran the macro for. Thanks for any help. "Gord Dibben" wrote: You don't type anything into the header. I provided macros. You choose which one you want to use and run it. If you're not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm or Ron de De Bruin's site on where to store macros. http://www.rondebruin.nl/code.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + r to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run or edit the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord On Thu, 18 Jun 2009 06:45:01 -0700, DaveC wrote: Gord, You seem to be the wizard of excel and I appreceate the help. I have tried to type "ws.Range("A1").Value" exactly into the header box with no success. This line just appears as the header. Can you tell me what I'm doing wrong or what exact steps I need to take to make this work. Thanks again. Dave "Gord Dibben" wrote: Sub CellInHeader() With ActiveSheet .PageSetup.CenterHeader = .Range("A1").Value End With End Sub Sub CellInHeaderr22() With ActiveSheet.PageSetup .LeftHeader = "&""Algerian,Regular""&16" & Range("A1").Value End With End Sub Sub Cell_In_All_Headers() Dim ws As Worksheet For Each ws In ActiveWorkbook.Sheets ws.PageSetup.CenterHeader = ws.Range("A1").Value Next End Sub Gord Dibben MS Excel MVP On Wed, 17 Jun 2009 12:53:01 -0700, DaveC wrote: any help on this? I would like to use the contents of a cell to auto-populate the header. Thanks for any assit. |
#8
|
|||
|
|||
can I use a cell content to auto-populate to a header in excel
You cannot change and save the default settings for a larger font.
Sub Cellinheader22 gives you a method of changing font type and font size. Did you not notice that? Gord On Thu, 18 Jun 2009 10:56:01 -0700, DaveC wrote: Thanks a million, I actually made this work. Feeling good. another question if I may. Is there a way to change and save the default font settings in the header and footer? I can manually do this but want it to be automatic in a larger font when it is populated from a cell referance that I just ran the macro for. Thanks for any help. "Gord Dibben" wrote: You don't type anything into the header. I provided macros. You choose which one you want to use and run it. If you're not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm or Ron de De Bruin's site on where to store macros. http://www.rondebruin.nl/code.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + r to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run or edit the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord On Thu, 18 Jun 2009 06:45:01 -0700, DaveC wrote: Gord, You seem to be the wizard of excel and I appreceate the help. I have tried to type "ws.Range("A1").Value" exactly into the header box with no success. This line just appears as the header. Can you tell me what I'm doing wrong or what exact steps I need to take to make this work. Thanks again. Dave "Gord Dibben" wrote: Sub CellInHeader() With ActiveSheet .PageSetup.CenterHeader = .Range("A1").Value End With End Sub Sub CellInHeaderr22() With ActiveSheet.PageSetup .LeftHeader = "&""Algerian,Regular""&16" & Range("A1").Value End With End Sub Sub Cell_In_All_Headers() Dim ws As Worksheet For Each ws In ActiveWorkbook.Sheets ws.PageSetup.CenterHeader = ws.Range("A1").Value Next End Sub Gord Dibben MS Excel MVP On Wed, 17 Jun 2009 12:53:01 -0700, DaveC wrote: any help on this? I would like to use the contents of a cell to auto-populate the header. Thanks for any assit. |
#9
|
|||
|
|||
can I use a cell content to auto-populate to a header in excel
Gord,
Sorry for my dumbness. What is "sub Cellinheader22"? Thanks "Gord Dibben" wrote: You cannot change and save the default settings for a larger font. Sub Cellinheader22 gives you a method of changing font type and font size. Did you not notice that? Gord On Thu, 18 Jun 2009 10:56:01 -0700, DaveC wrote: Thanks a million, I actually made this work. Feeling good. another question if I may. Is there a way to change and save the default font settings in the header and footer? I can manually do this but want it to be automatic in a larger font when it is populated from a cell referance that I just ran the macro for. Thanks for any help. "Gord Dibben" wrote: You don't type anything into the header. I provided macros. You choose which one you want to use and run it. If you're not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm or Ron de De Bruin's site on where to store macros. http://www.rondebruin.nl/code.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + r to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run or edit the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord On Thu, 18 Jun 2009 06:45:01 -0700, DaveC wrote: Gord, You seem to be the wizard of excel and I appreceate the help. I have tried to type "ws.Range("A1").Value" exactly into the header box with no success. This line just appears as the header. Can you tell me what I'm doing wrong or what exact steps I need to take to make this work. Thanks again. Dave "Gord Dibben" wrote: Sub CellInHeader() With ActiveSheet .PageSetup.CenterHeader = .Range("A1").Value End With End Sub Sub CellInHeaderr22() With ActiveSheet.PageSetup .LeftHeader = "&""Algerian,Regular""&16" & Range("A1").Value End With End Sub Sub Cell_In_All_Headers() Dim ws As Worksheet For Each ws In ActiveWorkbook.Sheets ws.PageSetup.CenterHeader = ws.Range("A1").Value Next End Sub Gord Dibben MS Excel MVP On Wed, 17 Jun 2009 12:53:01 -0700, DaveC wrote: any help on this? I would like to use the contents of a cell to auto-populate the header. Thanks for any assit. |
#10
|
|||
|
|||
can I use a cell content to auto-populate to a header in excel
Gord,
OK I found the "sub cellinheader22, when I enter as a marco should it look lkie this? Private Sub Workbook_Open() ActiveSheet.PageSetup.CenterHeader = "&""arial,bold""&18& rANGE("e1").VALUE ActiveSheet.PageSetup.CenterHeader = Range("e1").Value End Sub This is how I have it now and it does not chnage the font when I run the marco. It does populate the header with the cell content from E1, that is great. Thanks for any help Dave "DaveC" wrote: Gord, Sorry for my dumbness. What is "sub Cellinheader22"? Thanks "Gord Dibben" wrote: You cannot change and save the default settings for a larger font. Sub Cellinheader22 gives you a method of changing font type and font size. Did you not notice that? Gord On Thu, 18 Jun 2009 10:56:01 -0700, DaveC wrote: Thanks a million, I actually made this work. Feeling good. another question if I may. Is there a way to change and save the default font settings in the header and footer? I can manually do this but want it to be automatic in a larger font when it is populated from a cell referance that I just ran the macro for. Thanks for any help. "Gord Dibben" wrote: You don't type anything into the header. I provided macros. You choose which one you want to use and run it. If you're not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm or Ron de De Bruin's site on where to store macros. http://www.rondebruin.nl/code.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + r to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run or edit the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord On Thu, 18 Jun 2009 06:45:01 -0700, DaveC wrote: Gord, You seem to be the wizard of excel and I appreceate the help. I have tried to type "ws.Range("A1").Value" exactly into the header box with no success. This line just appears as the header. Can you tell me what I'm doing wrong or what exact steps I need to take to make this work. Thanks again. Dave "Gord Dibben" wrote: Sub CellInHeader() With ActiveSheet .PageSetup.CenterHeader = .Range("A1").Value End With End Sub Sub CellInHeaderr22() With ActiveSheet.PageSetup .LeftHeader = "&""Algerian,Regular""&16" & Range("A1").Value End With End Sub Sub Cell_In_All_Headers() Dim ws As Worksheet For Each ws In ActiveWorkbook.Sheets ws.PageSetup.CenterHeader = ws.Range("A1").Value Next End Sub Gord Dibben MS Excel MVP On Wed, 17 Jun 2009 12:53:01 -0700, DaveC wrote: any help on this? I would like to use the contents of a cell to auto-populate the header. Thanks for any assit. |
|
Thread Tools | |
Display Modes | |
|
|