amending code copy data from sheet to multiple without repeating

hello
I have this code works very well , but it repeats data continuously I would add line code just update old data if is changed and if there is a new data copy under old data

Code:

---

Sub CopyRows()   
Dim bottomD As Integer
    bottomD = Range("b" & Rows.Count).End(xlUp).Row
    Dim c As Range
    Dim ws As Worksheet
    For Each c In Sheets("data").Range("b2:b" & bottomD)
        For Each ws In Sheets
            ws.Activate
            If ws.Name = c Then
                c.EntireRow.Copy Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            End If
        Next ws
    Next c
End Sub

---

Hi

Try testing the following code on some test data first to see if it does what you want. I took a pretty basic approach to your problem by simply deduplicating any redundant entries. I appreciate that this may not completely accomplish what you need, but it is difficult to do that without further information on exactly what information is being updated.

Code:

---

Sub CopyRows()
    Dim bottomD As Integer
    bottomD = Sheets("data").Range("b" & Rows.Count).End(xlUp).Row
    Dim c As Range
    Dim ws As Worksheet
    For Each c In Sheets("data").Range("b2:b" & bottomD)
        Set ws = Sheets(c.Value)
        c.EntireRow.Copy ws.Cells(Rows.Count, "A").End(xlUp).Offset(1)
        ws.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6), Header:=xlNo
    Next c
End Sub

---

thanks DanWall I think there is better way like this

Code:

---

ws.range("a1").currentregion.clear

---

but I no know where I put this line, it gives me error
for more explanation
in sheet123 it brings data from sheet "data"

1

asd123

EXPIRED

20/11/2020

08/12/2020

18days

when I copy again should not happened every thing stay as is it , but when change data like last column change days 12 days instead of 18 days should overwrite old data not copy to a new row to the bottom
I hope this help :think:

Quote:

---

Originally Posted by maghari

thanks DanWall I think there is better way like this

Code:

---

ws.range("a1").currentregion.clear

---

but I no know where I put this line, it gives me error

---

This does something different to the code I wrote - my code deduplicates redundant entries, whereas your code above efffectively deletes everything on the sheet. In terms of the error, what does the error message say and where does it occur?

Quote:

---

for more explanation in sheet123 it brings data from sheet "data"

1

asd123

EXPIRED

20/11/2020

08/12/2020

18days

when I copy again should not happened every thing stay as is it , but when change data like last column change days 12 days instead of 18 days should overwrite old data not copy to a new row to the bottom
I hope this help :think:

---

It does help a little bit, but I suppose my question would be how is the computer to know when there is a new entry or whether there is an entry to be updated? Is it only the last two columns that would ever change? Would the number in the first column work as a key?

updating based on column d,e ( the dates)