| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel
I am trying to program a macro in Excel that when you run it, it puts the
date and time in the top-left header and the pathname in the bottom right footer. I also want the worksheet in the center header. I have it where all of this takes place. However, I want it to not label the center header if it is the default name (Sheet1, Sheet2, Sheet3, etc.). Below is the programming I have done, but I can't seem to get the Default name not to show. Can anyone give me some help or advice. Thanks! ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If CenterHeader = "Sheet1" Then CenterHeader = "" End If If CenterHeader = "Sheet2" Then CenterHeader = "" End If If CenterHeader = "Sheet3" Then CenterHeader = "" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub Aaron |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel
This is Excel 2003 running on Windows Server 2003. Thanks!
"KnightRiderAW" wrote: I am trying to program a macro in Excel that when you run it, it puts the date and time in the top-left header and the pathname in the bottom right footer. I also want the worksheet in the center header. I have it where all of this takes place. However, I want it to not label the center header if it is the default name (Sheet1, Sheet2, Sheet3, etc.). Below is the programming I have done, but I can't seem to get the Default name not to show. Can anyone give me some help or advice. Thanks! ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If CenterHeader = "Sheet1" Then CenterHeader = "" End If If CenterHeader = "Sheet2" Then CenterHeader = "" End If If CenterHeader = "Sheet3" Then CenterHeader = "" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub Aaron |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel
Is this what you mean?
ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If ActiveSheet.Name = ActiveSheet.CodeName Then CenterHeader = "" Else .CenterHeader = "&A" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... I am trying to program a macro in Excel that when you run it, it puts the date and time in the top-left header and the pathname in the bottom right footer. I also want the worksheet in the center header. I have it where all of this takes place. However, I want it to not label the center header if it is the default name (Sheet1, Sheet2, Sheet3, etc.). Below is the programming I have done, but I can't seem to get the Default name not to show. Can anyone give me some help or advice. Thanks! ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If CenterHeader = "Sheet1" Then CenterHeader = "" End If If CenterHeader = "Sheet2" Then CenterHeader = "" End If If CenterHeader = "Sheet3" Then CenterHeader = "" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub Aaron |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel
Unfortunately, this doesn't work. I also tried to do variations on what you
had suggested by removing the CenterHeader Command prior to the IF THEN statement and also adding a period in front of the first CenterHeader Command in the IF THEN statement. None of the three options worked. They still list the Sheet1 at the top of the page. Any other suggestions? Thanks! Aaron "Bob Phillips" wrote: Is this what you mean? ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If ActiveSheet.Name = ActiveSheet.CodeName Then CenterHeader = "" Else .CenterHeader = "&A" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... I am trying to program a macro in Excel that when you run it, it puts the date and time in the top-left header and the pathname in the bottom right footer. I also want the worksheet in the center header. I have it where all of this takes place. However, I want it to not label the center header if it is the default name (Sheet1, Sheet2, Sheet3, etc.). Below is the programming I have done, but I can't seem to get the Default name not to show. Can anyone give me some help or advice. Thanks! ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If CenterHeader = "Sheet1" Then CenterHeader = "" End If If CenterHeader = "Sheet2" Then CenterHeader = "" End If If CenterHeader = "Sheet3" Then CenterHeader = "" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub Aaron |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel
It does exactly what I thought you were trying to do, so I guess I don't
understand what it is you want. What are you trying to do exactly? -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Unfortunately, this doesn't work. I also tried to do variations on what you had suggested by removing the CenterHeader Command prior to the IF THEN statement and also adding a period in front of the first CenterHeader Command in the IF THEN statement. None of the three options worked. They still list the Sheet1 at the top of the page. Any other suggestions? Thanks! Aaron "Bob Phillips" wrote: Is this what you mean? ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If ActiveSheet.Name = ActiveSheet.CodeName Then CenterHeader = "" Else .CenterHeader = "&A" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... I am trying to program a macro in Excel that when you run it, it puts the date and time in the top-left header and the pathname in the bottom right footer. I also want the worksheet in the center header. I have it where all of this takes place. However, I want it to not label the center header if it is the default name (Sheet1, Sheet2, Sheet3, etc.). Below is the programming I have done, but I can't seem to get the Default name not to show. Can anyone give me some help or advice. Thanks! ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If CenterHeader = "Sheet1" Then CenterHeader = "" End If If CenterHeader = "Sheet2" Then CenterHeader = "" End If If CenterHeader = "Sheet3" Then CenterHeader = "" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub Aaron |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel
Bob,
What I am trying to do is make so that the Excel Tab name will appear at the top center of the page when you print. However, if it has the default name (Sheet1, Sheet2, etc.), I don't want to appear at all. When I put your coding suggestion into the macro, it still shows Sheet1 at the top of the page. I am trying to get rid of that only if it is the default. Thanks! Aaron "Bob Phillips" wrote: It does exactly what I thought you were trying to do, so I guess I don't understand what it is you want. What are you trying to do exactly? -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Unfortunately, this doesn't work. I also tried to do variations on what you had suggested by removing the CenterHeader Command prior to the IF THEN statement and also adding a period in front of the first CenterHeader Command in the IF THEN statement. None of the three options worked. They still list the Sheet1 at the top of the page. Any other suggestions? Thanks! Aaron "Bob Phillips" wrote: Is this what you mean? ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If ActiveSheet.Name = ActiveSheet.CodeName Then CenterHeader = "" Else .CenterHeader = "&A" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... I am trying to program a macro in Excel that when you run it, it puts the date and time in the top-left header and the pathname in the bottom right footer. I also want the worksheet in the center header. I have it where all of this takes place. However, I want it to not label the center header if it is the default name (Sheet1, Sheet2, Sheet3, etc.). Below is the programming I have done, but I can't seem to get the Default name not to show. Can anyone give me some help or advice. Thanks! ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If CenterHeader = "Sheet1" Then CenterHeader = "" End If If CenterHeader = "Sheet2" Then CenterHeader = "" End If If CenterHeader = "Sheet3" Then CenterHeader = "" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub Aaron |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel
Aaron,
Not for me it doesn't. Are you wanting it to be automatic, at print time, or will you run that macro for each worksheet? -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Bob, What I am trying to do is make so that the Excel Tab name will appear at the top center of the page when you print. However, if it has the default name (Sheet1, Sheet2, etc.), I don't want to appear at all. When I put your coding suggestion into the macro, it still shows Sheet1 at the top of the page. I am trying to get rid of that only if it is the default. Thanks! Aaron "Bob Phillips" wrote: It does exactly what I thought you were trying to do, so I guess I don't understand what it is you want. What are you trying to do exactly? -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Unfortunately, this doesn't work. I also tried to do variations on what you had suggested by removing the CenterHeader Command prior to the IF THEN statement and also adding a period in front of the first CenterHeader Command in the IF THEN statement. None of the three options worked. They still list the Sheet1 at the top of the page. Any other suggestions? Thanks! Aaron "Bob Phillips" wrote: Is this what you mean? ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If ActiveSheet.Name = ActiveSheet.CodeName Then CenterHeader = "" Else .CenterHeader = "&A" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... I am trying to program a macro in Excel that when you run it, it puts the date and time in the top-left header and the pathname in the bottom right footer. I also want the worksheet in the center header. I have it where all of this takes place. However, I want it to not label the center header if it is the default name (Sheet1, Sheet2, Sheet3, etc.). Below is the programming I have done, but I can't seem to get the Default name not to show. Can anyone give me some help or advice. Thanks! ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If CenterHeader = "Sheet1" Then CenterHeader = "" End If If CenterHeader = "Sheet2" Then CenterHeader = "" End If If CenterHeader = "Sheet3" Then CenterHeader = "" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub Aaron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
