Save a sheet in the same workbook but with same print settings.

Jo4x4 · Dec 23, 2010

Hi,

I have a sheet with a button on that I use to copy the sheet with a different name in the same workbook. However, the copy doesn't have the same print setup as the original. I know I can press ctrl and save page setup, but this is slow, as there are many copies. Also, someone might forget to do a sheet, and it will then print incorrectly.

Seasons greetings from sunny South Africa!

Thanks

Jo

Jo4x4 · Jan 3, 2011

Hi everybody. Hope you had a wonderful festive season.

Can somebody please help with some code to copy print settings as well?

Thanks

Jo

Hui · Jan 3, 2011

Jo4x4

How about adding the following code to your macro

This was written in Excel 2007

Some of the properties may not be available in earlier versions of Excel

If that is your case run it and when it crashes delete the line with the property that may not be suitable for your version.

[pre]

Code:

Sub Copy_Print_Settings()

Dim MyWrkBook As String
Dim NewSht As String
Dim OldSht As String

MyWrkBook = &#34;Copy worksheet.xlsm&#34;  &#39;Change to suit
NewSht = &#34;Sheet2&#34;  &#39;Change to suit
OldSht = &#34;Sheet1&#34;  &#39;Change to suit

Worksheets(NewSht).Select
With ActiveSheet.PageSetup
.LeftHeader = Worksheets(NewSht).PageSetup.LeftHeader
.CenterHeader = Worksheets(OldSht).PageSetup.CenterHeader
.RightHeader = Worksheets(OldSht).PageSetup.RightHeader
.LeftFooter = Worksheets(OldSht).PageSetup.LeftFooter
.CenterFooter = Worksheets(OldSht).PageSetup.CenterFooter
.RightFooter = Worksheets(OldSht).PageSetup.RightFooter
.LeftMargin = Worksheets(OldSht).PageSetup.LeftMargin
.RightMargin = Worksheets(OldSht).PageSetup.RightMargin
.TopMargin = Worksheets(OldSht).PageSetup.TopMargin
.BottomMargin = Worksheets(OldSht).PageSetup.BottomMargin
.HeaderMargin = Worksheets(OldSht).PageSetup.HeaderMargin
.FooterMargin = Worksheets(OldSht).PageSetup.FooterMargin
.PrintHeadings = Worksheets(OldSht).PageSetup.PrintHeadings
.PrintGridlines = Worksheets(OldSht).PageSetup.PrintGridlines
.PrintComments = Worksheets(OldSht).PageSetup.PrintComments
.PrintQuality = Worksheets(OldSht).PageSetup.PrintQuality
.CenterHorizontally = Worksheets(OldSht).PageSetup.CenterHorizontally
.CenterVertically = Worksheets(OldSht).PageSetup.CenterVertically
.Orientation = Worksheets(OldSht).PageSetup.Orientation
.Draft = Worksheets(OldSht).PageSetup.Draft
.PaperSize = Worksheets(OldSht).PageSetup.PaperSize
.FirstPageNumber = Worksheets(OldSht).PageSetup.FirstPageNumber
.Order = Worksheets(OldSht).PageSetup.Order
.BlackAndWhite = Worksheets(OldSht).PageSetup.BlackAndWhite
.Zoom = Worksheets(OldSht).PageSetup.Zoom
.FitToPagesWide = Worksheets(OldSht).PageSetup.FitToPagesWide
.FitToPagesTall = Worksheets(OldSht).PageSetup.FitToPagesTall
.PrintErrors = Worksheets(OldSht).PageSetup.PrintErrors

.OddAndEvenPagesHeaderFooter = Worksheets(OldSht).PageSetup.OddAndEvenPagesHeaderFooter
.DifferentFirstPageHeaderFooter = Worksheets(OldSht).PageSetup.DifferentFirstPageHeaderFooter
.ScaleWithDocHeaderFooter = Worksheets(OldSht).PageSetup.ScaleWithDocHeaderFooter
.AlignMarginsHeaderFooter = Worksheets(OldSht).PageSetup.AlignMarginsHeaderFooter

.EvenPage.LeftHeader.Text = Worksheets(OldSht).PageSetup.EvenPage.LeftHeader.Text
.EvenPage.CenterHeader.Text = Worksheets(OldSht).PageSetup.EvenPage.CenterHeader.Text
.EvenPage.RightHeader.Text = Worksheets(OldSht).PageSetup.EvenPage.RightHeader.Text
.EvenPage.LeftFooter.Text = Worksheets(OldSht).PageSetup.EvenPage.LeftFooter.Text
.EvenPage.CenterFooter.Text = Worksheets(OldSht).PageSetup.EvenPage.CenterFooter.Text
.EvenPage.RightFooter.Text = Worksheets(OldSht).PageSetup.EvenPage.RightFooter.Text

