Copy, paste & save worksheet many times

Hi All, long time no chat, delighted to be back and thanks for being there.
I have a macro (recorded in stages) that copies a worksheet from one WB (name “Test Module” ) into another workbook (name “Test Summary”).
I need to copy it a minimum of 4 times (possibly 6) and save each copy on a different sheet in "Test Summary".
Can you advise what additional code I need and where do I place it. Also I’d like to rename the new sheets in "Test Summary" to show the year e.g. 2015_1, 2015_2 etc.
Here’s my code It’s very long (and likely very inefficient). Hope that makes sense and Thanks again.


Sub CopySave_test()
'
' CopySave_test Macro
' Macro recorded 14-07-2015 by Me3
'

' From Original Workbook "Test Module”with Summary sheet open, unprotect & Copy
ActiveSheet.Unprotect
Range("A1:H39").Select
Selection.Copy

'open specific workbook "Test_Summary" and paste copied info into sheet1, then copy sheet1 in new workbook & paste special, pasteValues. (to remove links)
ChDir "C:\Users\Me3\Documents\John\Bernadette"
Workbooks.Open Filename:= _
"C:\Users\Me3\Documents\John\ Bernadette\Test_Summary.xls"
Range("A1").Select
ActiveSheet.Paste
Range("A1:H39").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
‘Set column widths
Range("B1:G1").Select
Selection.ColumnWidth = 14
Range("A1").Select
Selection.ColumnWidth = 30

'set page to Landscape
With ActiveSheet.pagesetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.pagesetup.PrintArea = "$A$1:$H$39"

With ActiveSheet.pagesetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.pagesetup.PrintArea = "$A$1:$H$39"
With ActiveSheet.pagesetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.590551181102362)
.RightMargin = Application.InchesToPoints(0.590551181102362)
.TopMargin = Application.InchesToPoints(0.393700787401575)
.BottomMargin = Application.InchesToPoints(0.511811023622047)
.HeaderMargin = Application.InchesToPoints(0.393700787401575)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 85
.PrintErrors = xlPrintErrorsDisplayed
End With

'Still in Test_Summary WB, view "pagebreak preview" and set parameters for one page print
With ActiveSheet.Select
ActiveWindow.View = xlPageBreakPreview
Application.CutCopyMode = False
ActiveSheet.pagesetup.PrintArea = "$A$1:$H$39"
ActiveWindow.View = xlNormalView
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End With
With ActiveWorkbook.ActiveSheet
ActiveWorkbook.Save
ActiveWindow.Close

'Original Workbook is now active
Range("A1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With

End Sub

Firstly, please use the

Please Login or Register  to view this content.