VBA Macro using Loop and SaveAs for two workbooks

[Hyking]

Hi
Can someone help a VBA newbie on creating a macro to Loop thru a Sourcedata workbook copying data into a Template workbook, and then SaveAs a new workbook for each piece of data copied. I am creating a new workbook for each service ID and have created the file name in a "LookupData"cell .
The macro works if I include the SourceData in the Template workbook but I don't want to send out the saved spreadsheets with the SourceData.
Here is what I have drafted so far but am having trouble with Activating the two different workbooks at the critical time to save and then return to the Template to run the next loop. I open the Template first and run the macro from there.
Let me know if you require more info. Regards Terry.

Sub SaveSARfiles()
Workbooks.Open Filename:=ThisWorkbook.Path & "\SARdata.xls"

Dim a As Long
Workbooks("SARdata.xls").Sheets("SourceData").Activate
For a = 3 To 55
If Cells(a, 2) <> "" Then
Workbooks("Template.xls").Sheets("CoverSheet").Cells(26, 2) = Workbooks("SARdata").Sheets("SourceData").Cells(a, 2)
'save worksheet using Filename
Workbooks("Template.xls").Sheets("CoverSheet").Select

Dim MyCurrPath As String
MyCurrPath = ThisWorkbook.Path
ThisFile = Sheets("LookupData").Cells(1, 1)
ActiveWorkbook.SaveAs Filename:=MyCurrPath & "\" & ThisFile

ElseIf Cells(a, 2) = "" Then
Exit Sub
End If
Next a
End Sub

 

[rjwebgraphix]

Welcome to the board,

Please surround vba in code tags. See my signature line. It helps when copying and pasting into the VBA editor, so we don't have to reformat the code to read/edit it.

Thanks,
RJ