.FirstPage.LeftHeader.Text = Worksheets(OldSht).PageSetup.FirstPage.LeftHeader.Text
.FirstPage.CenterHeader.Text = Worksheets(OldSht).PageSetup.FirstPage.CenterHeader.Text
.FirstPage.RightHeader.Text = Worksheets(OldSht).PageSetup.FirstPage.RightHeader.Text
.FirstPage.LeftFooter.Text = Worksheets(OldSht).PageSetup.FirstPage.LeftFooter.Text
.FirstPage.CenterFooter.Text = Worksheets(OldSht).PageSetup.FirstPage.CenterFooter.Text
.FirstPage.RightFooter.Text = Worksheets(OldSht).PageSetup.FirstPage.RightFooter.Text

End With
End Sub

[/pre]

Jo4x4 · Jan 4, 2011

Hi Hui,

No, I am way too stupid for this... You helped me with a VLOOKUP that changes my "master sheet" with information stored elsewhere in the workbook. A button then saves this new information as a new sheet, and renames it.

That is where my problem is with your print settings. I don't know how to change the "old sheet" and "new sheet" names.

Here is the code used for the button:

Private Sub Saveandreturn_Click()

Dim sh As Worksheet

Application.ScreenUpdating = False

Application.DisplayAlerts = False

With Me

On Error Resume Next

.Parent.Worksheets(.Range("D4").Value).Delete

On Error GoTo 0

Set sh = .Parent.Worksheets.Add(After:=.Parent.Worksheets(.Parent.Worksheets.Count))

sh.Name = .Range("D4").Value

.Cells.Copy sh.Range("A1")

sh.UsedRange.Value = sh.UsedRange.Value

End With

Me.Activate

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub

Thanks again for all your assistance.

Jo

Hui · Jan 4, 2011

Jo4x4

I have made a few smakll changes to your macro and also changes my previous macro

The changes in your macro extract the old and new sheet names and then calls the new macro to copy the settings

Replace your existing code with this

The 3 changes are commented

[pre]

Code:

Sub Saveandreturn_Click()
Dim sh As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

With Me
OldSht = Me.Name &#39;This is the existing sheet name

