updating pagesetup using cells from master worksheet - orientation & formatting footer - excel vba

[EvaFrank]

Hello,

I am new to excel vba and trying to complete my 1st project. I have the following problems but if anyone has feedback on a better way of doing things - let me know.

I want to create a "master" or "driver" macro workbook. This workbook will contain a sheet (adHoc) that will contain instructions how to format another workbook. I am working on the page setup portion now and I am stuck on a couple of statements.

#1 I want to update the footer - cell b28 of my master worksheet contains this

"&9 2014 YTD Measure Data for PCP" & Chr(10) & " &D &T" & Chr(10) & " Version 1.0" & Chr(10) & " &F"

when I run the macro - I want the formatting to be applied to the text but the entire statement as is displayed - I don't want to see the &9 or quotes - How do I get the macro to apply the format codes?

#2 I get a popup box with "type mismatch" when I set the orientation. cell b36 contains the value xlLandscape If I use the statement .PageSetup.Orientation = xlLandscape it works fine - it just has a problem reading the value from b36.


Julie

Here is the code that I am using.

Sub pg_setup()

    Dim reportWB As Excel.Workbook
    
    Dim sheet As Excel.Worksheet
    
    'open report workbook - name of workbook is in cell b4
    Set reportWB = Workbooks.Open(Workbooks("macros.xlsm").Sheets("adHoc").Range("b4").Value)
   
    Dim leftFooter
          
    leftFooter = Workbooks("macros.xlsm").Sheets("adHoc").Range("b28").Value
       
    For Each sheet In reportWB.Sheets
            
      With sheet
      
         .PageSetup.leftFooter = leftFooter
            
         .PageSetup.Orientation = Workbooks("macros.xlsm").Sheets("adHoc").Range("b36").Value
       
      End With     
    
    Next
    
End Sub