change folder name as required

Sub SaveToTemplet()

Folder = "c:\temp\"

With ActiveSheet
'get company name with employee name
CompName = .Range("C2")
'seperate company name from employee
CompName = Left(CompName, InStr(CompName, " ") - 1)
'add Ltd to Company name
CompName = CompName & "Ltd"

Set templet = Workbooks.Open(Filename:=Folder & CompName)
Set TempletSht = templet.ActiveSheet

.Range("C2").Copy Destination:=TempletSht.Range("B13")
.Range("G2").Copy Destination:=TempletSht.Range("B12")
.Range("F2").Copy Destination:=TempletSht.Range("B20")
.Range("J2").Copy Destination:=TempletSht.Range("B41")
.Range("A2").Copy Destination:=TempletSht.Range("B42")
.Range("O2").Copy Destination:=TempletSht.Range("B17")

End With

templet.Close savechanges:=True


End Sub

"franciz" wrote:

Hi all,

I have a working sheet contains 15 columns and undetermine rows of data. I
have workbooks saved as group name, eg. "ABC Ltd" in a folder named under
group, eg. "ABC Ltd". These workbooks consist of about 5 -10 sheet templates
and are named under "group person "eg. "ABC John"

I need a macro to do the following :

1) Go to the correct folder and get to the workbook based on Column C of the
working sheet by using the first two or three characters, eg C2 value is "ABC
John"
The macro will go to Folder "ABC Ltd" and get the files "ABC Ltd"

2) Based on the value in Column C of the working sheet, get the relevant
sheet template. Both the sheet templates and the data in column C are named
the same,
eg. "ABC John"

3) copy certain the data in row 2 to specfic cells in the sheet templates.

working worksheet C2 to B13 of template
working worksheet G2 to B12 of the template
working worksheet F2 to B20 of the template
working worksheet J2 to B41 of the template
working worksheet A2 to B42 of the template
working worksheet O2 to B17 of the template

TIA and appreciate any help on this.

regards, francis