Conditional Copying

[futureancient]

This should be simple, but I can't think of how to do this..

Outline -

I want to copy data from one sheet to another sheet if the values in a column match up between the two. These values change or get altered, so a simple vlookup wont do, as the copied data will also change - once copied, I need it to remain that way.

Detail -

In sheet1 I have column C with 1,2,3,4,5 etc. In Sheet2, there is column A also with 1,2,3,4,5... In columns D and E there is data, I want to copy this data to sheet1 columns G and H on the corresponding rows with the same numbers. ie Sheet1 C35 = 1, Sheet2 A5 = 1, copy Sheet2 D5 and E5, to Sheet1 G35 and H35.

After each copy, I will run a macro that will change which rows display 1,2,3,4,5... and so, the data it corresponds with will change. How do I make the data stick to where it is copied to in Sheet1, after the macro is run and the data changes.

Thanks for your help.

 

[Fizzyfish]

Try this code:

Sub copydata()
Dim ws1 As Worksheet, ws2 As Worksheet, RowCounter As Integer, LastRow As Integer
Set ws1 = ActiveWorkbook.Worksheets("Sheet1")
Set ws2 = ActiveWorkbook.Worksheets("Sheet2")
'Get last row in Sheet2
LastRow = ws2.Cells(ws1.Rows.Count, 1).End(xlUp).Row

For RowCounter = 1 To LastRow

If ws1.Cells(RowCounter, 3) = ws2.Cells(RowCounter, 1) Then

'Values match so copy the data to sheet 1
ws2.Cells(RowCounter, 4).Copy Destination:=ws1.Cells(RowCounter, 7)
ws2.Cells(RowCounter, 5).Copy Destination:=ws1.Cells(RowCounter, 8)​

End If​

Next RowCounter
End Sub

 

[futureancient]

Thanks for that, but it doesn't seem to be working. I run the macro and I get a lot of quickly jumping numbers..

It's not necessary to get to the last row, simply to copy across sheets the data that currently relates to 1,2,3,4 etc.

I will then alter what rows these are in and their order, they then need copying again. Essentially I could be filling up columns G and H in a random sequence. The problem of copying over rows that have already been done won't be an issue, since they will never arrive on those rows again due to how I'm altering the values...

 

[Fizzyfish]

Sorry I misunderstood what you were trying to do.

 

[futureancient]

No problem, thanks for your help anyway. Anyone else have any suggestions ?

 

[futureancient]

Still trying to figure out a solution for this one.. anyone able to help?