PDA

View Full Version : amending code copy data from sheet to multiple without repeating



maghari
11-27-2020, 05:29 AM
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

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

DanWall
12-02-2020, 02:25 PM
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.


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

maghari
12-02-2020, 02:54 PM
thanks DanWall I think there is better way like this

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:

DanWall
12-03-2020, 09:17 AM
thanks DanWall I think there is better way like this

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?


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?

maghari
12-07-2020, 11:53 AM
updating based on column d,e ( the dates)