Keep template margins setting when saving

SaraO
Dec 23rd 2016
Thread is marked as Resolved.

Dec 23rd 2016

#1

Hello,

I have a code that saves a copy of a template in several files. Its work really well but it doesn't keep the margin setting on the saved document. When we have to go in and reprint that document we always have to reset the margins or it will print on multiple pages.

How can this code be modified to save margins as they are set in the template?

Code

Public Sub Save_Receipt()


   Application.ScreenUpdating = False
   GetReceiptNumberRun
   SendReceiptToMaster
   Dim SaveAsFileName   As Variant
   Dim newWorkbook      As Workbook


   SaveAsFileName = "C:\Users\Administrator\Desktop\Shared\Receipts\" & Range("I10").Value & ".xlsx"
  
   Set newWorkbook = Workbooks.Add(XlWBATemplate.xlWBATWorksheet)


   ThisWorkbook.Worksheets("Receipt").Copy Before:=newWorkbook.Worksheets(1)


   'Tidy up copied Invoice sheet
   With newWorkbook.Worksheets(1)
      'Convert formulas to their values
      .Range("I11").Value = .Range("I11").Value       'Date
      .Range("J16:J48").Value = .Range("J16:J48").Value   'Totals
      .Range("H41:H46").Value = .Range("H41:H46").Value   'Hiden formulas
      .Range("B1").Value = .Range("B1").Value         'Hiden Formula
      'Delete data validation dropdowns
      .Range("B10").Validation.Delete
      .Range("I12").Validation.Delete
      .Range("F42").Validation.Delete
      'Delete unwanted columns, including hidden lookup table
      .Columns("K:AZ").Delete
   End With


   'Suppress all Excel warnings: deleting sheet; saving as macro-free workbook; file already exists
   Application.DisplayAlerts = False


   'Delete empty sheet
   newWorkbook.Sheets(2).Delete


   'Save new workbook in xlsx format
   newWorkbook.SaveAs SaveAsFileName, xlOpenXMLWorkbook
   newWorkbook.Close False


   PrintReceipt


   'Clear Receipt
   Clear_Receipt


   'Reenable Excel warnings
   Application.DisplayAlerts = True
   Application.ScreenUpdating = True


End Sub

Display More

Dec 23rd 2016

#2

Re: Keep template margins setting when saving

Try this

Code

Public Sub Save_Receipt()
     
    Application.ScreenUpdating = False
    GetReceiptNumberRun
    SendReceiptToMaster
    Dim SaveAsFileName   As String
    Dim newWorkbook      As Workbook
    Dim dMTop As Double, dMBot As Double, dMLft As Double, dMRgt As Double
        
    SaveAsFileName = "C:\Users\Administrator\Desktop\Shared\Receipts\" & Range("I10").Value & ".xlsx"
     
    Set newWorkbook = Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
     
    With ThisWorkbook.Sheets("Receipt")
        dMTop = .TopMargin
        dMBot = .BottomMargin
        dMLft = .LeftMargin
        dMRgt = .RightMargin
        .Copy Before:=newWorkbook.Worksheets(1)
    End With
     
     'Tidy up copied Invoice sheet
    With newWorkbook.Sheets(1)
         'Convert formulas to their values
        .Range("I11").Value = .Range("I11").Value 'Date
        .Range("J16:J48").Value = .Range("J16:J48").Value 'Totals
        .Range("H41:H46").Value = .Range("H41:H46").Value 'Hiden formulas
        .Range("B1").Value = .Range("B1").Value 'Hiden Formula
         'Delete data validation dropdowns
        .Range("B10").Validation.Delete
        .Range("I12").Validation.Delete
        .Range("F42").Validation.Delete
         'Delete unwanted columns, including hidden lookup table
        .Columns("K:AZ").Delete
         'Set the margins
        .TopMargin = dMTop: .BottomMargin = dMBot: .LeftMargin = dMLft: .RightMargin = dMRgt
     
         'Suppress all Excel warnings: deleting sheet; saving as macro-free workbook; file already exists
        Application.DisplayAlerts = False
        .Sheets(2).Delete
        Application.DisplayAlerts = True
         'Save new workbook in xlsx format
        .Parent.SaveAs SaveAsFileName, xlOpenXMLWorkbook
        .Parent.Close False
    End With
    
    PrintReceipt
     
     'Clear Receipt
    Clear_Receipt
     
End Sub

Display More

SaraO

Student

Points

555

Posts

71
Dec 23rd 2016
- #3
Re: Keep template margins setting when saving

Thank you very much! I will give it a try.
KjBox

OzMVP - Super Moderator

Reactions Received

243

Points

24,923

Posts

4,873

Operating System

Windows (x64)

Excel Version

Office 365
Dec 23rd 2016
- #4
Re: Keep template margins setting when saving

I modified the code just before your last post (code had a typo), make sure you try the modified code!
We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.
SaraO

Student

Points

555

Posts

71
Dec 23rd 2016
- #5
Re: Keep template margins setting when saving

I'm getting and error message "Object doesn't support this property or method" and highlights this row...

Code

dMTop = .TopMargin
SaraO

Student

Points

555

Posts

71
Dec 23rd 2016
- #6
Re: Keep template margins setting when saving

