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.
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.