VBA - Copy/Paste cell range from another sheet to target sheet if specific cell on target sheet equals value.

[CtrlAltRage]

Right now, I have a workbook that contains multiple sheets. The first sheet has 4 drop down menus where you select a specific option. Then in another cell, I have an IF formula that will populate several referenced cells from another sheet in the workbook. Each drop down menu populates data and can be a mix and match.

The idea I'm trying to accomplish is:

In Sheet1, use drop down menu in B2 and select "Basic"
D2 on Sheet1 populates data in cell ranges A1:G15 from Sheet2
In Sheet1, use drop down menu in B3 and select "Premium"
On Sheet1 the next blank row in column D populates with cell ranges A17:G25 from Sheet3
In Sheet1, use drop down menu in B4 and select "Basic"
On Sheet1 the next blank row in column D populates with cell ranges A45:65 from Sheet2
etc. etc.

The code that I use is below (forgive me if I made it unnecessarily long hah):

=IF('Automated Comparison'!B2="Basic",'Basic (For Reference)'!A2:G11,IF('Automated Comparison'!B2="Pro",'Pro (For Reference)'!A2:G12,IF(B2="Premium",'Premium (For Reference'!A2:G14,IF(B2="Enterprise",'Enterprise (For Reference)'!A2:G17))))

I've used conditional formatting to make sure the referenced cells look the way the they do on the other sheet, but each value in the drop down menu references cells that contain different ranges (column range is the same but rows are not) so I have to place the formulas further apart so they do not get overlap, which causes the sheet to look a little barren depending on the selections.

Ideally, I'd like to use VBA to create a copy paste function and paste the cell range based on the value of the drop down menus, but automatically find the next blank row.

I'm sure there's a way to do this - but VBA really confuses me at times.

Thanks in advance!