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]

Awesome. Thank you so much.

1 last question. Is there a way to get it to not copy a given spot if the value is 0 without breaking anything?

 

[DanteAmor]

1 last question. Is there a way to get it to not copy a given spot if the value is 0 without breaking anything?

You have to explain with examples, images, etc.

Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[ninjazor]

Sorry I should have been more clear.

So as I said above I work for a ******* cabinet company and this is a work around for making things translate from our design team to our manufacturing team much easier and more efficiently.
The database has essentially every conceivable non-custom cabinet in it. Custom stuff we can just do by hand. Not a big deal to not have it included here.
So imagine you have a ******* sink cabinet. It is specific in its construction for a sink to be installed and have access for things like plumbing/electrical.
Sink cabinets for the most part do not have drawers in them. Just a pair of doors you can open up.
If you look at the the database img I put up(for quicker referencehttps://imgur.com/a/VMM6XfC) you will see a lot of the parameters for any given cabinet are zero because it does not need those in its construction but another type of cabinet may.
For example a sink cab has 2 doors compared to a 4 drawer cabinet which would have 3 6" drawers, 1 12" drawer and no doors.

So as the macros creeps through my data copying matching cell to matching cell, if the cells value is 0(zero), can I get it to skip that cell so my table is only full of relevant data when I goto print it?

Edit - For some reason its censoring the word KIT CH EN

 

[DanteAmor]

Thanks for the explanation and for the context.


But to modify the macro for your requirement, you must be clear in the example.
You must specify the data of your example.
Something like, in cell F9 there is a 0 (zero), then if in cell F9 there is a 0, the macro must do the task1, otherwise if in cell F9 there is no 0 then the macro must do the task2.

By the way, take into account that the macro does not review data by data in each column.
What the macro does is find the header and copy the entire column from the source header to the destination header.

 

[ninjazor]

So there wouldn't be a way to setup some sort of filter system? Or am I looking at making a completely different macro for something like that?

 

[DanteAmor]

I still don't understand what you want.
You must explain with an example, the example must be in the images.
The images must be supported with a clear and concise explanation of what you need.
I can't give you an answer if I need to modify the macro or create a new one.

 

[ninjazor]

The areas I have outlined have zeros in some of the cells.
I don't want to copy zeros, for the simple fact that these tables get quite big and this increases readability when in the production shop.
Is there a way to either:
1: To not copy zero values?
2: Or go through afterwards and eliminate/filter out any zeros that were copied?

https://imgur.com/a/A01mqG8

 

[DanteAmor]

Instead of 0 do you want the empty cell?


Try this

Sub copy_paste_data_based_column_headers()
  Dim sh1 As Worksheet, sh2 As Worksheet, a() As Variant, b() As Variant
  Dim i As Long, j As Long, lr As Long, lc As Long, lr2 As Long
  
  Set sh1 = Sheets("Database")          'origin
  Set sh2 = Sheets("Cut List - Boxes")  'destination
  
  'last row on origin sheet
  lr = sh1.Range("A" & Rows.Count).End(xlUp).Row
  'last row on destination sheet
  lr2 = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
  
  'Store headers in the "a" variable of the origin sheet
  lc = sh1.Cells(1, Columns.Count).End(xlToLeft).Column
  a = WorksheetFunction.Transpose(sh1.Range("A1", sh1.Cells(1, lc)).Value)
  
  'Store headers in the "b" variable of the destination sheet
  lc = sh2.Cells(1, Columns.Count).End(xlToLeft).Column
  b = WorksheetFunction.Transpose(sh2.Range("A1", sh2.Cells(1, lc)).Value)
  
  For i = 1 To UBound(a, 1)
    For j = 1 To UBound(b, 1)


      'Compare header
      If b(j, 1) = a(i, 1) Then
        'copy the column
        sh2.Cells(lr2, j).Resize(lr).Value = sh1.Cells(2, i).Resize(lr).Value
        Exit For
      End If
      
    Next
  Next

[COLOR=#0000ff]    sh2.Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole, _[/COLOR]
[COLOR=#0000ff]        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _[/COLOR]
[COLOR=#0000ff]        ReplaceFormat:=False[/COLOR]



  MsgBox "End"
End Sub

 

[ninjazor]

Yes just leave empty cells.

This macro doesn't copy anything. It does however remove the zeros.

The first macro you gave me works perfectly but doesn't move down to the next row.
The second macro was working yesterday but now won't run. Sorry I don't know why, I can try and screenshot whats going on.
This new one(the third one) doesn't copy anything but does remove the zeros if there is data already in the cells.

I don't know how to better explain this. I will try and do a bunch of screen grabs for you to better explain stuff if that doesn't make sense

 

[DanteAmor]

The difference between the previous macro and this new one is only the blue line to replace the 0 by empty.

I still don't understand what you need.
You must put a screen with the source data and on another screen the destination as you want it.
And explain the examples of the screens.
Try to put 4 examples.
Since in your screens you only put an example.