VBA copying data from different columns

kshipp91

New Member
Joined
May 12, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Kitting.png
    Kitting.png
    32.2 KB · Views: 10
  • PasteBOM.png
    PasteBOM.png
    105.8 KB · Views: 10

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,984
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
May 12, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Complete.png
    Complete.png
    52.9 KB · Views: 8

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,984
Office Version
  1. 2007
Platform
  1. Windows
The names of the headers must be exactly the same.
Try this and tell me.

VBA Code:
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
 
Solution

kshipp91

New Member
Joined
May 12, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
The names of the headers must be exactly the same.
Try this and tell me.

VBA Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
13,984
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,148,250
Messages
5,745,645
Members
423,965
Latest member
visionquest1972

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top