MERGE AND REPLACE MATCHING CELLS
 

I have two sheets, one with one column of data, the other with more than
fifty. I need to take the first sheet and all data therein match and replace
identical column in the second sheet. How do I do this? Thanks!

MERGE AND REPLACE MATCHING CELLS
 

I think we need more information, with example(s) here. If I take an A from
sheet 1 and find matching cell (match by address or contents??) by contents
and replace it with data from first sheet, I'm left with something that looks
like it did to begin with.

Manually, you could select the column on the first sheet and the Copy it; go
to the second sheet and select the same column and Paste it over what ever is
in that same column.

If there is some reason you can't copy the entire column from one sheet to
the other, then we need to hear about that also.


"AnnMK" wrote:

I have two sheets, one with one column of data, the other with more than
fifty. I need to take the first sheet and all data therein match and replace
identical column in the second sheet. How do I do this? Thanks!

MERGE AND REPLACE MATCHING CELLS
 

Good point. Sorry. Sheet 1 has one column with ID's that have been color
coded *(font). I need to match them with the ID's in Sheet 2 (same column
header) that are not colored and replace ID's in sheet 2 with the colored
ID's from Sheet 1. Sheet 1 column A, about 2,000 rows. Sheet 2 column A
is the one I want updated. Kind regards

"JLatham" wrote:

I think we need more information, with example(s) here. If I take an A from
sheet 1 and find matching cell (match by address or contents??) by contents
and replace it with data from first sheet, I'm left with something that looks
like it did to begin with.

Manually, you could select the column on the first sheet and the Copy it; go
to the second sheet and select the same column and Paste it over what ever is
in that same column.

If there is some reason you can't copy the entire column from one sheet to
the other, then we need to hear about that also.


"AnnMK" wrote:

I have two sheets, one with one column of data, the other with more than
fifty. I need to take the first sheet and all data therein match and replace
identical column in the second sheet. How do I do this? Thanks!

MERGE AND REPLACE MATCHING CELLS
 

Ann,
The code below should do the trick for you. There are some Const values you
can change to get it to work properly in your workbook. I've already set it
up to compare column A on one sheet to column A on the other, but didn't know
the names of the sheets (as shown on their tabs), so you'll need to change
those.

To use the code:
Open your workbook, press [Alt]+[F11] to open the VB Editor (VBE).
In the VBE, choose Insert | Module and a blank module will appear.
Copy the code below and paste it into the blank module.
Change the Const values you need to in the editor.
Close the VBE

Use Tools | Macro | Macros to select the [UpdateFontColors] macro and click
the [Run] button. How long it takes depends on the number of entries in the
two lists.

Hope this helps. The code:

Sub UpdateFontColors()
'AnnMK - you need to change these Const values to = the proper
'sheet names and column IDs for your situation
'The "MasterSheet" is the one on the sheet with just 1 column
Const MasterSheet = "Sheet1" ' the name on the sheet's tab
Const MasterColumn = "A" ' column w/colored font entries

'The "ToUpdateSheet" is the one with the many columns.
Const ToUpdateSheet = "Sheet2" ' again, the name on that sheet's tab
Const ToUpdateColumn = "A" ' column with names to be altered
'END OF USER REDEFINABLE Values

Dim masterWS As Worksheet
Dim masterListRange As Range
Dim anyMasterEntry As Range

Dim toUpdateWS As Worksheet
Dim updateListRange As Range
Dim anyUpdateEntry As Range

Set masterWS = ThisWorkbook.Worksheets(MasterSheet)
Set masterListRange = masterWS.Range(MasterColumn & "1:" & _
masterWS.Range(MasterColumn & Rows.Count).End(xlUp).Address)
Set toUpdateWS = ThisWorkbook.Worksheets(ToUpdateSheet)
Set updateListRange = toUpdateWS.Range(ToUpdateColumn & "1:" & _
toUpdateWS.Range(ToUpdateColumn & Rows.Count).End(xlUp).Address)

