VBA copy/paste data based off of column headers

ninjazor

New Member
Joined
Nov 8, 2019
Messages
19
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
 
Last edited by a moderator:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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.

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

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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
Back
Top