VBA to copy one row at a time

[ScottishTornado]

Hello,

I have a workbook that requires me to copy data from one sheet to another.

The data I am copying is lifted from the original main page for analysis and possible amendments. I have been racking my brain over how to return this data if there has been any changes.

My thought is that I could use a loop to copy one row at a time back to the main sheet (which is filtered) one at a time, this would preserve any data.

Could anyone point me in the right direction?

thanks

 

[AtlantaAccessAndExcel]

Is there a data point that uniquely identifies your data? If so, you could us this to link the child data back to the parent data.

Also, we need a little more details to help you. Are these copy activities happening via macro, or manually? At what point does "return this data" happen?

 

[ScottishTornado]

Is there a data point that uniquely identifies your data? If so, you could us this to link the child data back to the parent data.

Also, we need a little more details to help you. Are these copy activities happening via macro, or manually? At what point does "return this data" happen?

Hey,

I have added a column that gives each row a unique number. The data is to be copied back when a button is clicked.

Is there any more details you need?

 

[AtlantaAccessAndExcel]

Try this:

Sub CopyBackToMaster()


Dim FoundCell As Range
Dim MasterSht As String
Dim UpdatedSht As String
Dim MySearchString As Variant
Dim LastRow As Integer
Dim StartRow As Integer
Dim MyRow As Integer
Dim InRange As String
Dim OutRange As String




MasterSht = "Master" 'your name here
UpdatedSht = "Child" 'your name here


'assume column A has the unique key
LastRow = ThisWorkbook.Worksheets(UpdatedSht).Cells(Rows.Count, "A").End(xlUp).Row
StartRow = 2 'your first row of actual data


For MyRow = StartRow To LastRow


    'assume column A has the unique key
    MySearchString = ThisWorkbook.Worksheets(UpdatedSht).Cells(MyRow, 1)


    'assume the key is located in column A
    Set FoundCell = ThisWorkbook.Worksheets(MasterSht).Range("A:A").Find(MySearchString, LookIn:=xlValues)
    If Not FoundCell Is Nothing Then
        'copy back the data here
        InRange = MyRow & ":" & MyRow
        OutRange = FoundCell.Row & ":" & FoundCell.Row
        ThisWorkbook.Worksheets(UpdatedSht).Range(InRange).Copy
        ThisWorkbook.Worksheets(MasterSht).Range(OutRange).PasteSpecial Paste:=xlPasteValues
    End If
Next


Application.CutCopyMode = False
End Sub

 

[ScottishTornado]

Fantastic work, it is working a treat. Thanks you for your help