'begin the actual work
Application.ScreenUpdating = False ' improve performance speed
For Each anyMasterEntry In masterListRange
For Each anyUpdateEntry In updateListRange
If anyMasterEntry.Value = anyUpdateEntry.Value Then
'changes ONLY the font color
anyUpdateEntry.Font.ColorIndex = _
anyMasterEntry.Font.ColorIndex
End If
Next
Next
'all done, do housekeeping
Set masterListRange = Nothing
Set updateListRange = Nothing
Set masterWS = Nothing
Set toUpdateWS = Nothing
End Sub


"AnnMK" wrote:

Good point. Sorry. Sheet 1 has one column with ID's that have been color
coded *(font). I need to match them with the ID's in Sheet 2 (same column
header) that are not colored and replace ID's in sheet 2 with the colored
ID's from Sheet 1. Sheet 1 column A, about 2,000 rows. Sheet 2 column A
is the one I want updated. Kind regards

"JLatham" wrote:

I think we need more information, with example(s) here. If I take an A from
sheet 1 and find matching cell (match by address or contents??) by contents
and replace it with data from first sheet, I'm left with something that looks
like it did to begin with.

Manually, you could select the column on the first sheet and the Copy it; go
to the second sheet and select the same column and Paste it over what ever is
in that same column.

If there is some reason you can't copy the entire column from one sheet to
the other, then we need to hear about that also.


"AnnMK" wrote:

I have two sheets, one with one column of data, the other with more than
fifty. I need to take the first sheet and all data therein match and replace
identical column in the second sheet. How do I do this? Thanks!

MERGE AND REPLACE MATCHING CELLS
 

Hey J.

Thanks for your help. I've never run a Macro. Did everything you advised
below but am getting a runtime error on

Set masterWS = ThisWorkbook.Worksheets(MasterSheet)

The worksheet tabs are default names, i.e., Sheet 1, Sheet 2. I'm not sure
what I'm doing wrong. Do you mind terribly giving me some further assistance?

Thanks again.
Kind regards,
a


"JLatham" wrote:

Ann,
The code below should do the trick for you. There are some Const values you
can change to get it to work properly in your workbook. I've already set it
up to compare column A on one sheet to column A on the other, but didn't know
the names of the sheets (as shown on their tabs), so you'll need to change
those.

To use the code:
Open your workbook, press [Alt]+[F11] to open the VB Editor (VBE).
In the VBE, choose Insert | Module and a blank module will appear.
Copy the code below and paste it into the blank module.
Change the Const values you need to in the editor.
Close the VBE

Use Tools | Macro | Macros to select the [UpdateFontColors] macro and click
the [Run] button. How long it takes depends on the number of entries in the
two lists.

Hope this helps. The code:

Sub UpdateFontColors()
'AnnMK - you need to change these Const values to = the proper
'sheet names and column IDs for your situation
'The "MasterSheet" is the one on the sheet with just 1 column
Const MasterSheet = "Sheet1" ' the name on the sheet's tab
Const MasterColumn = "A" ' column w/colored font entries

'The "ToUpdateSheet" is the one with the many columns.
Const ToUpdateSheet = "Sheet2" ' again, the name on that sheet's tab
Const ToUpdateColumn = "A" ' column with names to be altered
'END OF USER REDEFINABLE Values

Dim masterWS As Worksheet
Dim masterListRange As Range
Dim anyMasterEntry As Range

Dim toUpdateWS As Worksheet
Dim updateListRange As Range
Dim anyUpdateEntry As Range

Set masterWS = ThisWorkbook.Worksheets(MasterSheet)
Set masterListRange = masterWS.Range(MasterColumn & "1:" & _
masterWS.Range(MasterColumn & Rows.Count).End(xlUp).Address)
Set toUpdateWS = ThisWorkbook.Worksheets(ToUpdateSheet)
Set updateListRange = toUpdateWS.Range(ToUpdateColumn & "1:" & _
toUpdateWS.Range(ToUpdateColumn & Rows.Count).End(xlUp).Address)

