Search data sheet for updated values, if value doesn't exist in other sheets then copy data range to specific sheet

[dlsmith36]

Help needed by anyone who can grant me some assistance. Here is what I am trying to accomplish with VBA.

I have a workbook that contains 4 sheets -

1. summary - rollup of campaign information from billing and impressions
2. billing - campaign billing information
3. impressions - campaign impression data
4. data - raw data

I need to have the VBA search the data sheet for updated values in column A (opportunity ID) that do not exist in column A of both the billing and impressions sheet. If the value does not exist then copy updated rows, range A-G, from data sheet into billing sheet last row, range A-G and impressions sheet range A6-G. Once all data has been updated, copy new rows, range A6-F to end of data in Summary sheet.

I cannot have the information in billing and impression sheets to be overwritten with all new data because I have manual monthly data that has to be updated monthly. I only need the new rows to be added at the bottom of each sheet.

Uploaded file to download.

https://1drv.ms/x/s!Atj7RD3wjbBSh3UbZaVH1L5aSX_Q

Thanks in advance!

 

[JLGWhiz]

Be sure the sheet names are correct.

Sub t()
Dim ssh As Worksheet, bsh As Worksheet, ish As Worksheet, dsh As Worksheet, c As Range
Set ssh = Sheets("Summary") 'Validate sheet name
Set bsh = Sheets("Billing") 'Validate sheet name
Set ish = Sheets("Impressions") 'Validate sheet name
Set dsh = Sheets("data") 'Validate sheet name
    With dsh
        For Each c In .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
            If Application.CountIf(bsh.Range("A:A"), c.Value) = 0 Then
                c.Resize(, 7).Copy bsh.Cells(Rows.Count, 1).End(xlUp)(2)
                If ish.Range("A6") = "" Then
                    c.Resize(, 7).Copy ish.Range("A6")
                Else
                    c.Resize(, 7).Copy ish.Cells(Rows.Count, 1).End(xlUp)(2)
                End If
            End If
        Next
    End With
    With ish
        Intersect(.Range("A:F"), .UsedRange).Offset(5).Copy ssh.Cells(Rows.Count, 1).End(xlUp)(2)
    End With
End Sub

 

[dlsmith36]

Thanks for the quick reply JLGWhiz for the quick reply and code. I am getting an run time error 1004 - We can't do that to a merged cell on line

