Copy and paste a cell into another cell according to its content

Hi

I need help with this.

I need to copy contents of cells ( by rows) from one excel sheet to another. But the destination column of the cell will depend on the value of the cell being copied.

Say the "recipient" sheet will have columns for products P1234EN, P1234ES, P1443EN, P1443FR etc and may be they are in columns H,I,J,K and in the next row a corresponding quantity

The "donor" sheet will have a number of columns , each column predefined for one product as heading. I need to copy across from donor sheet to recipient sheet the quantity data from row by row placing each quantity under the correct product code column.
I want to go down rows 1 to X until empty row is reached and look at value of B1 (which is P1234EN) move along to C1 and copy this number and paste it in the recipient sheets Column which matches B1. I will know in advance what the columns in the recipient sheet will be.

Can this be done? is there a way to automate this?

I have a similar VBA code which copies from one sheet to another row by row. But with that one I can specify in the code where each data will be copied to. Difference here is that where the data ends up is determined by the value of another cell.

Thanks for any advice.

Talāt

Thanks for the response Robert.

I have only just seen this. I was waiting to receive an email alert saying that there was a reply to my post, but for some reason I never received it.
I will try your suggestion and come back to you. I am very grateful to you for taking the time to respond and make this suggestion.

Talk later.

Talāt

Hi Robert and anyone else who is willing to help out,

I tried Robert's suggestion above, but I guess due to my lack of VBA knowledge I did ot get very far. So I decided to have a different approach but this time I got stuck with the If/Elseif Statements. If anyone can sort me out with this, I think I am 90% there. I may get stuck again in the last part, but I will cry for help then.

Here is my Code which I think it will work once I sort out the if statements ( and add many more of them)

Thanks for your help and suggestions.

Talat

Sub PrintRoom()
     
   Application.ScreenUpdating = False
   
   Dim myRange      As String
   Dim InvNum        As Long     'Despatch Note Number
   Dim InvSheet      As Worksheet
   Dim PrintRmSheet  As Worksheet
   Dim NextRow       As Long     'the next available invoice row on the PrintRmSheet
   Dim oRow          As Long     'row number on PrintRmSheet
   Dim iRow          As Long     'row number on InvSheet
   Dim DestCol      As String    'Column Name on PrintRmSheet
   
   
   Set InvSheet = ThisWorkbook.Worksheets("INVOICE TEMPLATE")
   
   
    Workbooks.Open Filename:="G:\PUBS\PP-MS\INVOICES\PrintRmData.xlsx"
        
    Set PrintRmSheet = ActiveWorkbook.Worksheets("Sheet1")
   
   oRow = PrintRmSheet.UsedRange.Rows.Count + 1
   iRow = 20
 
      
    Do
     
    
        InvSheet.Range("K2").Copy  'DN Number
        PrintRmSheet.Cells(oRow, "A").PasteSpecial xlPasteValues
        InvSheet.Range("B6").Copy  'Section Name
        PrintRmSheet.Cells(oRow, "B").PasteSpecial xlPasteValues
       
        
      
      If InvSheet.Cells(iRow, "D") = "P4068EN" Then DestCol = "K"
      ElseIf InvSheet.Cells(iRow, "D") = "P4063EN" Then DestCol = "H"
      ElseIf InvSheet.Cells(iRow, "D") = "P4069MU" Then DestCol = "J"
 
      'Need to add more Else if conditions here
      
      End If
      InvSheet.Cells(iRow, "E").Copy
      PrintRmSheet.Cells(oRow, DestCol).PasteSpecial xlPasteValues
          
      
               
    
   
      iRow = iRow + 1
      'oRow = oRow + 1
   
   
   
   
   Loop Until IsEmpty(InvSheet.Cells(iRow, "D")) Or InvSheet.Cells(iRow, "D") = F
   
  
   
   
   Application.CutCopyMode = False
   ActiveWorkbook.Close True           'save changes and close
   
   Application.ScreenUpdating = True
     
End Sub