'begin the actual work
Application.ScreenUpdating = False ' improve performance speed
For Each anyMasterEntry In masterListRange
For Each anyUpdateEntry In updateListRange
If anyMasterEntry.Value = anyUpdateEntry.Value Then
'changes ONLY the font color
anyUpdateEntry.Font.ColorIndex = _
anyMasterEntry.Font.ColorIndex
End If
Next
Next
'all done, do housekeeping
Set masterListRange = Nothing
Set updateListRange = Nothing
Set masterWS = Nothing
Set toUpdateWS = Nothing
End Sub


"AnnMK" wrote:

Good point. Sorry. Sheet 1 has one column with ID's that have been color
coded *(font). I need to match them with the ID's in Sheet 2 (same column
header) that are not colored and replace ID's in sheet 2 with the colored
ID's from Sheet 1. Sheet 1 column A, about 2,000 rows. Sheet 2 column A
is the one I want updated. Kind regards

"JLatham" wrote:

I think we need more information, with example(s) here. If I take an A from
sheet 1 and find matching cell (match by address or contents??) by contents
and replace it with data from first sheet, I'm left with something that looks
like it did to begin with.

Manually, you could select the column on the first sheet and the Copy it; go
to the second sheet and select the same column and Paste it over what ever is
in that same column.

If there is some reason you can't copy the entire column from one sheet to
the other, then we need to hear about that also.


"AnnMK" wrote:

I have two sheets, one with one column of data, the other with more than
fifty. I need to take the first sheet and all data therein match and replace
identical column in the second sheet. How do I do this? Thanks!

MERGE AND REPLACE MATCHING CELLS
 

Double check this line of code:
Const MasterSheet = "Sheet1" ' the name on the sheet's tab
make sure that the "Sheet1" part of it is spelled EXACTLY like it is spelled
on the worksheet's tab.
Same for the line that reads
Const ToUpdateSheet = "Sheet2"

It is not unusual to find that on the sheet tab there may be an added space
after the last visible character, or even before the first one. If they are
"Sheet 1" and "Sheet 2" and not "Sheet1"/"Sheet2", then the missing space
before the digit is what is is causing the error.

I also presume that you put the code into the same workbook with those
worksheets? Right?

"AnnMK" wrote:

Hey J.

Thanks for your help. I've never run a Macro. Did everything you advised
below but am getting a runtime error on

Set masterWS = ThisWorkbook.Worksheets(MasterSheet)

The worksheet tabs are default names, i.e., Sheet 1, Sheet 2. I'm not sure
what I'm doing wrong. Do you mind terribly giving me some further assistance?

Thanks again.
Kind regards,
a


"JLatham" wrote:

Ann,
The code below should do the trick for you. There are some Const values you
can change to get it to work properly in your workbook. I've already set it
up to compare column A on one sheet to column A on the other, but didn't know
the names of the sheets (as shown on their tabs), so you'll need to change
those.

To use the code:
Open your workbook, press [Alt]+[F11] to open the VB Editor (VBE).
In the VBE, choose Insert | Module and a blank module will appear.
Copy the code below and paste it into the blank module.
Change the Const values you need to in the editor.
Close the VBE

Use Tools | Macro | Macros to select the [UpdateFontColors] macro and click
the [Run] button. How long it takes depends on the number of entries in the
two lists.

Hope this helps. The code:

Sub UpdateFontColors()
'AnnMK - you need to change these Const values to = the proper
'sheet names and column IDs for your situation
'The "MasterSheet" is the one on the sheet with just 1 column
Const MasterSheet = "Sheet1" ' the name on the sheet's tab
Const MasterColumn = "A" ' column w/colored font entries

'The "ToUpdateSheet" is the one with the many columns.
Const ToUpdateSheet = "Sheet2" ' again, the name on that sheet's tab
Const ToUpdateColumn = "A" ' column with names to be altered
'END OF USER REDEFINABLE Values

Dim masterWS As Worksheet
Dim masterListRange As Range
Dim anyMasterEntry As Range

Dim toUpdateWS As Worksheet
Dim updateListRange As Range
Dim anyUpdateEntry As Range

