Copy Rows from master workbook & paste in other workbooks
 

Hello, I would really appreciate some help here.

I have a master workbook which is updated daily, it contains:

A B C
D
09/09 11:51 25 7543377938 Qtess
09/09 12:06 40 3261241895 Joe
09/09 12:13 70 3787370105 Qtess
09/09 12:42 50 3770339825 Don
09/09 12:49 50 6293323104 Gary

I have a Folder which contains filess with Customer's names identical as
column D
Is it possible to copy row from Master wbk and paste into the last row of
each customer's file if date is different from past entries?

Thanks & regards
farid2001

Copy Rows from master workbook & paste in other workbooks
 

you'll need to debug this, and test, for index offsets, and the like

var = yourRange
lRows = yourRange.Rows.count
redim data(4)
for lRowCount = 1 to lRows
set orngDest = nothing
filename = var(lrowcount,4)
datetofind = var(lrowcount,1)
obk = workbooks.open(thisworkbook.path & "\" & filename & ".xls")
set oRngToFind =
obk.worksheets(1).range("a1").currentregion.column (1).find(datetofind, .....
)
if orngToFind is nothing then
set orngDest =
obk.worksheets(1).range("a1").offset(obk.worksheet s(1).range("a1").currentregion.rows.count()).resiz e(1,4)

for i = 1 to orngdest.columns.count
data(i) = var(lrowcount,i)
next i
orngDest.value2 = data
end if
application.displayalerts = false
obk.save
obk.close
application.displayalerts = true
next lRowCount

"farid2001" wrote:

Hello, I would really appreciate some help here.

I have a master workbook which is updated daily, it contains:

A B C
D
09/09 11:51 25 7543377938 Qtess
09/09 12:06 40 3261241895 Joe
09/09 12:13 70 3787370105 Qtess
09/09 12:42 50 3770339825 Don
09/09 12:49 50 6293323104 Gary

I have a Folder which contains filess with Customer's names identical as
column D
Is it possible to copy row from Master wbk and paste into the last row of
each customer's file if date is different from past entries?

Thanks & regards
farid2001

Copy Rows from master workbook & paste in other workbooks
 

Thanks for your help incre-d.

I'm new in VBA and I have problems with debuging with
obk = workbooks.open(thisworkbook.path & "\" & filename & ".xls")
Could you kindly please be more specific?

Thanks & regards
farid2001

"incre-d" wrote:

you'll need to debug this, and test, for index offsets, and the like

var = yourRange
lRows = yourRange.Rows.count
redim data(4)
for lRowCount = 1 to lRows
set orngDest = nothing
filename = var(lrowcount,4)
datetofind = var(lrowcount,1)
obk = workbooks.open(thisworkbook.path & "\" & filename & ".xls")
set oRngToFind =
obk.worksheets(1).range("a1").currentregion.column (1).find(datetofind, .....
)
if orngToFind is nothing then
set orngDest =
obk.worksheets(1).range("a1").offset(obk.worksheet s(1).range("a1").currentregion.rows.count()).resiz e(1,4)

for i = 1 to orngdest.columns.count
data(i) = var(lrowcount,i)
next i
orngDest.value2 = data
end if
application.displayalerts = false
obk.save
obk.close
application.displayalerts = true
next lRowCount

"farid2001" wrote:

Hello, I would really appreciate some help here.

I have a master workbook which is updated daily, it contains:

A B C
D
09/09 11:51 25 7543377938 Qtess
09/09 12:06 40 3261241895 Joe
09/09 12:13 70 3787370105 Qtess
09/09 12:42 50 3770339825 Don
09/09 12:49 50 6293323104 Gary

I have a Folder which contains filess with Customer's names identical as
column D
Is it possible to copy row from Master wbk and paste into the last row of
each customer's file if date is different from past entries?

Thanks & regards
farid2001

Copy Rows from master workbook & paste in other workbooks
 

I didn't look at the other lines of code, but try:

Set obk = workbooks.open(thisworkbook.path & "\" & filename & ".xls")

(added the Set statement)

farid2001 wrote:

Thanks for your help incre-d.

I'm new in VBA and I have problems with debuging with
obk = workbooks.open(thisworkbook.path & "\" & filename & ".xls")
Could you kindly please be more specific?

Thanks & regards
farid2001

"incre-d" wrote:

you'll need to debug this, and test, for index offsets, and the like

var = yourRange
lRows = yourRange.Rows.count
redim data(4)
for lRowCount = 1 to lRows
set orngDest = nothing
filename = var(lrowcount,4)
datetofind = var(lrowcount,1)
obk = workbooks.open(thisworkbook.path & "\" & filename & ".xls")
set oRngToFind =
obk.worksheets(1).range("a1").currentregion.column (1).find(datetofind, .....
)
if orngToFind is nothing then
set orngDest =
obk.worksheets(1).range("a1").offset(obk.worksheet s(1).range("a1").currentregion.rows.count()).resiz e(1,4)

for i = 1 to orngdest.columns.count
data(i) = var(lrowcount,i)
next i
orngDest.value2 = data
end if
application.displayalerts = false
obk.save
obk.close
application.displayalerts = true
next lRowCount

"farid2001" wrote:

Hello, I would really appreciate some help here.

I have a master workbook which is updated daily, it contains:

A B C
D
09/09 11:51 25 7543377938 Qtess
09/09 12:06 40 3261241895 Joe
09/09 12:13 70 3787370105 Qtess
09/09 12:42 50 3770339825 Don
09/09 12:49 50 6293323104 Gary

I have a Folder which contains filess with Customer's names identical as
column D
Is it possible to copy row from Master wbk and paste into the last row of
each customer's file if date is different from past entries?

Thanks & regards
farid2001

--

Dave Peterson

Copy Rows from master workbook & paste in other workbooks
 

Perfect Dave, it openned the customer's workbook.
Now I am having gebug problems with
obk.worksheets(1).range("a1").currentregion.column (1).find(datetofind, .....)
What am I supposed to enter instead of .....?

Thanks & regards
Farid

"Dave Peterson" wrote:

I didn't look at the other lines of code, but try:

Set obk = workbooks.open(thisworkbook.path & "\" & filename & ".xls")

(added the Set statement)

farid2001 wrote:

Thanks for your help incre-d.

I'm new in VBA and I have problems with debuging with
obk = workbooks.open(thisworkbook.path & "\" & filename & ".xls")
Could you kindly please be more specific?

Thanks & regards
farid2001

"incre-d" wrote:

you'll need to debug this, and test, for index offsets, and the like

var = yourRange
lRows = yourRange.Rows.count
redim data(4)
for lRowCount = 1 to lRows
set orngDest = nothing
filename = var(lrowcount,4)
datetofind = var(lrowcount,1)
obk = workbooks.open(thisworkbook.path & "\" & filename & ".xls")
set oRngToFind =
obk.worksheets(1).range("a1").currentregion.column (1).find(datetofind, .....
)
if orngToFind is nothing then
set orngDest =
obk.worksheets(1).range("a1").offset(obk.worksheet s(1).range("a1").currentregion.rows.count()).resiz e(1,4)

for i = 1 to orngdest.columns.count
data(i) = var(lrowcount,i)
next i
orngDest.value2 = data
end if
application.displayalerts = false
obk.save
obk.close
application.displayalerts = true
next lRowCount

"farid2001" wrote:

Hello, I would really appreciate some help here.

I have a master workbook which is updated daily, it contains:

A B C
D
09/09 11:51 25 7543377938 Qtess
09/09 12:06 40 3261241895 Joe
09/09 12:13 70 3787370105 Qtess
09/09 12:42 50 3770339825 Don
09/09 12:49 50 6293323104 Gary

I have a Folder which contains filess with Customer's names identical as
column D
Is it possible to copy row from Master wbk and paste into the last row of
each customer's file if date is different from past entries?

Thanks & regards
farid2001

--

Dave Peterson

Copy Rows from master workbook & paste in other workbooks
 

Record a macro when you do an Edit|Find.

You'll see all the parms that you can use.

ps. this line is really one logical line put on multiple physical lines:

set oRngToFind _
obk.worksheets(1).range("a1").currentregion.column S(1) _
.find(datetofind, ..... )

(I added an S to .columns, too)

farid2001 wrote:

Perfect Dave, it openned the customer's workbook.
Now I am having gebug problems with
obk.worksheets(1).range("a1").currentregion.column (1).find(datetofind, .....)
What am I supposed to enter instead of .....?

Thanks & regards
Farid

"Dave Peterson" wrote:

I didn't look at the other lines of code, but try:

Set obk = workbooks.open(thisworkbook.path & "\" & filename & ".xls")

(added the Set statement)

farid2001 wrote:

Thanks for your help incre-d.

I'm new in VBA and I have problems with debuging with
obk = workbooks.open(thisworkbook.path & "\" & filename & ".xls")
Could you kindly please be more specific?

Thanks & regards
farid2001

"incre-d" wrote:

you'll need to debug this, and test, for index offsets, and the like

var = yourRange
lRows = yourRange.Rows.count
redim data(4)
for lRowCount = 1 to lRows
set orngDest = nothing
filename = var(lrowcount,4)
datetofind = var(lrowcount,1)
obk = workbooks.open(thisworkbook.path & "\" & filename & ".xls")
set oRngToFind =
obk.worksheets(1).range("a1").currentregion.column (1).find(datetofind, .....
)
if orngToFind is nothing then
set orngDest =
obk.worksheets(1).range("a1").offset(obk.worksheet s(1).range("a1").currentregion.rows.count()).resiz e(1,4)

for i = 1 to orngdest.columns.count
data(i) = var(lrowcount,i)
next i
orngDest.value2 = data
end if
application.displayalerts = false
obk.save
obk.close
application.displayalerts = true
next lRowCount

"farid2001" wrote:

Hello, I would really appreciate some help here.

I have a master workbook which is updated daily, it contains:

A B C
D
09/09 11:51 25 7543377938 Qtess
09/09 12:06 40 3261241895 Joe
09/09 12:13 70 3787370105 Qtess
09/09 12:42 50 3770339825 Don
09/09 12:49 50 6293323104 Gary

I have a Folder which contains filess with Customer's names identical as
column D
Is it possible to copy row from Master wbk and paste into the last row of
each customer's file if date is different from past entries?

Thanks & regards
farid2001

--

Dave Peterson


--

Dave Peterson