Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts

    Fit to page (2002)

    I have a workbook with a macro that, I believe, Hans provided. The macro takes information from the single sheet workbook and creates five other sheets based on data in the original sheet. Each month, the user deletes the newly created sheets and enters data in the original, then runs the macro again that creates the other 5.

    Each time the new sheets are create, the user needs to go to each of them and edit the print set up to print 1 page wide. There must be a line of code I could put in the macro that would take care of this automatically when the new sheets are created. Thanks in advance, especially to Hans, for help.

    KW

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fit to page (2002)

    Say your code uses a variable wsh of type Worksheet. The following will specify that the sheet has to be fit to one page wide when printing:
    <code>
    With wsh.PageSetup
    .Zoom = False
    .FitToPagesWide = 1
    End With
    </code>
    Replace wsh with the actual name of the Worksheet variable.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts

    Re: Fit to page (2002)

    Not sure where to place that. Here's the macro:

    Public Sub Separate()
    Dim lLastRow As Long, lLastCol As Long, I As Long
    Dim oMst As Worksheet, oTgt As Worksheet

    Application.ScreenUpdating = False
    Set oMst = Worksheets("MasterSheet")
    lLastRow = oMst.Range("A65536").End(xlUp).Row - 1
    lLastCol = oMst.Range("IV1").End(xlToLeft).Column - 1
    For I = 1 To lLastRow
    Set oTgt = Nothing
    On Error Resume Next
    Set oTgt = Worksheets(oMst.Range("A1").Offset(I, 1).Value)
    On Error GoTo 0
    If oTgt Is Nothing Then
    Set oTgt = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oTgt.Name = oMst.Range("A1").Offset(I, 1).Value
    oMst.Cells.Copy
    oTgt.Range("A1").PasteSpecial Paste:=xlPasteFormats
    oMst.Range("A1").EntireRow.Copy
    oTgt.Paste Destination:=oTgt.Range("A1")
    oTgt.Range("A1").Select
    End If
    oMst.Range("A1").Offset(I, 1).EntireRow.Copy Destination:=oTgt.Range("A65536").End(xlUp).Offset (1, 0)
    Next I
    Application.CutCopyMode = False
    For Each oTgt In Worksheets
    If oTgt.Name <> oMst.Name Then
    lLastRow = oTgt.Range("A65536").End(xlUp).Row
    oTgt.Range("G2").Formula = "=D2-F2"
    End If
    Next oTgt
    Worksheets("0").PageSetup.LeftHeader = "VOID"
    Worksheets("1").PageSetup.LeftHeader = "Carl"
    Worksheets("2").PageSetup.LeftHeader = "Kenneth"
    Worksheets("3").PageSetup.LeftHeader = "Jonathan"
    Worksheets("4").PageSetup.LeftHeader = "Gary"
    Worksheets("5").PageSetup.LeftHeader = "Bonnie"
    Application.ScreenUpdating = True
    oMst.Activate
    End Sub

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts

    Re: Fit to page (2002)

    Seems to work if placed here:

    ...
    End If
    oMst.Range("A1").Offset(I, 1).EntireRow.Copy Destination:=oTgt.Range("A65536").End(xlUp).Offset (1, 0)
    With oTgt.PageSetup
    .Zoom = False
    .FitToPagesWide = 1
    End With
    Next I
    ...

Posting Permissions

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