Copy Sheets to Blank Template

[Slicemahn]

Hi,

I am trying to copy the contents of a workbook to another workbook but I am finding that hidden sheets also get copied as well. So here is what I have:

Workbook 1 _Source:

Worksheet 1 _ Title Page
Worksheet 2_ Summary Trend
Worksheet 3_ Monthly Results
.
.
.
etc,

Workbook 2_Template:
This workbook contains the same visible sheets as Workbook 1 but all sheets are blank


Here is the code i am using:

Sub GetUpandGo()

Dim flDstName as String     ' This holds the file string of where the final copy is to be saved
Dim flTmpName as String    ' This holds the file string of the template file
Dim wkSrc as Workbook
Dim wkDst as Workbook
Dim wsSrc as Worksheet
Dim wsTot as Long
Dim wsCur as Long

Set wkSrc = ThisWorkbook

flDstName = wkSrc.Names("inFlName").RefersToRange.Value
flTmpName = wkSrc.Names("inTemplate").RefersToRange.Value

wsTot = wkSrc.Worksheets.Count -1

Workbooks.Open flTmpName

Set wkDst = ActiveWorkbook
Set wsDst = wkDst.Worksheets(1)

For wsCur = 1 to wsTot
             wsDst.copy   afteer:=wsDst
Next wsCur

For wsCur = 1 to wsTot + 1
        wkSrc.Worksheets(wsCur).Cells.Copy
        wkDst.Worksheets(wsCur).Cells.PasteSpecial Paste:=xlPasteValues
        wkDst.Worksheets(wsCur).Cells.PasteSpecial Paste:=xlPasteFormats
        wkDst.Worksheets(wsCur).Cells.PasteSpecial Paste:=xlPasteColumnWidths
        wkDst.Worksheets(wsCur).Cells.Name = wkSrc.Worksheets(wsCur).Name
        wkDst.Worksheets(wsCur).Range("A1").Select
Next wsCur

Application.DisplayAlerts = False

wkDst.Worksheets("RAW").Delete      ' This sheet holds query tables so I don't need this to be in the final report

Application.DisplayAlerts = True

wkDst.Worksheets(1).Select
wkDst.SaveAs flDstName
wkDst.Close

End Sub

When i execute this code I get data copied into the template only on the first page. I want the contents of SourceWorbook.Sheet1 pasted as values to TemplateWorkbook.Sheet1 and so on. Thanks.

[snb]

You can use savecopyas to copy a complete workbook.
Then you can remove all sheets you do not need in that copy.

[Slicemahn]

A good suggestion but it have a lot of array formulas that I don't want transferred to the new workbook. I want just the values copy and pasted and all formulas, query tables ("RAW") to be deleted.

[snb]

using

sheet1.usedrange.value=sheet1.usedrange.value

removes all formulae.

[Slicemahn]

okay that helps. Any idea why my code above doesn't work?