VBA copy/paste data based off of column headers

[ninjazor]

Hi everybody

Long story short, I work for a construction company and I am in the process of making things much more efficient.
I have built a small database in Access and have a connection pulling all that data into my workbook.
Currently my database has 70 columns and will be expanded to around 200 when its done. With easily 2000 rows when done aswell.
I have built a basic selector with some parameters to pick something out of the database.
I have then got that selection pulling all the info for that particular item from the database using xlookup.

Now I want to create a macro that grabs(copy) info from this and pastes it to 1 of 4 sheets dependant on the data. I would like to be able to have it copy and paste the data to corresponding column headers. For example width to width. drawer box size to drawer box size. Obviously I have to point it to the correct sheet but I can't find any code that simplifies just copying data from one column to its matching column on another page.

Any help would be great. Hope I explained this well enough as I am not a VBA expert.
Here's some pics to hopefully make things easier to understand.


https://imgur.com/a/qBCyUwz
https://imgur.com/a/LoLu4bV

 

[ninjazor]

i pm'ed you a dropbox link

 

[DanteAmor]

Try this

In the "Cut List - Boxes" sheet you have a table, the macro is putting the data to the last row of the table.
I made some adjustments to the macro.
In the sheet "Build" is the code, I put the combobox1, I added the list of codes to the combobox1.
Select a code and press the button.

Private Sub CommandButton1_Click()
  Dim sh As Worksheet, sh1 As Worksheet, sh2 As Worksheet
  Dim i As Long, lc As Long, lr As Long, wRow As Long
  Dim vl As Variant, f As Range, c As Range
 
  Set sh = Sheets("Build")              'sheet with combobox
  Set sh1 = Sheets("Database")          'origin
  Set sh2 = Sheets("Cut List - Boxes")  'destination
 
  vl = Sheets(sh.Name).ComboBox1.Value
  If vl = "" Or Sheets(sh.Name).ComboBox1.ListIndex = -1 Then
    MsgBox "Select value"
    Exit Sub
  End If
 
  Set f = sh1.Range("A:A").Find(vl, , xlValues, xlWhole)
  If f Is Nothing Then
    MsgBox "Code does not exists"
  Else
    'Compare header and 'copy value
    For i = 1 To sh1.Cells(1, Columns.Count).End(xlToLeft).Column
      Set c = sh2.Rows(1).Find(sh1.Cells(1, i), , xlValues, xlWhole)
      If Not c Is Nothing Then
        If i = 1 Then
          lr = 1
          Do While sh2.Cells(lr, c.Column) <> ""
            lr = lr + 1
          Loop
        End If
        sh2.Cells(lr, c.Column).Value = sh1.Cells(f.Row, i).Value
      End If
    Next
  End If
  MsgBox "End"
End Sub

 

[ninjazor]

Debug doesn't like this line

vl = Sheets(sh.Name).ComboBox1.Value

 

[ninjazor]

Nevermind. Just tried the file you sent me. It works

 

[ninjazor]

Awesome! Thank you so much. Its very much appreciated. I am going to do some more work on the test sheet to enter more info into the other sheets and see if I can get it to work across all 4 of them. Let you know if I run into any other issues

 

[DanteAmor]

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