It still gives me the same error on the same line.

Dec 23rd 2016

#7

Re: Keep template margins setting when saving

Sorry, my mistake, change the code to this

Code

Public Sub Save_Receipt()
     
    Application.ScreenUpdating = False
    GetReceiptNumberRun
    SendReceiptToMaster
    Dim SaveAsFileName   As String
    Dim newWorkbook      As Workbook
    Dim dMTop As Double, dMBot As Double, dMLft As Double, dMRgt As Double
     
    SaveAsFileName = "C:\Users\Administrator\Desktop\Shared\Receipts" & Range("I10").Value & ".xlsx"
     
    Set newWorkbook = Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
     
    With ThisWorkbook.Sheets("Receipt").PageSetup
        dMTop = .TopMargin
        dMBot = .BottomMargin
        dMLft = .LeftMargin
        dMRgt = .RightMargin
        .Parent.Copy Before:=newWorkbook.Worksheets(1)
    End With
     
     'Tidy up copied Invoice sheet
    With newWorkbook.Sheets(1)
         'Convert formulas to their values
        .Range("I11").Value = .Range("I11").Value 'Date
        .Range("J16:J48").Value = .Range("J16:J48").Value 'Totals
        .Range("H41:H46").Value = .Range("H41:H46").Value 'Hiden formulas
        .Range("B1").Value = .Range("B1").Value 'Hiden Formula
         'Delete data validation dropdowns
        .Range("B10").Validation.Delete
        .Range("I12").Validation.Delete
        .Range("F42").Validation.Delete
         'Delete unwanted columns, including hidden lookup table
        .Columns("K:AZ").Delete
         'Set the margins
         With .PageSetup
            .TopMargin = dMTop: .BottomMargin = dMBot: .LeftMargin = dMLft: .RightMargin = dMRgt
         End With
         'Suppress all Excel warnings: deleting sheet; saving as macro-free workbook; file already exists
        Application.DisplayAlerts = False
        .Sheets(2).Delete
        Application.DisplayAlerts = True
         'Save new workbook in xlsx format
        .Parent.SaveAs SaveAsFileName, xlOpenXMLWorkbook
        .Parent.Close False
    End With
     
    PrintReceipt
     
     'Clear Receipt
    Clear_Receipt
     
End Sub

Display More

SaraO

Student

Points

555

Posts

71
Dec 28th 2016
- #8
Re: Keep template margins setting when saving

Good morning,

I finally got a chance to test the code and it is still not saving it with the original margins. It still shows up on two pages when I open the saved document.

Dec 29th 2016

#9

Re: Keep template margins setting when saving

It could be because the Paper Size that the Template Page Set-up is set to is different from the default Paper Size. Try this

Code

Public Sub Save_Receipt()
     
    Application.ScreenUpdating = False
    GetReceiptNumberRun
    SendReceiptToMaster
    Dim SaveAsFileName   As String
    Dim newWorkbook      As Workbook
    Dim PapSize, dMTop As Double, dMBot As Double, dMLft As Double, dMRgt As Double
     
    SaveAsFileName = "C:\Users\Administrator\Desktop\Shared\Receipts" & Range("I10").Value & ".xlsx"
     
    Set newWorkbook = Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
     
    With ThisWorkbook.Sheets("Receipt").PageSetup
        dMTop = .TopMargin
        dMBot = .BottomMargin
        dMLft = .LeftMargin
        dMRgt = .RightMargin
        PapSize = .XlPaperSize
        .Parent.Copy Before:=newWorkbook.Worksheets(1)
    End With
     
     'Tidy up copied Invoice sheet
    With newWorkbook.Sheets(1)
         'Convert formulas to their values
        .Range("I11").Value = .Range("I11").Value 'Date
        .Range("J16:J48").Value = .Range("J16:J48").Value 'Totals
        .Range("H41:H46").Value = .Range("H41:H46").Value 'Hiden formulas
        .Range("B1").Value = .Range("B1").Value 'Hiden Formula
         'Delete data validation dropdowns
        .Range("B10").Validation.Delete
        .Range("I12").Validation.Delete
        .Range("F42").Validation.Delete
         'Delete unwanted columns, including hidden lookup table
        .Columns("K:AZ").Delete
         'Set the margins and paper size
        With .PageSetup
            .TopMargin = dMTop: .BottomMargin = dMBot: .LeftMargin = dMLft: .RightMargin = dMRgt
            .PaperSize = PapSize
        End With
         'Suppress all Excel warnings: deleting sheet; saving as macro-free workbook; file already exists
        Application.DisplayAlerts = False
        .Sheets(2).Delete
        Application.DisplayAlerts = True
         'Save new workbook in xlsx format
        .Parent.SaveAs SaveAsFileName, xlOpenXMLWorkbook
        .Parent.Close False
    End With
     
    PrintReceipt
     
     'Clear Receipt
    Clear_Receipt
     
End Sub

Display More

SaraO

Student

Points

555

Posts

71
Dec 29th 2016
- #10
Re: Keep template margins setting when saving

Good morning KjBox,

It's giving me an error on the following line...

Code

PapSize = .XlPaperSize

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login

Share

This site uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close

Your browser has JavaScript disabled. If you would like to use all features of this site, it is mandatory to enable JavaScript.