Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to update all headers in Excel
We have an Excel Workbook with several different sheets (tabs). Some sheets
are Portrait and some are landscape. This workbook has a data input sheet for alot of the data that is used on several of the sheets. We used the =('Input Sheet'!D37) to automate the data input, but we can't get the Header/Footnote to update. Our problem is that the Header / Footnote Information is on the Data Input Sheet already. How can we take the Data from Input Sheet and place it in the Header / Footnote without changing all the page formatting? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to update all headers in Excel
Try this. This will setup the page (including header/footer information) for
any sheet in the workbook: With Sheets("Your sheet name").PageSetup .PrintArea = "" .PrintTitleRows = "" .PrintTitleColumns = "" .LeftHeader = "" '[Insert the data reference] .CenterHeader = "" '[Insert the data reference] .RightHeader = "" '[Insert the data reference] .LeftFooter = "" '[Insert the data reference] .CenterFooter = "" '[Insert the data reference] .RightFooter = "" '[Insert the data reference] .LeftMargin = Application.InchesToPoints(0.7) .RightMargin = Application.InchesToPoints(0.7) .TopMargin = Application.InchesToPoints(0.75) .BottomMargin = Application.InchesToPoints(0.75) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape 'or xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = True .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With Customize it to fit your particulars. As you can see, virtually every possible parameter is available for modification - any of these items that you're not going to specify can safely be omitted. You can make this a UDF and pass the varying information (header, footer, orientation, etc.) as arguments to avoid having to duplicate these lines for each sheet in the workbook. "Brian" wrote: We have an Excel Workbook with several different sheets (tabs). Some sheets are Portrait and some are landscape. This workbook has a data input sheet for alot of the data that is used on several of the sheets. We used the =('Input Sheet'!D37) to automate the data input, but we can't get the Header/Footnote to update. Our problem is that the Header / Footnote Information is on the Data Input Sheet already. How can we take the Data from Input Sheet and place it in the Header / Footnote without changing all the page formatting? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to update all headers in Excel
Record a macro when you change the print setting for the master worksheet.
Delete the settings that you don't care about (it'll speed up the code). Then you could use: Option Explicit Sub testme() Dim MstrWks As Worksheet Dim wks As Worksheet Set MstrWks = Nothing On Error Resume Next Set MstrWks = ActiveWorkbook.Worksheets("MasterSheetNameHere") On Error GoTo 0 If MstrWks Is Nothing Then MsgBox "Design error!" Exit Sub End If For Each wks In ActiveWorkbook.Worksheets If wks.Name = MstrWks.Name Then 'skip it Else 'do all the work you want. With wks.PageSetup .LeftHeader = MstrWks.PageSetup.LeftHeader .CenterHeader = MstrWks.PageSetup.CenterHeader 'and on and on... End With End If Next wks End Sub If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Brian wrote: We have an Excel Workbook with several different sheets (tabs). Some sheets are Portrait and some are landscape. This workbook has a data input sheet for alot of the data that is used on several of the sheets. We used the =('Input Sheet'!D37) to automate the data input, but we can't get the Header/Footnote to update. Our problem is that the Header / Footnote Information is on the Data Input Sheet already. How can we take the Data from Input Sheet and place it in the Header / Footnote without changing all the page formatting? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to update all headers in Excel
Our company has a Spread Sheet with multiple Sheets (Tabs). Some of the
Sheets are Portrait and some are landscape. Sheet one is a data input sheet (Tab name is = Input Sheet). Any Data that is used multiple times throughout the Spread Sheet is Input here. That part was easy, but getting the Header/Footnote to update on all sheets without changing the page formatting is difficult. We are trying to get the following for the Header: Town: Cell #: D5 TEO No: Cell #: D34 Page 1 of ? Office: Cell #: D38 Supplier Order No: Cell # D16 Appendix No: Cell # D36 Footnote is the same on all sheets and never changes. Can anyone please help us? I wish I could send the file to someone and have them do it for us. "Dave Peterson" wrote: Record a macro when you change the print setting for the master worksheet. Delete the settings that you don't care about (it'll speed up the code). Then you could use: Option Explicit Sub testme() Dim MstrWks As Worksheet Dim wks As Worksheet Set MstrWks = Nothing On Error Resume Next Set MstrWks = ActiveWorkbook.Worksheets("MasterSheetNameHere") On Error GoTo 0 If MstrWks Is Nothing Then MsgBox "Design error!" Exit Sub End If For Each wks In ActiveWorkbook.Worksheets If wks.Name = MstrWks.Name Then 'skip it Else 'do all the work you want. With wks.PageSetup .LeftHeader = MstrWks.PageSetup.LeftHeader .CenterHeader = MstrWks.PageSetup.CenterHeader 'and on and on... End With End If Next wks End Sub If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Brian wrote: We have an Excel Workbook with several different sheets (tabs). Some sheets are Portrait and some are landscape. This workbook has a data input sheet for alot of the data that is used on several of the sheets. We used the =('Input Sheet'!D37) to automate the data input, but we can't get the Header/Footnote to update. Our problem is that the Header / Footnote Information is on the Data Input Sheet already. How can we take the Data from Input Sheet and place it in the Header / Footnote without changing all the page formatting? -- Dave Peterson . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to update all headers in Excel
I wish i could just send the file to someone and have them do it.
"Brian" wrote: We have an Excel Workbook with several different sheets (tabs). Some sheets are Portrait and some are landscape. This workbook has a data input sheet for alot of the data that is used on several of the sheets. We used the =('Input Sheet'!D37) to automate the data input, but we can't get the Header/Footnote to update. Our problem is that the Header / Footnote Information is on the Data Input Sheet already. How can we take the Data from Input Sheet and place it in the Header / Footnote without changing all the page formatting? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to update all headers in Excel
Is there any way to sent the Sheet to someone and let them do it. i am not
good at this. "Dave Peterson" wrote: Record a macro when you change the print setting for the master worksheet. Delete the settings that you don't care about (it'll speed up the code). Then you could use: Option Explicit Sub testme() Dim MstrWks As Worksheet Dim wks As Worksheet Set MstrWks = Nothing On Error Resume Next Set MstrWks = ActiveWorkbook.Worksheets("MasterSheetNameHere") On Error GoTo 0 If MstrWks Is Nothing Then MsgBox "Design error!" Exit Sub End If For Each wks In ActiveWorkbook.Worksheets If wks.Name = MstrWks.Name Then 'skip it Else 'do all the work you want. With wks.PageSetup .LeftHeader = MstrWks.PageSetup.LeftHeader .CenterHeader = MstrWks.PageSetup.CenterHeader 'and on and on... End With End If Next wks End Sub If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Brian wrote: We have an Excel Workbook with several different sheets (tabs). Some sheets are Portrait and some are landscape. This workbook has a data input sheet for alot of the data that is used on several of the sheets. We used the =('Input Sheet'!D37) to automate the data input, but we can't get the Header/Footnote to update. Our problem is that the Header / Footnote Information is on the Data Input Sheet already. How can we take the Data from Input Sheet and place it in the Header / Footnote without changing all the page formatting? -- Dave Peterson . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to update all headers in Excel
Maybe someone will volunteer to help.
Or maybe you'll try it and see that it's not so difficult. Brian wrote: Is there any way to sent the Sheet to someone and let them do it. i am not good at this. "Dave Peterson" wrote: Record a macro when you change the print setting for the master worksheet. Delete the settings that you don't care about (it'll speed up the code). Then you could use: Option Explicit Sub testme() Dim MstrWks As Worksheet Dim wks As Worksheet Set MstrWks = Nothing On Error Resume Next Set MstrWks = ActiveWorkbook.Worksheets("MasterSheetNameHere") On Error GoTo 0 If MstrWks Is Nothing Then MsgBox "Design error!" Exit Sub End If For Each wks In ActiveWorkbook.Worksheets If wks.Name = MstrWks.Name Then 'skip it Else 'do all the work you want. With wks.PageSetup .LeftHeader = MstrWks.PageSetup.LeftHeader .CenterHeader = MstrWks.PageSetup.CenterHeader 'and on and on... End With End If Next wks End Sub If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Brian wrote: We have an Excel Workbook with several different sheets (tabs). Some sheets are Portrait and some are landscape. This workbook has a data input sheet for alot of the data that is used on several of the sheets. We used the =('Input Sheet'!D37) to automate the data input, but we can't get the Header/Footnote to update. Our problem is that the Header / Footnote Information is on the Data Input Sheet already. How can we take the Data from Input Sheet and place it in the Header / Footnote without changing all the page formatting? -- Dave Peterson . -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to update all headers in Excel
Hi Brian,
Give this code a try. You'll want to insert it into a Module in the VBE (Visual Basic Editor). You get there by opening your file, and pressing ALT+F11. In the VBE screen go to InsertModule. Copy and paste this code into the empty window that appears. Save your file. Close the VBE window. In your Excel window, press ALT+F8 to bring up the listing of macros associated to this workbook. Double click DynamicHeader to run the macro. This macro assumes that you've already set each page as either Portrait or Landscape and doesn't change that setting. 'code starts: Sub DynamicHeader() Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "Town: " & Sheets("Input Sheet").Range("D5") _ .Value & vbNewLine & "Office: " & Sheets("Input Sheet").Range("D38").Value .CenterHeader = "TEO No: " & Sheets("Input Sheet").Range("D34") _ .Value & vbNewLine & "Supplier Order No: " & Sheets("Input Sheet") _ .Range("D16").Value .RightHeader = "Page &P of &N" & vbNewLine & "Appendix No: " & Sheets _ ("Input Sheet").Range("D36").Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(1.25) End With Next sh End Sub 'code ends When you test this you'll notice that I put text into each of the available footer options. Substitute your footer text for my sample text in whichever option suits you best, and delete the other two option lines. Hope this helps! Bill "Brian" wrote: I wish i could just send the file to someone and have them do it. "Brian" wrote: We have an Excel Workbook with several different sheets (tabs). Some sheets are Portrait and some are landscape. This workbook has a data input sheet for alot of the data that is used on several of the sheets. We used the =('Input Sheet'!D37) to automate the data input, but we can't get the Header/Footnote to update. Our problem is that the Header / Footnote Information is on the Data Input Sheet already. How can we take the Data from Input Sheet and place it in the Header / Footnote without changing all the page formatting? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to update all headers in Excel
It gives me a Compile Error:
Syntax Error "fisch4bill" wrote: Hi Brian, Give this code a try. You'll want to insert it into a Module in the VBE (Visual Basic Editor). You get there by opening your file, and pressing ALT+F11. In the VBE screen go to InsertModule. Copy and paste this code into the empty window that appears. Save your file. Close the VBE window. In your Excel window, press ALT+F8 to bring up the listing of macros associated to this workbook. Double click DynamicHeader to run the macro. This macro assumes that you've already set each page as either Portrait or Landscape and doesn't change that setting. 'code starts: Sub DynamicHeader() Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "Town: " & Sheets("Input Sheet").Range("D5") _ .Value & vbNewLine & "Office: " & Sheets("Input Sheet").Range("D38").Value .CenterHeader = "TEO No: " & Sheets("Input Sheet").Range("D34") _ .Value & vbNewLine & "Supplier Order No: " & Sheets("Input Sheet") _ .Range("D16").Value .RightHeader = "Page &P of &N" & vbNewLine & "Appendix No: " & Sheets _ ("Input Sheet").Range("D36").Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(1.25) End With Next sh End Sub 'code ends When you test this you'll notice that I put text into each of the available footer options. Substitute your footer text for my sample text in whichever option suits you best, and delete the other two option lines. Hope this helps! Bill "Brian" wrote: I wish i could just send the file to someone and have them do it. "Brian" wrote: We have an Excel Workbook with several different sheets (tabs). Some sheets are Portrait and some are landscape. This workbook has a data input sheet for alot of the data that is used on several of the sheets. We used the =('Input Sheet'!D37) to automate the data input, but we can't get the Header/Footnote to update. Our problem is that the Header / Footnote Information is on the Data Input Sheet already. How can we take the Data from Input Sheet and place it in the Header / Footnote without changing all the page formatting? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to update all headers in Excel
Two questions:
1) What version of Excel are you using? and 2) Does the error designate a particular line that has the syntax error? If so please post it so it can be checked. When I used this code on my machine it worked flawlessly. Check all of your code for spacing and typing errors. Check the following code between the quotes; they should each be in a single line (or multiple lines joined by the line continuation characters): ".Value & vbNewLine & "Office: " & Sheets("Input Sheet").Range("D38").Value" and ".RightHeader = "Page &P of &N" & vbNewLine & "Appendix No: " & Sheets("Input Sheet").Range("D36").Value" Bill "Brian" wrote: It gives me a Compile Error: Syntax Error "fisch4bill" wrote: Hi Brian, Give this code a try. You'll want to insert it into a Module in the VBE (Visual Basic Editor). You get there by opening your file, and pressing ALT+F11. In the VBE screen go to InsertModule. Copy and paste this code into the empty window that appears. Save your file. Close the VBE window. In your Excel window, press ALT+F8 to bring up the listing of macros associated to this workbook. Double click DynamicHeader to run the macro. This macro assumes that you've already set each page as either Portrait or Landscape and doesn't change that setting. 'code starts: Sub DynamicHeader() Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "Town: " & Sheets("Input Sheet").Range("D5") _ .Value & vbNewLine & "Office: " & Sheets("Input Sheet").Range("D38").Value .CenterHeader = "TEO No: " & Sheets("Input Sheet").Range("D34") _ .Value & vbNewLine & "Supplier Order No: " & Sheets("Input Sheet") _ .Range("D16").Value .RightHeader = "Page &P of &N" & vbNewLine & "Appendix No: " & Sheets _ ("Input Sheet").Range("D36").Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(1.25) End With Next sh End Sub 'code ends When you test this you'll notice that I put text into each of the available footer options. Substitute your footer text for my sample text in whichever option suits you best, and delete the other two option lines. Hope this helps! Bill "Brian" wrote: I wish i could just send the file to someone and have them do it. "Brian" wrote: We have an Excel Workbook with several different sheets (tabs). Some sheets are Portrait and some are landscape. This workbook has a data input sheet for alot of the data that is used on several of the sheets. We used the =('Input Sheet'!D37) to automate the data input, but we can't get the Header/Footnote to update. Our problem is that the Header / Footnote Information is on the Data Input Sheet already. How can we take the Data from Input Sheet and place it in the Header / Footnote without changing all the page formatting? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Headers & Footers _auto update info from a cell in the worksheet | Excel Discussion (Misc queries) | |||
lookup using column headers and row headers | Excel Discussion (Misc queries) | |||
Hide Column Headers but not row (Headers) | Excel Programming | |||
i want to update one excel file the other one update automaticaly | New Users to Excel | |||
Excel 2003 - Update or Don't Update Links Problem | Excel Programming |