PageSetup problem in Macro 1

[DahMurf]

I have a spreadsheet (Format Reports.xls) that when it opens kicks off a macro. The macro then opens a different spreadsheet file, does formatting, closes the file, moves on to open other files to do the same type of formatting.

This is an excerpt of my macro code in "ThisWorkbook" of file "Format Reports.xls" which is the driver file that is opened:

Public Sub Workbook_Open()
    Workbooks.Open Filename:= _
        "C:\detail report 1.xls"
    Application.Run "'Format Reports.xls'!ThisWorkbook.detail_report_1"
 
    ActiveWindow.Close
      
    Workbooks.Open Filename:= _
     "C:\Formatting Complete.xls"

End Sub


Sub detail_report_1()
    Application.Run "'Format Reports.xls'!ThisWorkbook.fmt2"
    (other misc formatting code)
    ActiveWorkbook.SaveAs Filename:= _
        "C:\detail report 1.xls" _
        , FileFormat:=xlExcel5, Password:="", WriteResPassword:="password", _
        ReadOnlyRecommended:=True, CreateBackup:=False
    ActiveWindow.Close
End Sub

Sub fmt2()
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.Font.Bold = True
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    Cells.Select
    Cells.EntireColumn.AutoFit
    Selection.ColumnWidth = 6.14
    Cells.EntireRow.AutoFit
    Cells.EntireColumn.AutoFit
    Selection.ColumnWidth = 8.14
    Cells.EntireRow.AutoFit
    Cells.EntireColumn.AutoFit
    Cells.EntireRow.AutoFit
    Cells.EntireRow.AutoFit
    Range("A1").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
        .LeftHeader = ""
        .CenterHeader = "&A"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P of &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.675)
        .BottomMargin = Application.InchesToPoints(0.675)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
    End With
End Sub

The problem I am having is that the top half of the code in the fmt2 macro is working on the file "detail report 1.xls" including this code:

    Rows("2:2").Select
    ActiveWindow.FreezePanes = True

but the second half from the ActiveSheet.PageSetup command down is being executed on the file "Format Report.xls" rather then the file "detail report 1.xls":

    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
        .LeftHeader = ""
        .CenterHeader = "&A"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P of &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.675)
        .BottomMargin = Application.InchesToPoints(0.675)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
       .Draft = False
       .PaperSize = xlPaperLetter
       .FirstPageNumber = xlAutomatic
       .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
    End With

I've looked up some options in this forum but everything I've tried hasn't worked. How do I identify the file "detail report 1.xls" to be the focus of the PageSetup command?
Thanks!

 

[firefytr]

Hi,

You're not specifying before any of the "Cells" objects *which* cells they are associated with. You need to tell it not only what sheet those cells are on, but what workbook that worksheet is in. It's called explicitly setting your objects/references. It might go something like this ...

Public Sub Workbook_Open()
    Dim wbDetail As Workbook, wbComplete As Workbook
    If IsWbOpen("detail report 1.xls") Then
        Set wbDetail = Workbooks("detail report 1.xls")
    Else
        Set wbDetail = Workbooks.Open("C:\detail report 1.xls")
    End If
    Application.Run "'Format Reports.xls'!ThisWorkbook.detail_report_1"
    wbDetail.Close savechanges:=False
    If IsWbOpen("Formatting Complete.xls") Then
        Set wbComplete = Workbooks("Formatting Complete.xls")
    Else
        Set wbComplete = Workbooks.Open("C:\Formatting Complete.xls")
    End If
    Debug.Print wbDetail.Name
    Debug.Print wbComplete.Name
    Set wbDetail = Nothing
    Set wbComplete = Nothing
End Sub

Sub fmt2()
    Dim wb As Workbook, ws As Worksheet
    Set wb = Workbooks("book1.xls")
    Set ws = wb.Sheets("sheet1")
    ws.Cells.WrapText = True
    ws.Cells.Font.Bold = True
    wb.Activate
    ws.Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    ws.Range("B:B").ColumnWidth = 6.14
    ws.Range("C:C").ColumnWidth = 8.14
    ws.Cells.EntireColumn.AutoFit
    ws.Cells.EntireRow.AutoFit
    ws.PageSetup.PrintTitleRows = "$1:$1"
    ws.PageSetup.CenterHeader = "&A"
    ws.PageSetup.CenterFooter = "Page &P of &N"
    ws.PageSetup.LeftMargin = Application.InchesToPoints(0.5)
    ws.PageSetup.RightMargin = Application.InchesToPoints(0.5)
    ws.PageSetup.TopMargin = Application.InchesToPoints(0.675)
    ws.PageSetup.BottomMargin = Application.InchesToPoints(0.675)
    ws.PageSetup.HeaderMargin = Application.InchesToPoints(0.5)
    ws.PageSetup.FooterMargin = Application.InchesToPoints(0.5)
    ws.PageSetup.Orientation = xlLandscape
End Sub

This is the function used, it goes in a standard module ..

Public Function IsWbOpen(wbName As String) As Boolean
    On Error Resume Next
    IsWbOpen = Len(Workbooks(wbName).Name)
End Function

Not really sure what this is all supposed to be doing, but it might get you started.

HTH

-----------
Regards,
Zack Barresse

 

[DahMurf]

Excellent! This is exactly what I needed! I integrated it into my code & it works great!

Thank you so much!

 

[firefytr]

Great! You're very welcome! Glad to hear it works for you.

-----------
Regards,
Zack Barresse