Set masterWS = ThisWorkbook.Worksheets(MasterSheet)
Set masterListRange = masterWS.Range(MasterColumn & "1:" & _
masterWS.Range(MasterColumn & Rows.Count).End(xlUp).Address)
Set toUpdateWS = ThisWorkbook.Worksheets(ToUpdateSheet)
Set updateListRange = toUpdateWS.Range(ToUpdateColumn & "1:" & _
toUpdateWS.Range(ToUpdateColumn & Rows.Count).End(xlUp).Address)

'begin the actual work
Application.ScreenUpdating = False ' improve performance speed
For Each anyMasterEntry In masterListRange
For Each anyUpdateEntry In updateListRange
If anyMasterEntry.Value = anyUpdateEntry.Value Then
'changes ONLY the font color
anyUpdateEntry.Font.ColorIndex = _
anyMasterEntry.Font.ColorIndex
End If
Next
Next
'all done, do housekeeping
Set masterListRange = Nothing
Set updateListRange = Nothing
Set masterWS = Nothing
Set toUpdateWS = Nothing
End Sub


"AnnMK" wrote:

Good point. Sorry. Sheet 1 has one column with ID's that have been color
coded *(font). I need to match them with the ID's in Sheet 2 (same column
header) that are not colored and replace ID's in sheet 2 with the colored
ID's from Sheet 1. Sheet 1 column A, about 2,000 rows. Sheet 2 column A
is the one I want updated. Kind regards

"JLatham" wrote:

I think we need more information, with example(s) here. If I take an A from
sheet 1 and find matching cell (match by address or contents??) by contents
and replace it with data from first sheet, I'm left with something that looks
like it did to begin with.

Manually, you could select the column on the first sheet and the Copy it; go
to the second sheet and select the same column and Paste it over what ever is
in that same column.

If there is some reason you can't copy the entire column from one sheet to
the other, then we need to hear about that also.


"AnnMK" wrote:

I have two sheets, one with one column of data, the other with more than
fifty. I need to take the first sheet and all data therein match and replace
identical column in the second sheet. How do I do this? Thanks!

MERGE AND REPLACE MATCHING CELLS
 

Hiya. I did all that. Spelling is absolutely correct. When I get the pop
up saying "Runtime error" it's highlighting the following line ...

Set masterWS = ThisWorkbook.Worksheets(MasterSheet)

Don't know if that helps you. I'd still like to get this to work.
Thanks again.

"JLatham" wrote:

Double check this line of code:
Const MasterSheet = "Sheet1" ' the name on the sheet's tab
make sure that the "Sheet1" part of it is spelled EXACTLY like it is spelled
on the worksheet's tab.
Same for the line that reads
Const ToUpdateSheet = "Sheet2"

It is not unusual to find that on the sheet tab there may be an added space
after the last visible character, or even before the first one. If they are
"Sheet 1" and "Sheet 2" and not "Sheet1"/"Sheet2", then the missing space
before the digit is what is is causing the error.

I also presume that you put the code into the same workbook with those
worksheets? Right?

"AnnMK" wrote:

Hey J.

Thanks for your help. I've never run a Macro. Did everything you advised
below but am getting a runtime error on

Set masterWS = ThisWorkbook.Worksheets(MasterSheet)

The worksheet tabs are default names, i.e., Sheet 1, Sheet 2. I'm not sure
what I'm doing wrong. Do you mind terribly giving me some further assistance?

Thanks again.
Kind regards,
a


"JLatham" wrote:

Ann,
The code below should do the trick for you. There are some Const values you
can change to get it to work properly in your workbook. I've already set it
up to compare column A on one sheet to column A on the other, but didn't know
the names of the sheets (as shown on their tabs), so you'll need to change
those.

To use the code:
Open your workbook, press [Alt]+[F11] to open the VB Editor (VBE).
In the VBE, choose Insert | Module and a blank module will appear.
Copy the code below and paste it into the blank module.
Change the Const values you need to in the editor.
Close the VBE

Use Tools | Macro | Macros to select the [UpdateFontColors] macro and click
the [Run] button. How long it takes depends on the number of entries in the
two lists.

Hope this helps. The code:

