PDA

View Full Version : Copy Sheets to Blank Template



Slicemahn
11-18-2013, 10:15 PM
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
11-19-2013, 01:55 AM
You can use savecopyas to copy a complete workbook.
Then you can remove all sheets you do not need in that copy.

Slicemahn
11-19-2013, 06:14 AM
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
11-19-2013, 07:29 AM
using
sheet1.usedrange.value=sheet1.usedrange.value removes all formulae.

Slicemahn
11-19-2013, 03:33 PM
okay that helps. Any idea why my code above doesn't work?