How to Update only Sheet1 of a workbook template

[therempels]

I have this code to copy data from a range in one workbook to Sheet1 of a specific template. ( The data from Sheet1 then populates a second sheet in the template file.) Each file is created and named for the names in the dynamic named range “names1”.

This seems to work perfectly, but I need it to do two other things:

Firstly, I need it to check and see if a file has already been created with the filename, and if so, not to overwrite it, or prompt for saving.

Secondly, and most importantly, I need to find a way to have it check for an existing file, and then ONLY overwrite Sheet1 with the info from above, WITHOUT changing anything on any of the other sheets in the file, and then save and close the file. And then continue checking all the other names in the file, and either creating a new file from the template (as my code already does) OR updating only sheet1 and saving/closing the file.

I have searched for help on this, but I am new to VBA, I’m not sure where to put the add-ins and what syntax to use. Any help would be greatly appreciated!!!

 

[therempels]

Here is my working code:

Sub Smart1()


Dim src As Workbook

Dim dst As Workbook

SavePath = ActiveWorkbook.Path


Set src = ActiveWorkbook


For Each C In Range("Names1")


i = C.Row


Name = Cells(i, 44).Value

PSFFAll = Cells(i, 45).Value

CLSFall = Cells(i, 46).Value

CLSWin = Cells(i, 47).Value

CLSEnd = Cells(i, 48).Value

WWRFall = Cells(i, 49).Value

WWRWin = Cells(i, 50).Value

WWREnd = Cells(i, 51).Value

DORFWin = Cells(i, 52).Value

DORFEnd = Cells(i, 53).Value

AccWin = Cells(i, 54).Value

AccEnd = Cells(i, 55).Value


fname = Cells(i, 44).Value & ".xlsx"


Workbooks.Open FileName:=ThisWorkbook.Path & "Smart1.xlsx"


With Workbooks("Smart1.xlsx").Worksheets("Sheet1")

.Range("a2").Value = Name

.Range("B2").Value = PSFFAll

.Range("C2").Value = CLSFall

.Range("D2").Value = CLSWin

.Range("E2").Value = CLSEnd

.Range("F2").Value = WWRFall

.Range("G2").Value = WWRWin

.Range("H2").Value = WWREnd

.Range("I2").Value = DORFWin

.Range("J2").Value = DORFEnd

.Range("K2").Value = AccWin

.Range("L2").Value = AccEnd

End With


ActiveWorkbook.saveas FileName:=SavePath & "" & fname

ActiveWorkbook.Close True

On Error Resume Next


Next C


End Sub

 

[therempels]

Thanks to Tweedle! Here is the answer!

Sub Smart1()

Dim src As Workbook

Dim dst As Workbook

SavePath = ActiveWorkbook.Path


Set src = ActiveWorkbook


For Each C In Range("Names1")


i = C.Row


Name = Cells(i, 44).Value

PSFFAll = Cells(i, 45).Value

CLSFall = Cells(i, 46).Value

CLSWin = Cells(i, 47).Value

CLSEnd = Cells(i, 48).Value

WWRFall = Cells(i, 49).Value

WWRWin = Cells(i, 50).Value

WWREnd = Cells(i, 51).Value

DORFWin = Cells(i, 52).Value

DORFEnd = Cells(i, 53).Value

AccWin = Cells(i, 54).Value

AccEnd = Cells(i, 55).Value


fname = Cells(i, 44).Value & ".xlsx"


If Dir(SavePath & "" & fname) = "" Then

'Filename does not exist, then use template

Set dst = Workbooks.Open(Filename:=ThisWorkbook.Path & "Smart1.xlsx")

Else

'File already exists, then use existing & update

Set dst = Workbooks.Open(Filename:=SavePath & "" & fname)

End If


With dst.Worksheets("Sheet1")

.Range("a2").Value = Name

.Range("B2").Value = PSFFAll

.Range("C2").Value = CLSFall

.Range("D2").Value = CLSWin

.Range("E2").Value = CLSEnd

.Range("F2").Value = WWRFall

.Range("G2").Value = WWRWin

.Range("H2").Value = WWREnd

.Range("I2").Value = DORFWin

.Range("J2").Value = DORFEnd

.Range("K2").Value = AccWin

.Range("L2").Value = AccEnd

End With

Application.DisplayAlerts = False

dst.Close True, SavePath & "" & fname

Application.DisplayAlerts = True

On Error Resume Next


Next C


End Sub