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.
--
Thank You

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

--
Gary''s Student - gsnu200907


"HarryisTrying" wrote:

> 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.
> --
> Thank You

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.
--
Thank You


"Gary''s Student" wrote:

> 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
>
> --
> Gary''s Student - gsnu200907
>
>
> "HarryisTrying" wrote:
>
> > 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.
> > --
> > Thank You

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
--
Thank You


"Gary''s Student" wrote:

> 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
>
> --
> Gary''s Student - gsnu200907
>
>
> "HarryisTrying" wrote:
>
> > 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.
> > --
> > Thank You

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" <(E-Mail Removed)> skrev i
meddelelsen news:2A6769EA-2FB5-4CFA-95A4-(E-Mail Removed)...
>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
> --
> Thank You
>
>
> "Gary''s Student" wrote:
>
>> 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
>>
>> --
>> Gary''s Student - gsnu200907
>>
>>
>> "HarryisTrying" wrote:
>>
>> > 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.
>> > --
>> > Thank You

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
--
Thank You


"Per Jessen" wrote:

> 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" <(E-Mail Removed)> skrev i
> meddelelsen news:2A6769EA-2FB5-4CFA-95A4-(E-Mail Removed)...
> >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
> > --
> > Thank You
> >
> >
> > "Gary''s Student" wrote:
> >
> >> 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
> >>
> >> --
> >> Gary''s Student - gsnu200907
> >>
> >>
> >> "HarryisTrying" wrote:
> >>
> >> > 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.
> >> > --
> >> > Thank You
>
>

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

"HarryisTrying" <(E-Mail Removed)> skrev i
meddelelsen newsEB8204A-342E-4996-A3FD-(E-Mail Removed)...
> 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
> --
> Thank You
>
>
> "Per Jessen" wrote:
>
>> 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" <(E-Mail Removed)> skrev i
>> meddelelsen news:2A6769EA-2FB5-4CFA-95A4-(E-Mail Removed)...
>> >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
>> > --
>> > Thank You
>> >
>> >
>> > "Gary''s Student" wrote:
>> >
>> >> 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
>> >>
>> >> --
>> >> Gary''s Student - gsnu200907
>> >>
>> >>
>> >> "HarryisTrying" wrote:
>> >>
>> >> > 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.
>> >> > --
>> >> > Thank You
>>
>>