Hi,

I have an active workbook named Updates (sheet1) that I'd like to use to
update another closed workbork named NetRev on the C:\Network directory. I'd
like help to develop a sub which for each Id located in Column A of the
Updates workbook sheet1 (which is active) it would look in the closed NetRev
workbook column B and when found it would update Columns C thru G with the
results of the Updates workbook Columns B thru F. The number of rows vary
each day for Updates. I currently have 340 rows now. Also if the item is not
in Updates workbook but in NetRev I'd like to delete it from NetRev. Also if
it's a new item in Updates workbook, I'd like to add it to the last row of
NetRev workbook.

Any help would be greatly appreciated.
--
By persisting in your path, though you forfeit the little, you gain the
great.

Have a look here, Very useful stuff
http://www.rondebruin.nl/copy7.htm

"DavidH56" wrote:

Hi,

I have an active workbook named Updates (sheet1) that I'd like to use to
update another closed workbork named NetRev on the C:\Network directory. I'd
like help to develop a sub which for each Id located in Column A of the
Updates workbook sheet1 (which is active) it would look in the closed NetRev
workbook column B and when found it would update Columns C thru G with the
results of the Updates workbook Columns B thru F. The number of rows vary
each day for Updates. I currently have 340 rows now. Also if the item is not
in Updates workbook but in NetRev I'd like to delete it from NetRev. Also if
it's a new item in Updates workbook, I'd like to add it to the last row of
NetRev workbook.

Any help would be greatly appreciated.
--
By persisting in your path, though you forfeit the little, you gain the
great.

Thanks for replying,

I've used some of Ron Debruin's code and it has helped me quite a lot. But
since I have other columns of data in the NetRev workbook I need to do more
than simply retrieve data from closed workbook. I actually need to place
data into the closed workbook using vlookup from the Updates workbook to the
NetRev workbook. I seen similiar instances posted here such as:

Sub comparebooks()

LookFName = "C:\My documents\Lookup.xls"
Set Lookbk = Workbooks.Open(Filename:=LookFName)
Set SearchRange = Lookbk.Sheets("Sheet1").Columns("G")

With Workbooks("A.xls").Sheets("Sheet1")
Sh1RowCount = 1
Sh2RowCount = 1
Do While .Range("A" & Sh1RowCount) < ""
SearchValue = .Range("A" & Sh1RowCount)
Set c = SearchRange.Find(what:=SearchValue, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
With Workbooks("A.xls").Sheets("Sheet2")
.Range("A" & Sh2RowCount) = SearchValue
Sh2RowCount = Sh2RowCount + 1
End With
Else
.Range("B" & Sh1RowCount) = c.Offset(0, 6)
End If
Sh1RowCount = Sh1RowCount + 1
Loop
End With
Lookbk.Close savechanges:=False
End Sub

I'd like to be able to adjust such sub procedure for my particular situation.

Thanks

--
By persisting in your path, though you forfeit the little, you gain the
great.



"Office_Novice" wrote:

Have a look here, Very useful stuff
http://www.rondebruin.nl/copy7.htm

"DavidH56" wrote:

Hi,

I have an active workbook named Updates (sheet1) that I'd like to use to
update another closed workbork named NetRev on the C:\Network directory. I'd
like help to develop a sub which for each Id located in Column A of the
Updates workbook sheet1 (which is active) it would look in the closed NetRev
workbook column B and when found it would update Columns C thru G with the
results of the Updates workbook Columns B thru F. The number of rows vary
each day for Updates. I currently have 340 rows now. Also if the item is not
in Updates workbook but in NetRev I'd like to delete it from NetRev. Also if
it's a new item in Updates workbook, I'd like to add it to the last row of
NetRev workbook.

Any help would be greatly appreciated.
--
By persisting in your path, though you forfeit the little, you gain the
great.