On Error Resume Next
.Parent.Worksheets(.Range(&#34;D4&#34;).Value).Delete
On Error GoTo 0
Set sh = .Parent.Worksheets.Add(After:=.Parent.Worksheets(.Parent.Worksheets.Count))
sh.Name = .Range(&#34;D4&#34;).Value
.Cells.Copy sh.Range(&#34;A1&#34;)
sh.UsedRange.Value = sh.UsedRange.Value

NewSht = .Range(&#34;D4&#34;).Value &#39;This is the New sheet name

Call Copy_Print_Settings(OldSht, NewSht) &#39;Call subroutine to copy print settings

End With

Me.Activate

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

[/pre]

and Copy the following to a code module

Sub Copy_Print_Settings(OldSht, NewSht)

Dim MyWrkBook As String

Worksheets(NewSht).Select

With ActiveSheet.PageSetup
.PrintArea = Worksheets(OldSht).PageSetup.PrintArea
.LeftHeader = Worksheets(OldSht).PageSetup.LeftHeader
.CenterHeader = Worksheets(OldSht).PageSetup.CenterHeader
.RightHeader = Worksheets(OldSht).PageSetup.RightHeader
.LeftFooter = Worksheets(OldSht).PageSetup.LeftFooter
.CenterFooter = Worksheets(OldSht).PageSetup.CenterFooter
.RightFooter = Worksheets(OldSht).PageSetup.RightFooter
.LeftMargin = Worksheets(OldSht).PageSetup.LeftMargin
.RightMargin = Worksheets(OldSht).PageSetup.RightMargin
.TopMargin = Worksheets(OldSht).PageSetup.TopMargin
.BottomMargin = Worksheets(OldSht).PageSetup.BottomMargin
.HeaderMargin = Worksheets(OldSht).PageSetup.HeaderMargin
.FooterMargin = Worksheets(OldSht).PageSetup.FooterMargin
.PrintHeadings = Worksheets(OldSht).PageSetup.PrintHeadings
.PrintGridlines = Worksheets(OldSht).PageSetup.PrintGridlines
.PrintComments = Worksheets(OldSht).PageSetup.PrintComments
.PrintQuality = Worksheets(OldSht).PageSetup.PrintQuality
.CenterHorizontally = Worksheets(OldSht).PageSetup.CenterHorizontally
.CenterVertically = Worksheets(OldSht).PageSetup.CenterVertically
.Orientation = Worksheets(OldSht).PageSetup.Orientation
.Draft = Worksheets(OldSht).PageSetup.Draft
.PaperSize = Worksheets(OldSht).PageSetup.PaperSize
.FirstPageNumber = Worksheets(OldSht).PageSetup.FirstPageNumber
.Order = Worksheets(OldSht).PageSetup.Order
.BlackAndWhite = Worksheets(OldSht).PageSetup.BlackAndWhite
.Zoom = Worksheets(OldSht).PageSetup.Zoom
.FitToPagesWide = Worksheets(OldSht).PageSetup.FitToPagesWide
.FitToPagesTall = Worksheets(OldSht).PageSetup.FitToPagesTall
.PrintErrors = Worksheets(OldSht).PageSetup.PrintErrors

.OddAndEvenPagesHeaderFooter = Worksheets(OldSht).PageSetup.OddAndEvenPagesHeaderFooter
.DifferentFirstPageHeaderFooter = Worksheets(OldSht).PageSetup.DifferentFirstPageHeaderFooter
.ScaleWithDocHeaderFooter = Worksheets(OldSht).PageSetup.ScaleWithDocHeaderFooter
.AlignMarginsHeaderFooter = Worksheets(OldSht).PageSetup.AlignMarginsHeaderFooter

.EvenPage.LeftHeader.Text = Worksheets(OldSht).PageSetup.EvenPage.LeftHeader.Text
.EvenPage.CenterHeader.Text = Worksheets(OldSht).PageSetup.EvenPage.CenterHeader.Text
.EvenPage.RightHeader.Text = Worksheets(OldSht).PageSetup.EvenPage.RightHeader.Text
.EvenPage.LeftFooter.Text = Worksheets(OldSht).PageSetup.EvenPage.LeftFooter.Text
.EvenPage.CenterFooter.Text = Worksheets(OldSht).PageSetup.EvenPage.CenterFooter.Text
.EvenPage.RightFooter.Text = Worksheets(OldSht).PageSetup.EvenPage.RightFooter.Text

.FirstPage.LeftHeader.Text = Worksheets(OldSht).PageSetup.FirstPage.LeftHeader.Text
.FirstPage.CenterHeader.Text = Worksheets(OldSht).PageSetup.FirstPage.CenterHeader.Text
.FirstPage.RightHeader.Text = Worksheets(OldSht).PageSetup.FirstPage.RightHeader.Text
.FirstPage.LeftFooter.Text = Worksheets(OldSht).PageSetup.FirstPage.LeftFooter.Text
.FirstPage.CenterFooter.Text = Worksheets(OldSht).PageSetup.FirstPage.CenterFooter.Text
.FirstPage.RightFooter.Text = Worksheets(OldSht).PageSetup.FirstPage.RightFooter.Text

End With
End Sub

Works well for me in Excel 2007.

Jo4x4 · Jan 4, 2011

Thanks Hui, you are the man! Sorry Ninja

Jo

Jo4x4 · Jan 5, 2011

Hi Hui,

I need your help again if you can.

I have uploaded a sample here: http://rapidshare.com/files/440880304/sample.xlsx

On the Electronic score sheet, I will add a list so that the operator can just choose the team, and the driver and nav name will pop up. That is not the problem.

What I need, if possible, is a button to print this sheet, and then save the results to the sheet named "Results".

Not a necessity, but it would be great if the "results" sheet can auto-sort as the team scores are added. There are about 60 teams.

BTW, where in the world are you?

Thanks again

Jo

Hui · Jan 5, 2011

This is where I am

http://chandoo.org/wp/about-hui/

Jo4x4 · Jan 5, 2011

Hi Hui,

Sorry to be a pain... Can you help please?

Thanks

Jo

Hui · Jan 6, 2011

Jo

Have a look at

http://rapidshare.com/files/441027120/sample.xlsm

Jo4x4 · Jan 6, 2011

Hi Hui,

Thanks for all your help. It works great, except when there is not yet a team in. From the second team it works, but if you clear the values, it then tries to put the first team name at the bottom of the sheet.

Thanks

Jo

Jo4x4 · Jan 6, 2011

Hi Hui, don't worry about it. I simply put a "fake" team at the top, and then it works fine.

Thanks again

Jo

Hui · Jan 6, 2011

Once you've done one

Move it up and replace the first row

Jo4x4 · Jan 7, 2011

Hi Hui, thanks again for your assistance!

You have mail...

Jo

Save a sheet in the same workbook but with same print settings.

Jo4x4

New Member

Jo4x4

New Member

Hui

Excel Ninja

Jo4x4

New Member

Hui

Excel Ninja

Jo4x4

New Member

Jo4x4

New Member

Hui

Excel Ninja

Jo4x4

New Member

Hui

Excel Ninja

Jo4x4

New Member

Jo4x4

New Member

Hui

Excel Ninja

Jo4x4

New Member