PDA

View Full Version : Solved: Copying Workbooks without disrupting the original



Slicemahn
07-20-2007, 01:24 AM
Hello Everyone,

I am currently trying to write some code that will allow me to copy a workbook but not save the original with the newly formatted worksheets. The original is to be used to obtain data from a web page and that is why saving over this workbook would disrupt things. Please let me know your thoughts.

Slicemahn
07-20-2007, 02:35 AM
Private Sub btCreate_Click()
Dim DstName As String
Dim TemplateName As String
Dim SrcWrbk As Workbook
Dim DestWrbk As Workbook
Dim SrcWksht As Worksheet
Dim DestWksht As Worksheet
Dim wsTot As Long
Dim wsCur As Long


Set SrcWrbk = ThisWorkbook

DstName = SrcWrbk.Names("Location").RefersToRange.Value
TemplateName = SrcWrbk.Names("Diectory").RefersToRange.Value
wsTot = SrcWrbk.Worksheets.Count - 1

Workbooks.Open TemplateName
Set DestWrbk = ActiveWorkbook
Set DestWksht = DestWrbk.Worksheets(1)

For wsCur = 1 To wsTot
DestWksht.Copy after:=DestWksht
Next wsCur

For wsCur = 1 To wsTot + 1
SrcWrbk.Worksheets(wsCur).Cells.Copy
DestWrbk.Worksheets(wsCur).Cells.PasteSpecial Paste:=xlPasteValues
DestWrbk.Worksheets(wsCur).Cells.PasteSpecial Paste:=xlPasteFormats
DestWrbk.Worksheets(wsCur).Cells.PasteSpecial Paste:=xlPasteColumnWidths
DestWrbk.Worksheets(wsCur).Name = SrcWrbk.Worksheets(wsCur).Name
DestWrbk.Worksheets(wsCur).Activate
DestWrbk.Worksheets(wsCur).Range("A1").Select
Next wsCur

Application.DisplayAlerts = False
DestWrbk.Worksheets("Title").Delete
Application.DisplayAlerts = True

DestWrbk.Worksheets(1).Select
DestWrbk.SaveAs DstName
DestWrbk.Close
End Sub


I solved my own problem..yaaay!