Sub UpdateFontColors()
'AnnMK - you need to change these Const values to = the proper
'sheet names and column IDs for your situation
'The "MasterSheet" is the one on the sheet with just 1 column
Const MasterSheet = "Sheet1" ' the name on the sheet's tab
Const MasterColumn = "A" ' column w/colored font entries

'The "ToUpdateSheet" is the one with the many columns.
Const ToUpdateSheet = "Sheet2" ' again, the name on that sheet's tab
Const ToUpdateColumn = "A" ' column with names to be altered
'END OF USER REDEFINABLE Values

Dim masterWS As Worksheet
Dim masterListRange As Range
Dim anyMasterEntry As Range

Dim toUpdateWS As Worksheet
Dim updateListRange As Range
Dim anyUpdateEntry As Range

Set masterWS = ThisWorkbook.Worksheets(MasterSheet)
Set masterListRange = masterWS.Range(MasterColumn & "1:" & _
masterWS.Range(MasterColumn & Rows.Count).End(xlUp).Address)
Set toUpdateWS = ThisWorkbook.Worksheets(ToUpdateSheet)
Set updateListRange = toUpdateWS.Range(ToUpdateColumn & "1:" & _
toUpdateWS.Range(ToUpdateColumn & Rows.Count).End(xlUp).Address)

'begin the actual work
Application.ScreenUpdating = False ' improve performance speed
For Each anyMasterEntry In masterListRange
For Each anyUpdateEntry In updateListRange
If anyMasterEntry.Value = anyUpdateEntry.Value Then
'changes ONLY the font color
anyUpdateEntry.Font.ColorIndex = _
anyMasterEntry.Font.ColorIndex
End If
Next
Next
'all done, do housekeeping
Set masterListRange = Nothing
Set updateListRange = Nothing
Set masterWS = Nothing
Set toUpdateWS = Nothing
End Sub


"AnnMK" wrote:

Good point. Sorry. Sheet 1 has one column with ID's that have been color
coded *(font). I need to match them with the ID's in Sheet 2 (same column
header) that are not colored and replace ID's in sheet 2 with the colored
ID's from Sheet 1. Sheet 1 column A, about 2,000 rows. Sheet 2 column A
is the one I want updated. Kind regards

"JLatham" wrote:

I think we need more information, with example(s) here. If I take an A from
sheet 1 and find matching cell (match by address or contents??) by contents
and replace it with data from first sheet, I'm left with something that looks
like it did to begin with.

Manually, you could select the column on the first sheet and the Copy it; go
to the second sheet and select the same column and Paste it over what ever is
in that same column.

If there is some reason you can't copy the entire column from one sheet to
the other, then we need to hear about that also.


"AnnMK" wrote:

I have two sheets, one with one column of data, the other with more than
fifty. I need to take the first sheet and all data therein match and replace
identical column in the second sheet. How do I do this? Thanks!

MERGE AND REPLACE MATCHING CELLS
 

What does the complete runtime error message say? It's hard to imagine
anything other than there not being a worksheet with the proper name in the
same workbook that the code is running from. The code is in the same
workbook with 'Sheet', right?

I'm going to be unavailable for about 48 hours - participating in the MSFT
event in Dallas - so if you want, you could send me the workbook via email
and I can look at it when I return. Good email [remove the spaces] is
Help From @ jlathamsite . com


"AnnMK" wrote:

Hiya. I did all that. Spelling is absolutely correct. When I get the pop
up saying "Runtime error" it's highlighting the following line ...

Set masterWS = ThisWorkbook.Worksheets(MasterSheet)

Don't know if that helps you. I'd still like to get this to work.
Thanks again.

"JLatham" wrote:

Double check this line of code:
Const MasterSheet = "Sheet1" ' the name on the sheet's tab
make sure that the "Sheet1" part of it is spelled EXACTLY like it is spelled
on the worksheet's tab.
Same for the line that reads
Const ToUpdateSheet = "Sheet2"

It is not unusual to find that on the sheet tab there may be an added space
after the last visible character, or even before the first one. If they are
"Sheet 1" and "Sheet 2" and not "Sheet1"/"Sheet2", then the missing space
before the digit is what is is causing the error.

