Consolidate data
 

Hello,

I have info in columns A to AC in worksheet 1 and 2. I need to merge
them as follows:
If item number (Column A) exists in sheet 1 and 2, bring the full row
across from WS2 and replace the entry in WS 1

Thanks

Consolidate data
 

Sorry, ammended:

I have info in columns A to AC in worksheet 1 and 2. I need to merge
them as follows:
If item number (Column A) exists in sheet 1 and 2, bring the full row
across from WS2 and replace the entry in WS 1 except column C which must remain the original value as in WS1

Thanks

Consolidate data
 

Hello,

I have info in columns A to AC in worksheet 1 and 2. I need to merge
them as follows:
If item number (Column A) exists in sheet 1 and 2, bring the full row
across from WS2 and replace the entry in WS 1 except column C which
must remain the original value as in WS1


Thanks




On Jan 29, 1:05 pm, "splat" wrote:
Sorry, ammended:



I have info in columns A to AC in worksheet 1 and 2. I need to merge
them as follows:
If item number (Column A) exists in sheet 1 and 2, bring the full row
across from WS2 and replace the entry in WS 1 except column C which must remain the original value as in WS1

Thanks- Hide quoted text -- Show quoted text -

Consolidate data
 

Hi splat

Try this. Note you do not mention about if data exists on sheet2 and not
sheet1. :

Option Explicit

Sub mergeit()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lRowLast As Long, lRow As Long, lSrcRow As Long

Set ws1 = Worksheets("WS1")
Set ws2 = Worksheets("WS2")

lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp)

On Error Resume Next

For lRow = 2 To lRowLast
lSrcRow = 0
lSrcRow = Application.WorksheetFunction.Match( _
ws1.Cells(lRow, 1), ws2.Range("A:A"), 0)
If lSrcRow 0 Then
ws2.Range("A" & lSrcRow & ":AC" & lSrcRow).Copy _
ws1.Range("A" & lRow & ":AC" & lRow)
End If
Next lRow

End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"splat" wrote:

Hello,

I have info in columns A to AC in worksheet 1 and 2. I need to merge
them as follows:
If item number (Column A) exists in sheet 1 and 2, bring the full row
across from WS2 and replace the entry in WS 1

Thanks

Consolidate data
 

You need to change the following:

ws2.Range("A" & lSrcRow & ":AC" & lSrcRow).Copy _
ws1.Range("A" & lRow & ":AC" & lRow)

to
ws2.Range("B" & lSrcRow).Copy _
ws1.Range("B" & lRow )
ws2.Range("D" & lSrcRow & ":AC" & lSrcRow).Copy _
ws1.Range("D" & lRow & ":AC" & lRow)



--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Martin Fishlock" wrote:

Hi splat

Try this. Note you do not mention about if data exists on sheet2 and not
sheet1. :

Option Explicit

Sub mergeit()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lRowLast As Long, lRow As Long, lSrcRow As Long

Set ws1 = Worksheets("WS1")
Set ws2 = Worksheets("WS2")

lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp)

On Error Resume Next

For lRow = 2 To lRowLast
lSrcRow = 0
lSrcRow = Application.WorksheetFunction.Match( _
ws1.Cells(lRow, 1), ws2.Range("A:A"), 0)
If lSrcRow 0 Then
ws2.Range("A" & lSrcRow & ":AC" & lSrcRow).Copy _
ws1.Range("A" & lRow & ":AC" & lRow)
End If
Next lRow

End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"splat" wrote:

Hello,

I have info in columns A to AC in worksheet 1 and 2. I need to merge
them as follows:
If item number (Column A) exists in sheet 1 and 2, bring the full row
across from WS2 and replace the entry in WS 1

Thanks

Consolidate data
 

Thanks for the reply Martin,

I get the following highlighted in yellow:

lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp)

BTW, if the data is in sheet 2, then it will also be in sheet 1.

Thanks again.



On Jan 29, 2:06 pm, Martin Fishlock
wrote:
Hi splat

Try this. Note you do not mention about if data exists on sheet2 and not
sheet1. :

Option Explicit

Sub mergeit()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lRowLast As Long, lRow As Long, lSrcRow As Long

Set ws1 = Worksheets("WS1")
Set ws2 = Worksheets("WS2")

lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp)

On Error Resume Next

For lRow = 2 To lRowLast
lSrcRow = 0
lSrcRow = Application.WorksheetFunction.Match( _
ws1.Cells(lRow, 1), ws2.Range("A:A"), 0)
If lSrcRow 0 Then
ws2.Range("A" & lSrcRow & ":AC" & lSrcRow).Copy _
ws1.Range("A" & lRow & ":AC" & lRow)
End If
Next lRow

End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.



"splat" wrote:
Hello,

I have info in columns A to AC in worksheet 1 and 2. I need to merge
them as follows:
If item number (Column A) exists in sheet 1 and 2, bring the full row
across from WS2 and replace the entry in WS 1

