Format variables in a footer 2

[sschoberg]

In an Excel spreadsheet I would like to create a macro to get alpha numeric information from a particular cell, paste that information to the left footer, and then format it to size 12, bold, & italic. Then I want to take information from a different cell, paste it to the same footer after that first information, do a carriage return, and format this second part to size 10, regular. Since the information in these two cells changes each time I create the spreadsheet, I think I need to use variables for each cell to carry the information to the footer. Does anyone have an example of how I can do all of this?

 

[SkipVought]

Hi,

First Macro-Record doing exactly what you want and post your generated code for a start.

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[sschoberg]

'First get the values in the cells

Dim Hospital As Variant
Dim Phone As Variant
Dim Pharmacist As Variant
Range("C1").Select
Hospital = ActiveCell.Value
Range("B1").Select
Phone = ActiveCell.Value
Range("D4").Select
Pharmacist = ActiveCell.Value

'Then put the variables in the footer and format them

With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""Arial,Bold""&12MEDICATION RECONCILIATION LIST FOR DISCHARGE"
        .RightHeader = ""
        .LeftFooter = _
        "&""Arial,Bold Italic""&12 & Hospital" & Chr(10) & "Arial,Regular""&10 & Phone"
        .CenterFooter = ""
        .RightFooter = _
        "&""Arial,Bold""&9PHARMACIST: &""Arial,Regular""&9 & Pharmacist" & Chr(10) & "Page &P of &N"

 

[SkipVought]

    Dim sHospital As String
    Dim sPhone As String
    Dim sPharmacist As String
    sHospital = [C1]
    sPhone = [B1]
    sPharmacist = [D4]

    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""Arial,Bold""&12MEDICATION RECONCILIATION LIST FOR DISCHARGE"
        .RightHeader = ""
        .LeftFooter = _
        "&""Arial,Bold Italic""&12 & Hospital " & sHospital & Chr(10) & "&""Arial,Regular""&10 & Phone " & sPhone
        .CenterFooter = ""
        .RightFooter = _
        "&""Arial,Bold""&9PHARMACIST: &""Arial,Regular""&9 " & sPharmacist & Chr(10) & "Page &P of &N"
    End With

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[sschoberg]

Thank you for your reply and I copied your code into my macro exactly as you stated. Even though there is information in each of the cells on the spreadsheet, it appears that the variables are not being loaded with it and so nothing is printing out.

 

[SkipVought]

When you say that, "...nothing is printing out." does that mean that after you run the procedure and PRINT, that the variables are not inserted into the header/footer or NOTHING is printing?

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[sschoberg]

I meant to say after the procedure is run and printed, in the footer where I expect to see the information printing that are in the variables there is nothing. So for example on the left footer I only see
Hospital
Phone
but it should be
Hospital Good Samaritan
Phone (###)-###-####
where the hospital name came from cell C1 and their phone from cell B1.

 

[SkipVought]

explicitly state the worksheet...

  With Worksheets("YourSheetNameHere")
    sHospital = .[C1]
    sPhone = .[B1]
    sPharmacist = .[D4]
  End with

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[sschoberg]

It's still not working - it's doing the same as I described previously. This is what I have in the first section of the macro.

Dim sHospital As String
Dim sPhone As String
Dim sPharmacist As String
With Worksheets("Medlist")
sHospital = .[C1]
sPhone = .[B1]
sPharmacist = .[D4]
End With

This is what I have later on.

With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""Arial,Bold""&12MEDICATION RECONCILIATION LIST FOR DISCHARGE"
        .RightHeader = ""
        .LeftFooter = _
        "&""Arial,Bold Italic""&12 & Hospital" & sHospital & Chr(10) & "&""Arial,Regular""&10 & Phone" & sPhone
        .CenterFooter = ""
        .RightFooter = _
        "&""Arial,Bold""&9PHARMACIST: &""Arial,Regular""&9 " & sPharmacist & Chr(10) & "Page &P of &N"
End With

 

[SkipVought]

I'm really puzzled!

I can run this code, copied directly from my former post, naming the sheet containing the data for hospital, pharmasist and phone, and perform a page setup on ANY SHEET in my workbook. The results in Print Preview indicates that the data has been concatenated after I run the code.

If you put a break on the [tt]With ActiveSheet.PageSetup[/tt] statement, what values do you see in sHospital, sPharmasist & sPhone using View/Watch Window?

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[sschoberg]

After a good night's sleep I took one look at the code this morning and realized what was wrong. The first step in my process is to retrieve a file into my worksheet. I had placed your code before this step. So I was trying to take values from cells before those values were there. Thank you very much for your help. It works perfectly now.