I also presume that you put the code into the same workbook with those
worksheets? Right?

"AnnMK" wrote:

Hey J.

Thanks for your help. I've never run a Macro. Did everything you advised
below but am getting a runtime error on

Set masterWS = ThisWorkbook.Worksheets(MasterSheet)

The worksheet tabs are default names, i.e., Sheet 1, Sheet 2. I'm not sure
what I'm doing wrong. Do you mind terribly giving me some further assistance?

Thanks again.
Kind regards,
a


"JLatham" wrote:

Ann,
The code below should do the trick for you. There are some Const values you
can change to get it to work properly in your workbook. I've already set it
up to compare column A on one sheet to column A on the other, but didn't know
the names of the sheets (as shown on their tabs), so you'll need to change
those.

To use the code:
Open your workbook, press [Alt]+[F11] to open the VB Editor (VBE).
In the VBE, choose Insert | Module and a blank module will appear.
Copy the code below and paste it into the blank module.
Change the Const values you need to in the editor.
Close the VBE

Use Tools | Macro | Macros to select the [UpdateFontColors] macro and click
the [Run] button. How long it takes depends on the number of entries in the
two lists.

Hope this helps. The code:

Sub UpdateFontColors()
'AnnMK - you need to change these Const values to = the proper
'sheet names and column IDs for your situation
'The "MasterSheet" is the one on the sheet with just 1 column
Const MasterSheet = "Sheet1" ' the name on the sheet's tab
Const MasterColumn = "A" ' column w/colored font entries

'The "ToUpdateSheet" is the one with the many columns.
Const ToUpdateSheet = "Sheet2" ' again, the name on that sheet's tab
Const ToUpdateColumn = "A" ' column with names to be altered
'END OF USER REDEFINABLE Values

Dim masterWS As Worksheet
Dim masterListRange As Range
Dim anyMasterEntry As Range

Dim toUpdateWS As Worksheet
Dim updateListRange As Range
Dim anyUpdateEntry As Range

Set masterWS = ThisWorkbook.Worksheets(MasterSheet)
Set masterListRange = masterWS.Range(MasterColumn & "1:" & _
masterWS.Range(MasterColumn & Rows.Count).End(xlUp).Address)
Set toUpdateWS = ThisWorkbook.Worksheets(ToUpdateSheet)
Set updateListRange = toUpdateWS.Range(ToUpdateColumn & "1:" & _
toUpdateWS.Range(ToUpdateColumn & Rows.Count).End(xlUp).Address)

'begin the actual work
Application.ScreenUpdating = False ' improve performance speed
For Each anyMasterEntry In masterListRange
For Each anyUpdateEntry In updateListRange
If anyMasterEntry.Value = anyUpdateEntry.Value Then
'changes ONLY the font color
anyUpdateEntry.Font.ColorIndex = _
anyMasterEntry.Font.ColorIndex
End If
Next
Next
'all done, do housekeeping
Set masterListRange = Nothing
Set updateListRange = Nothing
Set masterWS = Nothing
Set toUpdateWS = Nothing
End Sub


"AnnMK" wrote:

Good point. Sorry. Sheet 1 has one column with ID's that have been color
coded *(font). I need to match them with the ID's in Sheet 2 (same column
header) that are not colored and replace ID's in sheet 2 with the colored
ID's from Sheet 1. Sheet 1 column A, about 2,000 rows. Sheet 2 column A
is the one I want updated. Kind regards

"JLatham" wrote:

I think we need more information, with example(s) here. If I take an A from
sheet 1 and find matching cell (match by address or contents??) by contents
and replace it with data from first sheet, I'm left with something that looks
like it did to begin with.

Manually, you could select the column on the first sheet and the Copy it; go
to the second sheet and select the same column and Paste it over what ever is
in that same column.

If there is some reason you can't copy the entire column from one sheet to
the other, then we need to hear about that also.


"AnnMK" wrote:

I have two sheets, one with one column of data, the other with more than
fifty. I need to take the first sheet and all data therein match and replace
identical column in the second sheet. How do I do this? Thanks!