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

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

Hi Joel

Thanks for looking into this.
It give me an error message Run time error "1004"
" cannot access to "ABC Ltd", the document may be read-only or encrypted
with line highlighted :

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

I have check the workbook and it was not protected, both the path and the
files are
in the correct place.

Not sure where did this goes wrong.

regards, francis

"Joel" wrote:

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

There is a space missing in the file name. I have abcLtd.

from
CompName = CompName & "Ltd"
to
CompName = CompName & " Ltd"

"franciz" wrote:

Hi Joel

Thanks for looking into this.
It give me an error message Run time error "1004"
" cannot access to "ABC Ltd", the document may be read-only or encrypted
with line highlighted :

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

I have check the workbook and it was not protected, both the path and the
files are
in the correct place.

Not sure where did this goes wrong.

regards, francis

"Joel" wrote:

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

You also need to change the Folder to the folder you are using.

"franciz" wrote:

Hi Joel

Thanks for looking into this.
It give me an error message Run time error "1004"
" cannot access to "ABC Ltd", the document may be read-only or encrypted
with line highlighted :

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

I have check the workbook and it was not protected, both the path and the
files are
in the correct place.

Not sure where did this goes wrong.

regards, francis

"Joel" wrote:

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

Hi Joel

The macro does copy specific data to the sheet template except for B17which
give the result of #Ref, I believe this is due to the formula in column O
=F2/N2 in B17

Is it possible to have the macro look at column C and go to the specific
folders and get the sheet in the file which named the same as column C?

1) all the specific folders are saved in F:\MyProcess\xxxxx
where xxxxx is the folders' name that I need to access the files based on
the first 2 or 3 characters in column C of the working sheet

Eg. C2 have the value of ABC John
C3 have the value of ABC Mary
C4 have the value of NYC Maria

can the macro
1) open the sheet "ABC John" which it does now and copy the data and after
it is done
2) move to C3 and open the next sheet template which have "ABC Mary" under
"ABC Ltd" and copy the data.
3) then move to C4 and open NYC folder, look for "NYC Maria" and open it.
copy the
data to the sheet template of "NYC Maria"

thank you very much for assisting in this, I appreciate your effort very much

regards, francis




"Joel" wrote:

You also need to change the Folder to the folder you are using.

"franciz" wrote:

Hi Joel

Thanks for looking into this.
It give me an error message Run time error "1004"
" cannot access to "ABC Ltd", the document may be read-only or encrypted
with line highlighted :

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

I have check the workbook and it was not protected, both the path and the
files are
in the correct place.

Not sure where did this goes wrong.

regards, francis

"Joel" wrote:

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

Try replaceing the copy statement with this code. This will not copy the
format. If you neeed formats I'll need to use pastespecial.

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



"franciz" wrote:

Hi Joel

The macro does copy specific data to the sheet template except for B17which
give the result of #Ref, I believe this is due to the formula in column O
=F2/N2 in B17

Is it possible to have the macro look at column C and go to the specific
folders and get the sheet in the file which named the same as column C?

1) all the specific folders are saved in F:\MyProcess\xxxxx
where xxxxx is the folders' name that I need to access the files based on
the first 2 or 3 characters in column C of the working sheet

Eg. C2 have the value of ABC John
C3 have the value of ABC Mary
C4 have the value of NYC Maria

can the macro
1) open the sheet "ABC John" which it does now and copy the data and after
it is done
2) move to C3 and open the next sheet template which have "ABC Mary" under
"ABC Ltd" and copy the data.
3) then move to C4 and open NYC folder, look for "NYC Maria" and open it.
copy the
data to the sheet template of "NYC Maria"

thank you very much for assisting in this, I appreciate your effort very much

regards, francis




"Joel" wrote:

You also need to change the Folder to the folder you are using.

"franciz" wrote:

Hi Joel

Thanks for looking into this.
It give me an error message Run time error "1004"
" cannot access to "ABC Ltd", the document may be read-only or encrypted
with line highlighted :

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

I have check the workbook and it was not protected, both the path and the
files are
in the correct place.

Not sure where did this goes wrong.

regards, francis

"Joel" wrote:

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