I have a list of data on sheet1. In column A I have a list of countries,
in column C-L there is a list of figures relating to column A. On sheet
2 I have lists of data that I need to get into the table on sheet A.
The data I need (from sheet2) is when column B=column A(sheet1) I need
the data from column K to replace the figure in column C of sheet one
shifting the figures in the row to the right. If column D
(sheet2)=column A (sheet1) I need to insert the value from column L in
a similar fashion. Each name in column A appears a number of times on
sheet two and I need a program that can read them in order regardless
of whether they appear in column B or D.

Any help at all is most appreciated


Phil


--
phil2006
------------------------------------------------------------------------
phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092
View this thread: http://www.excelforum.com/showthread...hreadid=551797

Phil,

It is hard to determine what you really want, but I think I've figured it
out. I assumed that the data starts on the first row and will continue the
rest of the sheet. Obviously there is alot that can be done with this (ie.
deleting data after column L, checking for blanks, etc), but this Sub gets
the main issue out of the way.

Sub MoveData()
Dim cntry As String
For i = 1 To Sheet1.UsedRange.Rows.Count
If Cells(i, "A") < "" Then
cntry = Cells(i, "A")
For j = 1 To Sheet2.UsedRange.Rows.Count
If Sheet2.Cells(j, "B") = cntry Then
Sheet2.Cells(j, "K").Copy
Sheet1.Cells(i, "B").Insert (xlShiftToRight)
Sheet1.Cells(i, "B").PasteSpecial (xlPasteValues)
End If
If Sheet2.Cells(j, "D") = cntry Then
Sheet2.Cells(j, "L").Copy
Sheet1.Cells(i, "B").Insert (xlShiftToRight)
Sheet1.Cells(i, "B").PasteSpecial (xlPasteValues)
End If
Next
End If
Next
End Sub

Mike



"phil2006" wrote:


I have a list of data on sheet1. In column A I have a list of countries,
in column C-L there is a list of figures relating to column A. On sheet
2 I have lists of data that I need to get into the table on sheet A.
The data I need (from sheet2) is when column B=column A(sheet1) I need
the data from column K to replace the figure in column C of sheet one
shifting the figures in the row to the right. If column D
(sheet2)=column A (sheet1) I need to insert the value from column L in
a similar fashion. Each name in column A appears a number of times on
sheet two and I need a program that can read them in order regardless
of whether they appear in column B or D.

Any help at all is most appreciated


Phil


--
phil2006
------------------------------------------------------------------------
phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092
View this thread: http://www.excelforum.com/showthread...hreadid=551797