VBA copying data from different columns

[kshipp91]

Hello!

I am hoping to get some help with an excel spreadsheet I have been working on, the first sheet is called PasteBOM and the second sheet is called Kitting.

When we paste a customers parts list into the PasteBOM sheet, the user can then use the drop down for the heading. Selecting the required heading i.e. Item Number, Designator, Part Number. We are doing it this way because each parts list can be different and so I don't want the user having to manipulate the data, only to paste and select a heading for each column.

I am struggling with writing VBA that after pressing a button, will copy the data in the column they have called "Designator" and pasting it into the Kitting sheet under the heading "Designator". The difficulty is that the parts lists being pasted into the PasteBOM sheet will not always be in the same order.

I have uploaded two images showing the two tabs and what they look like.

I would really appreciate any help or tips!

Thank you

 

[DanteAmor]

You can put another image of the Kitting sheet showing how the data would look after pasting.
And maybe a brief explanation if you require any special pasted or a pattern to follow to paste each item.

 

[kshipp91]

You can put another image of the Kitting sheet showing how the data would look after pasting.
And maybe a brief explanation if you require any special pasted or a pattern to follow to paste each item.

Thank you for your message. This is how I would like the data to pull through. I would like the macro to copy the column that matches each title and pull through the correct data that matches the heading if that makes sense? Just copy and paste if possible.

 

[DanteAmor]

The names of the headers must be exactly the same.
Try this and tell me.

Sub copying_from_columns()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim c As Range, f As Range
  Dim r As Long
  
  Set sh1 = Sheets("PasteBOM")
  Set sh2 = Sheets("Kitting")
  
  r = sh1.Range("A" & Rows.Count).End(3).Row - 1
  For Each c In sh1.Range("A1", sh1.Cells(1, Columns.Count).End(1))
    Set f = sh2.Rows(14).Find(c.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      f.Offset(1).Resize(r, 1).Value = c.Offset(1).Resize(r, 1).Value
    End If
  Next
End Sub

 

[kshipp91]

The names of the headers must be exactly the same.
Try this and tell me.

Sub copying_from_columns()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim c As Range, f As Range
  Dim r As Long
 
  Set sh1 = Sheets("PasteBOM")
  Set sh2 = Sheets("Kitting")
 
  r = sh1.Range("A" & Rows.Count).End(3).Row - 1
  For Each c In sh1.Range("A1", sh1.Cells(1, Columns.Count).End(1))
    Set f = sh2.Rows(14).Find(c.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      f.Offset(1).Resize(r, 1).Value = c.Offset(1).Resize(r, 1).Value
    End If
  Next
End Sub

This worked perfectly! Thank you so much for your help Dante

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.