Thanks- Hide quoted text -- Show quoted text -

Consolidate data
 

lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp)
should be

lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp).Row

--
Regards,
Tom Ogilvy


"splat" wrote:

Thanks for the reply Martin,

I get the following highlighted in yellow:

lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp)

BTW, if the data is in sheet 2, then it will also be in sheet 1.

Thanks again.



On Jan 29, 2:06 pm, Martin Fishlock
wrote:
Hi splat

Try this. Note you do not mention about if data exists on sheet2 and not
sheet1. :

Option Explicit

Sub mergeit()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lRowLast As Long, lRow As Long, lSrcRow As Long

Set ws1 = Worksheets("WS1")
Set ws2 = Worksheets("WS2")

lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp)

On Error Resume Next

For lRow = 2 To lRowLast
lSrcRow = 0
lSrcRow = Application.WorksheetFunction.Match( _
ws1.Cells(lRow, 1), ws2.Range("A:A"), 0)
If lSrcRow 0 Then
ws2.Range("A" & lSrcRow & ":AC" & lSrcRow).Copy _
ws1.Range("A" & lRow & ":AC" & lRow)
End If
Next lRow

End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.



"splat" wrote:
Hello,

I have info in columns A to AC in worksheet 1 and 2. I need to merge
them as follows:
If item number (Column A) exists in sheet 1 and 2, bring the full row
across from WS2 and replace the entry in WS 1

Thanks- Hide quoted text -- Show quoted text -

Consolidate data
 

Thanks Tom

It was using silly test data that gave the same answer as the row number....

Splat:

I also start on row 2 from the for statement and maybe you needto change it
to row 1.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Tom Ogilvy" wrote:

lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp)
should be

lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp).Row

--
Regards,
Tom Ogilvy


"splat" wrote:

Thanks for the reply Martin,

I get the following highlighted in yellow:

lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp)

BTW, if the data is in sheet 2, then it will also be in sheet 1.

Thanks again.



On Jan 29, 2:06 pm, Martin Fishlock
wrote:
Hi splat

Try this. Note you do not mention about if data exists on sheet2 and not
sheet1. :

Option Explicit

Sub mergeit()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lRowLast As Long, lRow As Long, lSrcRow As Long

Set ws1 = Worksheets("WS1")
Set ws2 = Worksheets("WS2")

lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp)

On Error Resume Next

For lRow = 2 To lRowLast
lSrcRow = 0
lSrcRow = Application.WorksheetFunction.Match( _
ws1.Cells(lRow, 1), ws2.Range("A:A"), 0)
If lSrcRow 0 Then
ws2.Range("A" & lSrcRow & ":AC" & lSrcRow).Copy _
ws1.Range("A" & lRow & ":AC" & lRow)
End If
Next lRow

End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.



"splat" wrote:
Hello,

I have info in columns A to AC in worksheet 1 and 2. I need to merge
them as follows:
If item number (Column A) exists in sheet 1 and 2, bring the full row
across from WS2 and replace the entry in WS 1

Thanks- Hide quoted text -- Show quoted text -

Consolidate data
 

Thanks guysfor the reply, it did something, but what it was, i'm not
sure. Both ws1 and 2 remain the same. It didn't bring the info from
rows a - ac on ws2 over to ws1.




On Jan 29, 3:37 pm, Martin Fishlock
wrote:
Thanks Tom

It was using silly test data that gave the same answer as the row number....

Splat:

I also start on row 2 from the for statement and maybe you needto change it
to row 1.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.



"Tom Ogilvy" wrote:
lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp)
should be

lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp).Row

--
Regards,
Tom Ogilvy

"splat" wrote:

Thanks for the reply Martin,

I get the following highlighted in yellow:

lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp)

BTW, if the data is in sheet 2, then it will also be in sheet 1.

Thanks again.

On Jan 29, 2:06 pm, Martin Fishlock
wrote:
Hi splat

Try this. Note you do not mention about if data exists on sheet2 and not
sheet1. :

Option Explicit

Sub mergeit()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lRowLast As Long, lRow As Long, lSrcRow As Long

Set ws1 = Worksheets("WS1")
Set ws2 = Worksheets("WS2")

lRowLast = ws1.Cells(Rows.Count, 1).End(xlUp)

On Error Resume Next

For lRow = 2 To lRowLast
lSrcRow = 0
lSrcRow = Application.WorksheetFunction.Match( _
ws1.Cells(lRow, 1), ws2.Range("A:A"), 0)
If lSrcRow 0 Then
ws2.Range("A" & lSrcRow & ":AC" & lSrcRow).Copy _
ws1.Range("A" & lRow & ":AC" & lRow)
End If
Next lRow

End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

"splat" wrote:
Hello,

I have info in columns A to AC in worksheet 1 and 2. I need to merge
them as follows:
If item number (Column A) exists in sheet 1 and 2, bring the full row
across from WS2 and replace the entry in WS 1

Thanks- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -