A Problem In Copying Sheets - Difference in Printing

[Ark68]

In my project, I copy a worksheet from one workbook (WB1) to a new workbook (WB2).
The worksheet in WB1 is all set up to print properly on one page. All the margins are set as well as any scaling (which there isn't any). The print areas are identical.
The worksheet appears to be copied over verbatum to the second workbook, wowever, without any changes, this worksheet does not print on the one page. The margins are the same, and no scaling, all setting appear to be the same.

Is there somewhere else I should be looking to as to why the sheets are not the same from a printing standpoint? For me to fit the same workheet on one page in WB2 would require me to scale the page, which I cannot do.

 

[rollis13]

all setting appear to be the same.

When you copy a sheet to another sheet Excel doesn't copy printer settings so there must be some other setting you didn't check. Maybe if you attach a sample file someone could be of more help.

 

[Ark68]

Hi Rollis. I suspect that could be the problem. I've only checked the page settings. What would I look at in terms of printers settings, and are their ways to apply printer settings between pages to maintain the consistency I'm looking for? I can past an original and copy when I get home if it helps others, but if it's printer settings, won't everyone have different results?

 

[bob33]

Are you manually copying and pasting the worksheet or running VBA to do it?
If VBA, provide sample workbook.

 

[Ark68]

I am running VBA to do it. I can provide the workbook, but it will be highly redacted due to sensitive information contained within. I can only provide the worksheet that I copy and the code I use to copy the sheets(s) to the new workbook. Not sure if it's going to help.

Here is the workbook. The worksheet is the one I wish to copy to a new workbook. The print range is A1:Q44. A print preview puts that range on one page. If I manually copy the worksheet to a new workbook, it works fine. The copied version is identical and fits on one page. I'm assuming VBA is messing it up?

Here is the code I use in one of my routines to copy the worksheet over to the new workbook. The code creates a path string and filename. It checks if the file already exists and if it does, deletes it to be able to recreate it. It creates a new (empty) workbook and saves it as the filename in the path specified. It then copies worksheet ("Master") from the original workbooks 17 times and renames each according the the values in the array. It's these copies that have lost the single page fit that the original does.

Sub dist_create()
'Stop
    Application.ScreenUpdating = False
    Dim filePath As String
    Dim tc As Boolean
    Dim CList(1 To 5) As String
    Dim rw As Integer
    Dim sh As Worksheet
    Dim bLeft, bRight, c As Range
    Dim arrFolders() As String
    Dim i As Integer
    Dim strPath As String
    
    'setup sheets staff
    'check if distributables have been prepared already
'Stop
    mntxt = MonthName(Month(inq_date))
    daytext = WeekdayName(Weekday(inq_date), True)
    crtyr = Year(Now)
    filePath = distpath & crtyr & "\" & mntxt & "\" & Format(Day(inq_date), "00") & " " & UCase(daytext) & "\"
    nfn = "WS " & Format(inq_date, "dd-mmm-yy") & ".xlsx"
    
    If FolderExists(filePath) = False Then
        ui1 = MsgBox("Distributables file for " & Format(inq_date, "dddd mmm-dd") & " does not exist." & Chr(13) & "Create now?", vbInformation + vbYesNo, "Distributables Folder")
            If ui1 = vbYes Then
                Debug.Print filePath
                Call subCreateFolders(filePath)
            Else
                Exit Sub
            End If
    Else
        'ui1 = MsgBox("Distributables Folder Exists" & Chr(13) & filePath & Chr(13) & "[YES] to review; [NO] to recreate", vbInformation + vbYesNoCancel, "Distributables Folder")
        'If ui1 = vbYes Then
        '    Stop
        'ElseIf ui1 = vbNo Then
        '    Stop
        'End If
    End If
    
    'create and save new target workbook
    filePath = filePath & nfn
    If FileExists(filePath) = True Then Kill (filePath) 'delete previous file for now
        
    Workbooks.Add.SaveAs Filename:=filePath
    Set wb_daily = Workbooks(nfn)
    
    'copy sheets
    arrNames = Array("MASTER", "EVL", "EVE", "LWP", "WPL", "WPE", "RPL", "RPE", "HPL", "HPE", "BPL", "BPE", "CUL", "CUE2", "CUE1", "CWP", "CRP", "LSP")
    
'create raw sheets
'Stop
    For i = 0 To 17
        shnm = arrNames(i)
        Debug.Print shnm
        Set ssh = Nothing
        On Error Resume Next
        Set ssh = ThisWorkbook.Sheets("Master")
        On Error GoTo 0
        If Not ssh Is Nothing Then
            ssh.Copy After:=wb_daily.Sheets(1)
            ActiveSheet.Name = shnm
            'sheets are hidden
        End If
        If shnm <> "MASTER" Then
            Stop
            With ActiveSheet
                .Unprotect
            'eliminate buttons
                With ActiveSheet.Columns("Q:AG")
                    For Each shp In .Parent.Shapes
                        If Not Intersect(shp.TopLeftCell, .Cells) Is Nothing Then shp.Delete
                    Next shp
                End With
                With ActiveSheet.Range("D1:R9")
                    For Each shp In .Parent.Shapes
                        If Not Intersect(shp.TopLeftCell, .Cells) Is Nothing Then shp.Delete
                    Next shp
                End With
            'eliminate staffing range
                .Columns("S:AG").Clear
                .Range("O4") = ActiveSheet.Name
                .Protect
            End With
        End If
    Next i
'Stop
    'set worksheets
    Set ws_dmaster = wb_daily.Worksheets("MASTER")
    Set ws_evl = wb_daily.Worksheets("EVL")
    Set ws_eve = wb_daily.Worksheets("EVE")
    Set ws_lwp = wb_daily.Worksheets("LWP")
    Set ws_wpl = wb_daily.Worksheets("WPL")
    Set ws_wpe = wb_daily.Worksheets("WPE")
    Set ws_rpl = wb_daily.Worksheets("RPL")
    Set ws_rpe = wb_daily.Worksheets("RPE")
    Set ws_hpl = wb_daily.Worksheets("HPL")
    Set ws_hpe = wb_daily.Worksheets("HPE")
    Set ws_bpl = wb_daily.Worksheets("BPL")
    Set ws_bpe = wb_daily.Worksheets("BPE")
    Set ws_cul = wb_daily.Worksheets("CUL")
    Set ws_cue2 = wb_daily.Worksheets("CUE2")
    Set ws_cuel = wb_daily.Worksheets("CUE1")
    Set ws_wrp = wb_daily.Worksheets("CWP")
    Set ws_crp = wb_daily.Worksheets("CRP")
    Set ws_lsp = wb_daily.Worksheets("LSP")
    Application.DisplayAlerts = False
    Worksheets("Sheet1").Delete
    Application.DisplayAlerts = False
    
'refine individual crew sheets
'...  ...  ...  more code follows

(I really value the folk here at Mr. Excel, and I respect the desire to help, but sharing "my code" or my workbook isn't practical. It relies on access to several network drives to even start the application. It is several thousand lines worth of VBA, with 15 worksheets, 22 modules and 8 forms. Also note, several of the variables are declared publically in other modules.).

 

[bob33]

Ark,
Understood on your issues.
I was curious what happens under the hood as a worksheet content is copied and pasted into another workbook and then formatted for printing: Perhaps some of this code might work for you or provide a trigger for your repair?

Sub copier()
'
' copier Macro
'
    Range("A1:G139").Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Range("I1").Select
    Application.CutCopyMode = False
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.3)
        .RightMargin = Application.InchesToPoints(0.3)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 7
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    Windows("Donation_Valuations_20230511.xlsx").Activate
End Sub

 

[Ark68]

Hello Bob,
My sincere apologies for only now acknowledging you contribution. I feel you put a lot of effort into this solution and for that I am very thankful.
I had stepped aside a bit from this project (life and all you know lol) and got back at it to realize I wasn't having this issue after all. Not sure what I'm doing differently now thatn what I was doing when I originally posted, but everything is fitting as copied. It could perhaps have been an issue when I was working on another computer. I will have to see if that's the case when I return to that terminal.
Thanks to you though, I have what could be a solution should I need it.

I'll give you some lovin' but for the sake of others I can't mark as solved yet until it's tested.

 

[Ark68]

So, my suspicions are accurate. The print range changes from one computer to the next. On my home computer, with a higher resolution monitor, everything works as it should. The source worksheet, and each copied worksheet fits on a single printed page. The same worksheet, and the copies, are broken between two pages, on my work computer which is a lower resolution screen. (Not sure if screen has any bearing on print area, but thats the only difference I see). I don't print at home (no printer), I only print at work.

Any thoughts on how to overcome this critical problem?

 

[Ark68]

From my work computer, I gathered the print properties of a finished worksheet (created previously and successfully with this application) that fits on one page. I then added this code to my existing code as the sheets are being copied.

For i = 0 To 17
        shnm = arrNames(i)
        Debug.Print shnm
        Set ssh = Nothing
        On Error Resume Next
        Set ssh = ThisWorkbook.Sheets("Master")
        On Error GoTo 0
        If Not ssh Is Nothing Then
            ssh.Copy after:=wb_daily.Sheets(1)
            ActiveSheet.Name = shnm
            'sheets are hidden
            
' -------- This was added May 29 to try and set up proper print areas -------
            Application.PrintCommunication = True
            ActiveSheet.PageSetup.PrintArea = ""
            Application.PrintCommunication = False
            With ActiveSheet.PageSetup
                .LeftHeader = ""
                .CenterHeader = ""
                .RightHeader = ""
                .LeftFooter = ""
                .CenterFooter = ""
                .RightFooter = ""
                .LeftMargin = 50.4
                .RightMargin = 50.4
                .TopMargin = A54
                .BottomMargin = 54
                .HeaderMargin = 21.6
                .FooterMargin = 21.6
                .PrintHeadings = False
                .PrintGridlines = False
                .PrintComments = xlPrintNoComments
                .CenterHorizontally = False
                .CenterVertically = False
                .Orientation = xlLandscape
                .Draft = False
                .PaperSize = xlPaperLetter
                .FirstPageNumber = xlAutomatic
                .Order = xlDownThenOver
                .BlackAndWhite = False
                .Zoom = 100
                .FitToPagesWide = 1
                .FitToPagesTall = 1
                .PrintErrors = 0
                .OddAndEvenPagesHeaderFooter = False
                .DifferentFirstPageHeaderFooter = False
                .ScaleWithDocHeaderFooter = True
                .AlignMarginsHeaderFooter = True
                .EvenPage.LeftHeader.Text = ""
                .EvenPage.CenterHeader.Text = ""
                .EvenPage.RightHeader.Text = ""
                .EvenPage.LeftFooter.Text = ""
                .EvenPage.CenterFooter.Text = ""
                .EvenPage.RightFooter.Text = ""
                .FirstPage.LeftHeader.Text = ""
                .FirstPage.CenterHeader.Text = ""
                .FirstPage.RightHeader.Text = ""
                .FirstPage.LeftFooter.Text = ""
                .FirstPage.CenterFooter.Text = ""
                .FirstPage.RightFooter.Text = ""
            End With
            Application.PrintCommunication = True
        End If
        ... ... ...
Next i

I don't get any errors when I execute the code, and all the pages are copied over. However, when I go to print, Excel crashes on the print preview. I have to CTRL-ALT-DEL to close Excel.