Writing data from one sheet to another template sheet and change the filename

[freeblue11]

Hi Folks!

I have a range of data from A2:A140 in one excel file and an other
file which basically is like a template.
The data from the cells A2:A140 ( one at a time) should write into Cell
C4 of the template file and then save the file as (DATA.A2) PFSR
Overview and (DATA.A3) PFSR Overview and so on......
Here DATA.A2 being the alphanumeric data in cell A2, A3 and so on.

Can anybody please help me on this. I have like 765 individual files to
create in the same way and I dont want to do it manually!!

Thanks so much for your help.

- Britney Cox

 

[Dave Peterson]

Untested, but it did compile ok:

Option Explicit
Sub testme01()

Dim DataWks As Worksheet
Dim TempWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim DestCell As Range
Dim myPath As String

myPath = "C:\temp"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

'change to match the already opened files!
Set DataWks = Workbooks("someworkbookname.xls").Worksheets("sheet1")
Set TempWks = Workbooks("someotherworkbook.xls").Worksheets("sheet2")

Set myRng = DataWks.Range("a2:A140")

Set DestCell = TempWks.Range("C4")

For Each myCell In myRng.Cells
With DestCell
.Value = myCell.Value
.Parent.SaveAs Filename:=myPath & .Value & " PFSR Overview.xls", _
FileFormat:=xlWorkbookNormal
End With
Next myCell

End Sub

There is no validation in this routine. Make sure you have good names in
A2:A140.


Change myPath to the folder that gets the files.
Open both workbooks and change these lines:
Set DataWks = Workbooks("someworkbookname.xls").Worksheets("sheet1")
Set TempWks = Workbooks("someotherworkbook.xls").Worksheets("sheet2")
to what fits.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

But I have no idea how you get 765 files from data in A2:A140. That looks more
like 139 files to me.