locked
Duplicate rows from one sheet to another RRS feed

  • Question

  • 

    In my workbook I have a button whit duplicates templates within the workbook and has an inbox to give the sheet a unique name using this VBA:

    Sub NewCustomer()
    Sheets("Main").Unprotect ""
        Dim shtNew As Worksheet
        Worksheets("New").Copy After:=Worksheets("Main")
        Set shtNew = Worksheets(Worksheets("Main").Index + 1)
      shtNew.Name = "" & InputBox("Customers Name")
      
      Dim myValue As Variant
       With Worksheets("Main")
            .Hyperlinks.Add Anchor:=.Cells(.Rows.Count, "D").End(xlUp)(2), Address:="", SubAddress:= _
            "'" & shtNew.Name & "'!A1", TextToDisplay:=shtNew.Name
            Sheets("Main").Protect ""        
        End With
    End Sub
    

    As you can see, each time a new template is added and named it drops a hyperlink to that sheet on a sheet called 'Main'.

    https://1drv.ms/u/s!AqWyxoZtZ0lm2PQlwW76-djttaQ    What I would like, is that when a transaction is entered onto the customers record, that record is copied to the table on the overview sheet. That way, we have a record for the customer on their individual sheet, and a full record of all transactions made, added row by row every time a transaction is made one whichever customer sheet on the overview sheet. I am desperate to get this working today as my managers have are putting pressure on me to get it up and running on Monday and I have been bashing my head against it for a week. Many thanks in advance, Luke

    Friday, November 23, 2018 9:58 AM

Answers

  • Hi Luke,

    Thanks for your asking. Please remember to mark the replies as answers if they helped and please help us close the thread.

     

    Thank you for understanding. If you have any question, or update, please feel free to let us know.

     

    I wish you a happy life!

     

    Best Regards,

     

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    • Marked as answer by Luke Sykes Thursday, November 29, 2018 3:52 PM
    Wednesday, November 28, 2018 2:27 AM

All replies

  • Hi Luke,

    Please refer to the following code:

    Option Explicit
    
    Sub Move_Dupes()
    
        Dim lastrow As Long, lastcolumn As Long, rng As Range
        
        lastrow = Cells(Rows.Count, "A").End(xlUp).Row
        lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
        
        Application.ScreenUpdating = False
        
        With Sheet1
            Set rng = .Range("A1", .Cells(lastrow, lastcolumn))
            .AutoFilterMode = False
            .Range("D2").Formula = "=A2&B2&C2"
            .Range("D2").AutoFill Destination:=.Range("D2:D" & lastrow)
            
            .Range("E2").Formula = "=COUNTIF($D$2:$D$" & lastrow & ",D2)"
            .Range("E2").AutoFill Destination:=.Range("E2:E" & lastrow)
            
            rng.AutoFilter field:=5, Criteria1:=">1"
            rng.Offset(1, 0).SpecialCells(12).Copy Destination:=Sheet2.Range("A1")
            rng.Offset(1, 0).EntireRow.Delete
            Columns("D:E").ClearContents
            .AutoFilterMode = False
            
        End With
        
        Set rng = Nothing
        
        Application.ScreenUpdating = True
    
    End Sub

    For more information, please see the following links:

    Move Duplicate Rows to New Sheet

    Excel Moving duplicate values to new sheet

    Move Duplicate rows into another sheet

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, November 26, 2018 2:33 AM
  • Hi Luke,

    Thanks for your asking. Please remember to mark the replies as answers if they helped and please help us close the thread.

     

    Thank you for understanding. If you have any question, or update, please feel free to let us know.

     

    I wish you a happy life!

     

    Best Regards,

     

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    • Marked as answer by Luke Sykes Thursday, November 29, 2018 3:52 PM
    Wednesday, November 28, 2018 2:27 AM
  • Hi Lina

    Thank you for your reply.

    I have decided to follow another route. Rather than using the vba to copy the entire row, I am thinking of using a worksheet change to copy a unique transaction number from the individual customer sheets to the subsequent overview sheets.

    Here is my post on the community forum...

    

    Thursday, November 29, 2018 9:57 AM