[COLOR=#333333] c.Resize(, 7).Copy bsh.Cells(Rows.Count, 1).End(xlUp)(2)[/COLOR]

I have merged cells throughout my sheets, however, I do not have merged cells in columns A-F starting with row 6 on all sheets except data. Data sheet functions as a standard data table with header row and data starting in A2

Hope this helps.

 

[JLGWhiz]

Thanks for the quick reply JLGWhiz for the quick reply and code. I am getting an run time error 1004 - We can't do that to a merged cell on line

[COLOR=#333333] c.Resize(, 7).Copy bsh.Cells(Rows.Count, 1).End(xlUp)(2)[/COLOR]

I have merged cells throughout my sheets, however, I do not have merged cells in columns A-F starting with row 6 on all sheets except data. Data sheet functions as a standard data table with header row and data starting in A2

Hope this helps.

The indication is that the merged cell(s) are on the billing sheet. But try changing as shown below.

For Each c In .Range[COLOR=#ff0000]("A2[/COLOR]", .Cells(Rows.Count, 1).End(xlUp))

 

[JLGWhiz]

I think that what was happening was that with the range for the source search item beginning in cell A1 of the data sheet, it found no match for the header in cell A1 of the data sheet, so it tried to copy A1:G1 of the target sheet and that is where it found the merged cells. So the modification should fix that.

 

[dlsmith36]

JLGWhiz - I really appreciate your knowledge and expertise on this. I have one more question, hopefully. Now that I have the data copying over to all sheets correctly. How do I get the formats and formulas from the previous rows on all sheets (summary, billing, impressions) to copy down in the new rows, down to the last row with data?

Columns with formats and formulas that need copying down to new rows:

Summary sheet - columns G-Z
Billing sheet - columns H-AB
Impressions sheet - columns H-AA

Basically if there is data in column A of the new row to copy format and formulas from previous row on sheet.

 

[JLGWhiz]

Try this modified version

Sub t2()
Dim ssh As Worksheet, bsh As Worksheet, ish As Worksheet, dsh As Worksheet, c As Range
Set ssh = Sheets("Summary") 'Validate sheet name
Set bsh = Sheets("Billing") 'Validate sheet name
Set ish = Sheets("Impressions") 'Validate sheet name
Set dsh = Sheets("data") 'Validate sheet name
    With dsh
        For Each c In .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
            If Application.CountIf(bsh.Range("A:A"), c.Value) = 0 Then
                c.Resize(, 7).Copy bsh.Cells(Rows.Count, 1).End(xlUp)(2)
                If ish.Range("A6") = "" Then
                    c.Resize(, 7).Copy ish.Range("A6")
                Else
                    c.Resize(, 7).Copy ish.Cells(Rows.Count, 1).End(xlUp)(2)
                End If
            End If
        Next
    End With
    With ish
        Intersect(.Range("A:F"), .UsedRange).Offset(5).Copy ssh.Cells(Rows.Count, 1).End(xlUp)(2)
    .Range(.Cells(Rows.Count, "H").End(xlUp), .Cells(Rows.Count, "A").End(xlUp).Offset(, 26)).FillDown
    End With
    With ssh
    .Range(.Cells(Rows.Count, "G").End(xlUp), .Cells(Rows.Count, "A").End(xlUp).Offset(, 25)).FillDown
    End With
    With bsh
    .Range(.Cells(Rows.Count, "H").End(xlUp), .Cells(Rows.Count, "A").End(xlUp).Offset(, 27)).FillDown
    End With
End Sub

 

[JLGWhiz]

Use this instead of the one in post #7 .

Sub t3()
Dim ssh As Worksheet, bsh As Worksheet, ish As Worksheet, dsh As Worksheet, c As Range
Set ssh = Sheets("Summary") 'Validate sheet name
Set bsh = Sheets("Billing") 'Validate sheet name
Set ish = Sheets("Impressions") 'Validate sheet name
Set dsh = Sheets("data") 'Validate sheet name
    With dsh
        For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
            If Application.CountIf(bsh.Range("A6", bsh.Cells(Rows.Count, 1).End(xlUp)), c.Value) = 0 Then
                c.Resize(, 7).Copy bsh.Cells(Rows.Count, 1).End(xlUp)(2)
                If ish.Range("A6") = "" Then
                    c.Resize(, 7).Copy ish.Range("A6")
                Else
                    c.Resize(, 7).Copy ish.Cells(Rows.Count, 1).End(xlUp)(2)
                End If
            End If
        Next
    End With
    With ish
        Intersect(.Range("A:F"), .UsedRange).Offset(5).Copy ssh.Cells(Rows.Count, 1).End(xlUp)(2)
    .Range(.Cells(Rows.Count, "H").End(xlUp), .Cells(Rows.Count, "A").End(xlUp).Offset(, 26)).FillDown
    End With
    With ssh
    .Range(.Cells(Rows.Count, "G").End(xlUp), .Cells(Rows.Count, "A").End(xlUp).Offset(, 25)).FillDown
    End With
    With bsh
    .Range(.Cells(Rows.Count, "H").End(xlUp), .Cells(Rows.Count, "A").End(xlUp).Offset(, 27)).FillDown
    End With
End Sub

 

[dlsmith36]

Thank you sooo much. Code works like a charm.

 

[JLGWhiz]

Thank you sooo much. Code works like a charm.

You're welcome.
Regards, JLG