[HELP] Is it possible to copy & paste a table based on 3 dependant combo boxes?

[Demention]

Hi everyone,

I'm having some trouble trying to figure this out as my experience in excel is basically one big google search. I've been tasked with putting together a workbook for our vendors and I'd like to try and make it as easy for them as possible.

What I have is a workbook with 2 sheets. Sheet1, is where I'd like the user to stay, which has a commandbutton to display a userform. Sheet2 has some named ranges for the userform.

The userform has 3 dependant comboboxes. ComboBox1 RowSource = Manufacturer (named range located on sheet2). ComboBox2 and ComboBox3, I found some VBA code to fill in the the rest of the data from Sheet2.

Private Sub Manufacturer_Change()
Me.Model.RowSource = Me.Manufacturer
End Sub

ComboBox3 is the same code

Private Sub Model_Change()
Me.Options.RowSource = Me.Model
End Sub

On Sheet1, the button I have created opens the userform and clears a specific area.

Private Sub SelectionWindow_btn_Click()
Range("C3:J36").Clear
Selection_Window.Show
End Sub

I have a bunch of tables on Sheet1 which will be hidden from the user as I want to freeze the panes, stopping the user from scrolling down.

Now for my problem.

Within my userform I have another button called "Import". What I would like this button to do, based on the selection which the user made in all 3 comboboxes, I'd like them to click the import button which then copies the correlating table or range to the specific area I mentioned before.

E.g. If ComboBox1 = Toyota
ComboBox2 = Hilux
ComboBox3 = G
Then copy A1755:H1777 or named range to C3

These tables are located from A41 down. The other tables to the right I have other plans for.

My coding / VBA knowledge is virtually non-existent. So if anyone could be kind enough to show me an example which I can use and add to to accommodate about 80 different tables, I'd be incredibly appreciative and you'd be a lifesaver!

Thanks in advance!

**Note: I do have a thread open on Mr Excel.

***Added sample workbook with named ranges.

 

[Nebu]

Hi:

I am not clear about what you want to copy as per your uploaded file, there is nothing in the range A1755:H1777. Please explain how ComboBox1 = Toyota,
ComboBox2 = Hilux, ComboBox3 = G are related. Also explain what does the tables you created signify and what are the different shapes you have inserted on your Cover tab.

Thanks

 

[Demention]

Hi Nebu

I see what you mean, I made up an example on the spot and didn't look at the sample book.

ComboBox1 is the vehicle manufacturer
ComboBox2 is the model of vehicle
ComboBox3 is an Option what goes into the vehicle.

So each vehicle manufacturer has different models which all have their own set of options.

When the user selects:
ComboBox1 = Ford
ComboBox2 = Falcon
ComboBox3 = A
When the user then clicks the "Import" button, it then copies the table "FALCON_A" located at A41:H49 which is a list of items and pastes the table to C3.

The shapes in the tables labeled "ITEM 1" "ITEM 2" etc are purely representative of photos of said item. I placed the tables as I have it laid out in my master file.

Thanks for the swift reply!

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[Demention]

Wow. This is awesome. I've been staring at the code for a while now and had a bit of a test run.

As far as I can tell it reads the contents of the vehicle model cell and copies the table with it. Is there a possibility to add a 2nd cell to this? Reason being, with each vehicle model I have multiple options: A, A1, B, G, F2 etc. The option I've marked as the gold letter to the left of the Model heading in each table.
From what I've tested it takes the first table associated with the vehicle model. Unless I'm mistaken?

I still love how this works and can't get over it :D

Thanks so much

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[Demention]

That is exactly it.

Thanks so much for your help Nebu. This exercise has sparked an interest to take a course on VBA so I could do this on my own one day.

You're a champion!