Com,apring sheet1 to sheet2 & outputing to sheet3 2

[keywordmonkey]

Hi

I'm new to VBA programing and don't know where to start with code to do this:

User has pasted old values on spreadsheet1, and new values onto sheet2.

1) Need a macro that:
- compares contents of each row on sheet2 column A, to sheet1 column A (not case sensitive)
- if the contents of a sheet2 row matches one on sheet1 , it CUTS the whole row on sheet2 and pastes onto sheet3
- it then copies the value of sheet1 column C for the matching row onto column C of sheet3

The end result is sheet 1 and 2 have been compared, the matches copied to sheet3 from sheet2, but updated with the column C value of sheet1.

Can anybody help?

 

[SkipVought]

Hi,

Here's some code that will do what you want...

Sub CopyData()
    Dim lookup, ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, lRow3 As Long
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    Set ws3 = Worksheets("Sheet3")
    lRow3 = 1
    For Each c In Range(Cells(1, 1), Cells(1, 1).End(xlDown))
        lookup = Application.Match(c.Value, Range(ws2.Cells(1, 1), ws2.Cells(1, 1).End(xlDown)), 0)
        If Not IsError(lookup) Then
            With ws3
                .Cells(lRow3, 1).Value = c.Value
                .Cells(lRow3, 3).Value = c.Offset(0, 2).Value
            End With
            lRow3 = lRow3 + 1
        End If
    Next
    Set ws1 = Nothing
    Set ws2 = Nothing
    Set ws3 = Nothing
End Sub

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Tiglet]

Skip,

You've saved my life!!

I've been trying to work out how to do a range.select with variables - didn't cross my mind to combine with cell(r,c)

Been scratching my head for a while over this
Definitely deserves a star from me!
Thanks

 

[keywordmonkey]

Thanks SkipVought - but I'm getting an error:

Visual Basic
400

Any ideas what's wrong?

(Sheets are called sheet1/2/3)

 

[SkipVought]

Did you copy and paste this code?

On what line of code is it failing? Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[keywordmonkey]

Ah, fixed it myself - I stupidly pasted it into sheet1 in VB Editor, not workbook.

But, when there is a match between a cell in column A on sheets 1 and 2, it's not copying the value from sheet2 column C onto sheet3. It's not copying column B or D either from sheet2.

Hw do I add this in?

Thanks again.

 

[SkipVought]

sorry,

I forgot to add a couple lines of code per your request...

Sub CopyData()
    Dim lookup, ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, lRow3 As Long
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    Set ws3 = Worksheets("Sheet3")
    lRow3 = 1
    For Each c In Range(Cells(1, 1), Cells(1, 1).End(xlDown))
        lookup = Application.Match(c.Value, Range(ws2.Cells(1, 1), ws2.Cells(1, 1).End(xlDown)), 0)
        If Not IsError(lookup) Then
            With ws3
                For Each h In ws2.Cells(1, 1).CurrentRegion.Columns
                    .Cells(lRow3, h.Column).Value = ws2.Cells(lookup, h.Column).Value
                Next
                .Cells(lRow3, 1).Value = c.Value
                .Cells(lRow3, 3).Value = c.Offset(0, 2).Value
            End With
            lRow3 = lRow3 + 1
        End If
    Next
    Set ws1 = Nothing
    Set ws2 = Nothing
    Set ws3 = Nothing
End Sub

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[keywordmonkey]

Hi Skip

That copies the columns ok - but I need a slight change (sorry to move the goal posts)

It needs to compare sheet1 column A with sheet2 column A, and if there is a match between them, CUT the contents of the sheet2 row (columns A-E inclusive) to sheet3.

Then it needs to cut the contents of sheet1 for just column C and overwrite this on sheet3.

In other words it will:
1)compare column A on sheet1 and sheet2
2)for any matches cut the whole row to sheet3 (by cutting it reduces sheet2 to list just the column A values with no match on sheet1)
3)for the matches, cut the value of sheet1 column C and paste it onto sheet3.

This has definitely taught me to plan and check requirements before I start to write code as I learn.