Consulting

Results 1 to 5 of 5

Thread: amending code copy data from sheet to multiple without repeating

  1. #1

    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
    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
    Attached Files Attached Files

  2. #2
    VBAX Regular
    Joined
    Mar 2018
    Posts
    10
    Location
    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

  3. #3
    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

  4. #4
    VBAX Regular
    Joined
    Mar 2018
    Posts
    10
    Location
    Quote Originally Posted by maghari View Post
    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
    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?

  5. #5

    updating based on column d,e ( the dates)





Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •