VBA To Copy Data Particular To Separate Worksheet Based On Cell Value

[oldeirish]

Hi,
My workbook has contains two sheets that are used as follows. The first sheet (Current Data Source) downloads data from a remote database into 6 columns of data. The next week, I transfer this data (using VBA code) into another worksheet, with same six columns called Previous Data Source and download the new data to Current Data Source.
In Columns D the data is split into 4 criteria, Quote, HP, LP and Conf. I’d like to have VBA macros to copy only the data, from Columns A and D on both sheets that have changed since the previous week based on a cell value. The VBA code to compare only the data in Column A (Names) that have changed with the cell value in Column D (Status) from either Quote, HP, LP to “Conf”.
I’ve tried many different codes and tried several formulas as well, from VLOOKUP to INDEX and MATCH, but can’t seem to get it right. I’m new to VBA and am trying to understand it better each day, so I apologize if my request does not make a lot of sense. I’m appealing to all Excel wizards out there who can write this code in the hopes that I not lose any more hair! lol

 

[XLfriend777]

Hi olderish - Question - are the names in column A always the same? If so, it would be rather simple to just compare the contents of column D and then mark them for copying if they have changed. If the names in column A change, then it gets a little more complicated. Hope this helps get things started. I love Vancouver! eh!

 

[JLGWhiz]

The post is not clear, but what this code does is walk down column A of the current data and look for each item on the previous data sheet. If found, it compares column D of both sheets and if different will highlight the item on the current data sheet. (It was not specified in the post as to what should occur when not matched.) If an item on the current sheet cannot be found on the pervious data sheet, then a message box will report the item and the user can note the item and click OK to continue.

Sub compare()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, fn As Range
Set sh1 = Sheets(1) 'Edit sheet name - This is current data
Set sh2 = Sheets(2) 'Edit sheet name - This is prev data
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
    For Each c In sh1.Range("A2:A" & lr)
        Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                If c.Offset(0, 3) <> fn.Offset(0, 3) Then
                    c.Resize(1, 4).Interior.ColorIndex = 6
                End If
            Else
                MsgBox c & " not found in previous data."
            End If
    Next
End Sub

 

[oldeirish]

Hi Goesr,

The data changes every week. If a client starts as a Quote, they could have the status of HP by the next week, or very likely "Conf". I just want to compare the names in Column A to the clients that have changed their status to "Conf" in the new week.

I love Vancouver as well!!! Eh! We really do say that...A LOT!

 

[oldeirish]

Hi JLGWhiz,

Thank you so much, I am going to give this a shot and see if I've managed to explain myself correctly!!!.

Again, thank you...I'll repost soon.

 

[oldeirish]

The post is not clear, but what this code does is walk down column A of the current data and look for each item on the previous data sheet. If found, it compares column D of both sheets and if different will highlight the item on the current data sheet. (It was not specified in the post as to what should occur when not matched.) If an item on the current sheet cannot be found on the pervious data sheet, then a message box will report the item and the user can note the item and click OK to continue.

Sub compare()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, fn As Range
Set sh1 = Sheets(1) 'Edit sheet name - This is current data
Set sh2 = Sheets(2) 'Edit sheet name - This is prev data
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
    For Each c In sh1.Range("A2:A" & lr)
        Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                If c.Offset(0, 3) <> fn.Offset(0, 3) Then
                    c.Resize(1, 4).Interior.ColorIndex = 6
                End If
            Else
                MsgBox c & " not found in previous data."
            End If
    Next
End Sub

Hi again JLGWhiz,

I'm getting Type Mismatch error...I've changed it to

Sub compare()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, fn As Range
Set sh1 = ("Current_Data_Source") 'Edit sheet name - This is current data
Set sh2 = ("Prev_Data_Source") 'Edit sheet name - This is prev data
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
For Each c In sh1.Range("A2:A" & lr)
Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
If Not fn Is Nothing Then
If c.Offset(0, 3) <> fn.Offset(0, 3) Then
c.Resize(1, 4).Interior.ColorIndex = 6
End If
Else
MsgBox c & " not found in previous data."
End If
Next
End Sub

Sorry, don't understand what I could be doing wrong, I really literally have no clue and I wish I did!

Thank you kindly in advance!!!!

 

[JLGWhiz]

When you get the error message, click the 'Debug' button and post the line of code that is highlighted.

 

[oldeirish]

Hi JLGWhiz,

https://www.dropbox.com/sh/ily44jzn8b3hko6/AAD2OMvBJ90-3X_1IZSxF7POa?dl=0

Sorry, I couldn't figure out how to post screen shot. Hopefully this helps, please let me know if it does not.

Thank you so much!

 

[oldeirish]

Maybe this will explain a bit more of what I'd like to accomplish.

The data changes every week. If a client starts as a Quote, they could have the status of HP by the next week, or very likely "Conf". I just want to compare the names in Column A to the clients that have changed their status to "Conf" in the new week.

Thank you kindly,

Meghan

 

[JLGWhiz]

Maybe this will explain a bit more of what I'd like to accomplish.

The data changes every week. If a client starts as a Quote, they could have the status of HP by the next week, or very likely "Conf". I just want to compare the names in Column A to the clients that have changed their status to "Conf" in the new week.

Thank you kindly,

Meghan

I think this will do it.

Sub compare()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, fn As Range
Set sh1 = Sheets("Current_Data_Source")  'Edit sheet name - This is current data
Set sh2 = Sheets("Prev_Data_Source") 'Edit sheet name - This is prev data
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
    For Each c In sh1.Range("A2:A" & lr)
        Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                If c.Offset(0, 3) <> "Conf" And fn.Offset(0, 3) = "Conf" Then
                    c.Resize(1, 4).Interior.ColorIndex = 6
                End If
            End If
    Next
End Sub