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

CtrlAltRage

New Member
Joined
Aug 23, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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):

Excel Formula:
=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!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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