Copy Changes in one workbook to another identical Master Workbook

[HarryisTrying]

I receive a workbook with three worksheets that have cells in them that are
changed. the worksheets are Servers, Network and Storage
The cells have a colorindex = 4 if changed.

I put the received in a folder C:/site changes
and the Master in C:/Master Inventory

I would like to have a Macro that would match column A between worksheets
and if the received file has any change cells (green) move the value of that
cell to the corresponding cell in the master worksheet. The worksheets have
up to 37 columns and could have a thousand rows or so. The received
worksheets typically have 5 to 75 changed rows.

 

[Gary''s Student]

This assumes that both "received.xls" amnd "master.xls" are open:

Sub marine()
Dim sh As Worksheet, rr As Range
Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = Workbooks("master.xls")
Set wb2 = Workbooks("received.xls")
For Each sh In wb2.Sheets
For Each r In sh.UsedRange
If r.Interior.ColorIndex = 4 Then
r.Copy wb1.Sheets(sh.Name).Range(r.Address)
End If
Next
Next
End Sub

 

[HarryisTrying]

Wow!
I thought that would be really complicated. I had search the web and patched
some code together that was much, much longer and I didn't have it working.

I hope to learn to write code as one line but for now it takes me 10 lines
or so to do what you did in one!

Thanks. I usually try to modify what is provided to do something different.
That way I begin to learn how the code provided really works

I appreciate your help very much.

 

[HarryisTrying]

I need something that does a row (Column A) match between files before doing
any updates.

What I didn't explain well is the columns are the exact same thing but the
master has a 1000 rows and the recieved may have 50 rows (which have updated
cells).

So, I need to look at rec'd read the name in A column then go to Master find
the Name and then update the cells that changed (green in the received).

Also, if a Name is in received but not in Master I want to right that record
to the end of Master and do a Pop Up thats says "New Record Added " & Name

Sorry, it sure works if the files are exact except for changes but the rec'd
file is a subset of Master

 

[Per Jessen]

Hi

This code is supposed to be pasted into the Master file macro sheet.

Sub CopyGreenCells()
Dim wbA As Workbook
Dim wbB As Workbook
Dim shA As Worksheet
Dim sbB As Worksheet
Dim FileToOpen As String
Dim FirstRow As Long
Dim LastRow As Long

Set wbA = ThisWorkbook
Set shA = wbA.Worksheets("Sheet1")
FileToOpen = Application.GetOpenFilename
Set wbB = Workbooks.Open(FileToOpen)
Set shb = wbB.Worksheets("Sheet1")

FirstRow = 2 ' Headings in row 1
LastRow = shb.Range("A" & Rows.Count).End(xlUp).Row

For r = FirstRow To LastRow
ID = shb.Range("A" & r).Value
Set f = shA.Range("A1:A65536").Find(what:=ID, After:=shA.Range("A1"),
LookIn:=xlValues, Lookat:=xlWhole)
If Not f Is Nothing Then
DestRow = f.Row
For col = 2 To 37 'Col B to col AK
If shb.Cells(r, col).Interior.ColorIndex = 4 Then
shb.Cells(r, col).Copy shA.Cells(DestRow, col)
End If
Next
Set f = Nothing
Else
shb.Range("A" & r).EntireRow.Copy
shA.Range("A1").End(xlDown).Offset(1, 0)
msg = MsgBox("New record added" & ID, vbInformation + vbOKOnly,
"File update")
End If
Next
End Sub

Regards,
Per

 

[HarryisTrying]

Once again you have done what I was trying to do. I can do some simple things
but how did you learn this advanced method? Are there books that can help? I
read Mr. Excel and J. Walkenback books, and they are great but your code
seems above that level.

I do have a couple of questions. I would like to copy only the values and
tried to modifiy the shb.Cells(r, col).Copy shA.Cels .(DestRow, col) to end
with .PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
but that didn't seem to be right since I got the text in Red in VBE

I did this because I have formatting and formulas in cells and apparently
the received file didn't match the Master and I got a Pop Up asking if I
wanted to use the destination and I said Yes and it seem to work fine. Trying
to prevent from getting those messages.


Other question the line below
Set f = shA.Range("A1:A65536").Find(what:=ID, After:=shA.Range("A1"),
is just going to the end of the rows for Excel 2003. I only have about 1
thousand rows so I changed it to A1:A5000 which gives me plenty of growth room

 

[Per Jessen]

Thanks for your reply.

I only read one book: 'Excel 2000 VBA programmer's reference' by 'John
Green, Stephen Bullen, Felipe Martins', and then I have followed this news
group for some years. Have also used the MVP' tutorials etc. like what you
find on http://www.cpearson.com/excel/topic.aspx. Also VBA help feature in
Excel 2000 is very usefull.

The secret is to use objects as reference (ie Range/Worksheet objects), and
get a lot of programming experience by following / contributing to
newsgroups like this.

When you use PasteSpecial, the pastespecial statement can not be on same
line like a normal Copy/Paste statement.

shb.Range("A" & r).EntireRow.Copy

shA.Range("A1").End(xlDown).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

To just search cells with data in column A, use this:

Set f = shA.Range("A1", shA.Range("A1").End(xlDown)).Find(what:=ID,
After:=shA.Range("A1"), LookIn:=xlValues, Lookat:=xlWhole)

Regards,
Per