Copy print settings from one workbook to another

[vrunda]

How can one copy print settings from one workbook to another -both workbooks have same sheet names but ofcourse different contents. Any excel command or vba code will be appreciated. I tried clicking ctrl and selecting page setup then ok but did not work.

Thanks

 

[PCosta87]

Hi vrunda,

Maybe something like this (both workbooks must be open):

Sub CopyPrintSettings()

Dim ws As Worksheet
Dim workbook As String

workbook = InputBox("Please provide workbook name (extension included)", "Copy print settings from Workbook")

If workbook = vbNullString Then Exit Sub Else

On Error GoTo msg

For Each ws In ActiveWorkbook.Sheets

    With ws.PageSetup
        .PrintArea = Workbooks(workbook).Sheets(ws.Name).PageSetup.PrintArea
        .LeftHeader = Workbooks(workbook).Sheets(ws.Name).PageSetup.LeftHeader
        .CenterHeader = Workbooks(workbook).Sheets(ws.Name).PageSetup.LeftHeader
        .RightHeader = Workbooks(workbook).Sheets(ws.Name).PageSetup.LeftHeader
        .LeftFooter = Workbooks(workbook).Sheets(ws.Name).PageSetup.LeftFooter
        .CenterFooter = Workbooks(workbook).Sheets(ws.Name).PageSetup.LeftFooter
        .RightFooter = Workbooks(workbook).Sheets(ws.Name).PageSetup.LeftFooter
        .LeftMargin = Workbooks(workbook).Sheets(ws.Name).PageSetup.LeftMargin
        .RightMargin = Workbooks(workbook).Sheets(ws.Name).PageSetup.RightMargin
        .TopMargin = Workbooks(workbook).Sheets(ws.Name).PageSetup.TopMargin
        .BottomMargin = Workbooks(workbook).Sheets(ws.Name).PageSetup.BottomMargin
        .HeaderMargin = Workbooks(workbook).Sheets(ws.Name).PageSetup.HeaderMargin
        .FooterMargin = Workbooks(workbook).Sheets(ws.Name).PageSetup.FooterMargin
        .PrintHeadings = Workbooks(workbook).Sheets(ws.Name).PageSetup.PrintHeadings
        .PrintGridlines = Workbooks(workbook).Sheets(ws.Name).PageSetup.PrintGridlines
        .PrintComments = Workbooks(workbook).Sheets(ws.Name).PageSetup.PrintComments
        .CenterHorizontally = Workbooks(workbook).Sheets(ws.Name).PageSetup.CenterHorizontally
        .CenterVertically = Workbooks(workbook).Sheets(ws.Name).PageSetup.CenterVertically
        .Orientation = Workbooks(workbook).Sheets(ws.Name).PageSetup.Orientation
        .Draft = Workbooks(workbook).Sheets(ws.Name).PageSetup.Draft
        .PaperSize = Workbooks(workbook).Sheets(ws.Name).PageSetup.PaperSize
        .FirstPageNumber = Workbooks(workbook).Sheets(ws.Name).PageSetup.FirstPageNumber
        .Order = Workbooks(workbook).Sheets(ws.Name).PageSetup.Order
        .BlackAndWhite = Workbooks(workbook).Sheets(ws.Name).PageSetup.BlackAndWhite
        .Zoom = Workbooks(workbook).Sheets(ws.Name).PageSetup.Zoom
        .FitToPagesWide = Workbooks(workbook).Sheets(ws.Name).PageSetup.FitToPagesWide
        .FitToPagesTall = Workbooks(workbook).Sheets(ws.Name).PageSetup.FitToPagesTall
        .PrintErrors = Workbooks(workbook).Sheets(ws.Name).PageSetup.PrintErrors
      
        .OddAndEvenPagesHeaderFooter = Workbooks(workbook).Sheets(ws.Name).PageSetup.OddAndEvenPagesHeaderFooter
        .DifferentFirstPageHeaderFooter = Workbooks(workbook).Sheets(ws.Name).PageSetup.DifferentFirstPageHeaderFooter
        .ScaleWithDocHeaderFooter = Workbooks(workbook).Sheets(ws.Name).PageSetup.ScaleWithDocHeaderFooter
        .AlignMarginsHeaderFooter = Workbooks(workbook).Sheets(ws.Name).PageSetup.AlignMarginsHeaderFooter
      
        .EvenPage.LeftHeader.Text = Workbooks(workbook).Sheets(ws.Name).PageSetup.EvenPage.LeftHeader.Text
        .EvenPage.CenterHeader.Text = Workbooks(workbook).Sheets(ws.Name).PageSetup.EvenPage.CenterHeader.Text
        .EvenPage.RightHeader.Text = Workbooks(workbook).Sheets(ws.Name).PageSetup.EvenPage.RightHeader.Text
        .EvenPage.LeftFooter.Text = Workbooks(workbook).Sheets(ws.Name).PageSetup.EvenPage.LeftFooter.Text
        .EvenPage.CenterFooter.Text = Workbooks(workbook).Sheets(ws.Name).PageSetup.EvenPage.CenterFooter.Text
        .EvenPage.RightFooter.Text = Workbooks(workbook).Sheets(ws.Name).PageSetup.EvenPage.RightFooter.Text
      
        .FirstPage.LeftHeader.Text = Workbooks(workbook).Sheets(ws.Name).PageSetup.FirstPage.LeftHeader.Text
        .FirstPage.CenterHeader.Text = Workbooks(workbook).Sheets(ws.Name).PageSetup.FirstPage.CenterHeader.Text
        .FirstPage.RightHeader.Text = Workbooks(workbook).Sheets(ws.Name).PageSetup.FirstPage.RightHeader.Text
        .FirstPage.LeftFooter.Text = Workbooks(workbook).Sheets(ws.Name).PageSetup.FirstPage.LeftFooter.Text
        .FirstPage.CenterFooter.Text = Workbooks(workbook).Sheets(ws.Name).PageSetup.FirstPage.CenterFooter.Text
        .FirstPage.RightFooter.Text = Workbooks(workbook).Sheets(ws.Name).PageSetup.FirstPage.RightFooter.Text
    End With
  
Next ws

Exit Sub

msg: MsgBox "Workbook not found! Please verify workbook name and extension.", vbExclamation

End Sub

Let us know if it works

Regards

 

[vrunda]

It worked for me . I just appreciate your efforts. Sorry for replying late as I wasn't online. Thank you so much

 

[PCosta87]

It worked for me . I just appreciate your efforts. Sorry for replying late as I wasn't online. Thank you so much

You're welcome