Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    I've written code using VBA to run a Macro on an Excel spreadsheet which includes text as well as referencing a changeable cell within the spreadsheet.

    However, I don't want the Macro to run from the first page, but from the second.

    Can anyone please help me with regards to how I can do this and if so what the code is.

    Many thanks.......I'm totally out of my comfort zone on this one!!

    Penny

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    Do you mean the second worksheet?

    Can you tell us some more about what you want to accomplish with the macro? Thanks in advance.

  3. #3
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans

    Thanks for your quick response!

    Basically (she says!) I want to put a header on a worksheet within a workbook. The header is part text and part extrapolating information from a particular cell which is changeable depending on who the worksheet is being completed for (i.e. change in Company names). However, I don't want the Macro to run on the first page as that is the title page, I'd like it to run from the second page. The macro I have so far is:

    Sub UpdateHeader()

    ActiveSheet.PageSetup.LeftHeader = "&40Risk Profile for " & Range("c31").Value
    End Sub


    This gives me the Macro but it shows on all pages, whereas I want it to miss out the first page and start running from the second page on that worksheet, but I don't know how to do this. On Word, you can request that a header doesn't show on the first page, but I can't see that you can do this on Excel so don't know how to accomplish this......

    Does all that make sense? (fingers crossed!)

    Thanks again.

    Penny

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You'll find several examples of how to set the header or footer for all but the first page on McGimpsey & Associates : Excel : subs : first sheet header and footer.
    Post back if you have problems implementing those ideas.

  5. #5
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans

    Thanks ever so much for that link......I've input this:

    Public Sub NoFirstPageHeader_AllSheets()
    Dim wsSheet As Worksheet
    Dim sHeader As String
    For Each wsSheet In Worksheets
    With wsSheet
    sHeader = .PageSetup.LeftHeader
    ActiveSheet.PageSetup.LeftHeader = "&40Risk Profile for " & Range("c31").Value
    .PageSetup.LeftHeader = sHeader
    End With
    Next wsSheet
    End Sub

    But its still putting the header on the first page....??? Can you tell if I'm missing anything obvious?

    Many thanks for your help so far.

    Penny

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Excel does not really have a way to place a different (or no) header/footer on the first page of a printout only, so you have to change the header/footer on the fly during the printout. Here is the example from McGimpsey's page modified for your situation:

    Code:
    Public Sub NoFirstPageHeader_AllSheets()
      Dim wsSheet As Worksheet
      For Each wsSheet In ActiveWorkbook.Worksheets
    	With wsSheet
    	  ' Clear header before printing the first page
    	  .PageSetup.LeftHeader = ""
    	  ' Print the first page
    	  .PrintOut From:=1, To:=1
    	  ' Set the header for subsequent pages
    	  .PageSetup.LeftHeader = "&40Risk Profile for " & wsSheet.Range("C31").Value
    	  ' Print the subsequent pages
    	  .PrintOut From:=2
    	End With
      Next wsSheet
    End Sub

  7. #7
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans

    Sorry for the huge delay in responding to you, I've been out of the office so haven't been working on this.

    I've now input the code that you modified for me and I'm afraid to say that it's still printing on the first page. Sorry to sound silly but what do you mean by saying that I'll have to 'change the header/footer on the fly' during the printout?

    Many thanks once again...dunno what I'd do without you here to help me!

    Penny

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The macro that I posted will print each worksheet in the workbook in two steps:

    1. The left-hand part of the header is cleared by the code ("on the fly")

    .PageSetup.LeftHeader = ""

    and the first page of the sheet is printed:

    .PrintOut From:=1, To:=1

    There should be no left-hand part of the header in the printout.

    2. The left-hand part of the header is populated:

    .PageSetup.LeftHeader = "&40Risk Profile for " & wsSheet.Range("C31").Value

    and the remaining pages of the sheet are printed:

    .PrintOut From:=2

    There should be a left-hand part of the header in the printout.

    If you have specified a center header and/or a right-hand part of the header in the Page Setup dialog, they will not be affected by the macro; they will be printed on ALL pages.

  9. #9
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ahhh....think I'm with you now......however, it automatically starts printing the entire workbook when we only want it to print that particular worksheet (the Summary Sheet) as the other worksheets merely populate the 'Summary Sheet'.......How can I get it to just print that particular worksheet automatically, I will obviously need to add something into the code you've (very kindly) given me...??

    Thanks Hans

    Penny

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you change the line

    For Each wsSheet In ActiveWorkbook.Worksheets

    to

    For Each wsSheet In ActiveWindow.SelectedSheets

    the macro will print the currently selected sheet(s).

    If you only ever want to print the Summary Sheet, you can simplify the macro to

    Code:
    Public Sub NoFirstPageHeader()
      With Worksheets("Summary Sheet")
    	' Clear header before printing the first page
    	.PageSetup.LeftHeader = ""
    	' Print the first page
    	.PrintOut From:=1, To:=1
    	' Set the header for subsequent pages
    	.PageSetup.LeftHeader = "&40Risk Profile for " & .Range("C31").Value
    	' Print the subsequent pages
    	.PrintOut From:=2
      End With
    End Sub
    Replace Summary Sheet with the actual name of the sheet.

  11. #11
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You're a star!!!! It works!!!

    Although I've got another problem. I've got some text (ie the company name and copyright text) on the bottom of each page (not in the footer as I'd probably have to write more code to copy the copyright logo into the footer!!) and thats printing on a separate page each time. There's 6 pages in total, but it keeps putting the text on the bottom of the page onto a new sheet. Would you know why this is?

    Thanks again......

    Penny

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    There's probably not enough space for the text. Does it help if you decrease the bottom margin in the Page Setup dialog?

  13. #13
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Morning Hans!

    Again - sorry for delay - I'm out of the office more than 'in' these days!

    I'm not sure that's the problem as if I go to 'Page Preview' the text is on the page, however, when I print it out that's when it prints those two lines at the bottom on the page on a separate page....but it does it for all 6 pages within the worksheet...??

    Are you able to offer any advice?

    Many thanks once again

    Penny

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Could you attach a small sample workbook that demonstrates the problem? Delete or alter sensitive information.

  15. #15
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans!

    Great to hear from you!

    You may or may not have seen that I've posted another topic (well another two). I've decided to use the same format of vb code for this problem as I have for the header and footer situation as I don't want this on the front page either! Hopefully that makes sense to do that?

    On the other post I've included the code I've used but the issues I have now is that I want the first line of text to be in blue (0,153,204) and the copyright statement to be two lines down.....

    I can't find for the life of me the little codes you can use to alter text (I think I must be being really stupid today ) so wonder if you know how I can include this in my code.

    You've helped me so much so far - I'm sure you'll be glad when I've got this all sorted!!

    Many thanks.

    Penny

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •