VBA Copy/Paste Based on Drop Down Value that Replaces Existing Paste if Value Changes

**Ctrl-Alt-Rage** · 08-30-2022, 10:09 AM

Hey all,

I am hoping I can get some guidance. VBA is something I just started getting my feet wet with so I'm really new to it. I will do my best to explain what I'm trying to accomplish.

I've created a spreadsheet that has a primary sheet and 4 additional sheets.

The first sheet has several drop down menus in column B that have the same 5 options on each of them:

Drop Down 1 - (None, Basic, Pro, Premium, Enterprise)
Drop Down 2 - (None, Basic, Pro, Premium, Enterprise)
Etc.

The additional sheets are listed as the names of the drop down menu options.

Sheet 2 = Basic
Sheet 3 = Pro
Sheet 4 = Premium
Etc.

Each of those sheets have multiple "categories" of data.

Sheet 2 - Basic Option 1, Basic Option 2
Sheet 3 - Pro Option 1, Pro Option 2
Etc.

My goal is to be able to select an option from each drop down in sheet 1 and have it populate a range of data based off the specific drop down being used and copy the specified range to the next blank row in column E of sheet 1.

Currently - the functionality works, but I am trying to refine it a bit to be more efficient. The issue right now is if I select Drop Down 1 and choose "Basic" it populates the data as intended; but if I select Drop Down 1 again and choose "Pro", it populates the data under the data that was just populated.

I'm trying to get it to work so that the data from each drop down can only be populate once, and if the value of that drop down changes - it replaces the data that drop down originally populated.

Something to note - the ranges of the options for each drop down vary in row size (but not column size) so Drop Down 1-Option 1 may have a range of A2:G12 but Option 2 would have the range of A2:G15.

Lastly - if the option "None" is selected on any of the drop downs - it would remove the copied range.

Is there a way to do this?

Here is the script I've written. It works perfectly fine - but I know full well it could be improved.

This the code in the Module:

Please Login or Register  to view this content.

This is the code in the Worksheet:

Please Login or Register  to view this content.

Thank you in advance for anyone who can offer help.

**Mumps1** · 08-30-2022, 11:00 AM

It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file.

**Ctrl-Alt-Rage** · 08-30-2022, 11:30 AM

Thanks for the reply. Here is the attached file.

I'm trying to make it so that when a drop down option is selected in Sheet 1 (VBA Automated Comparison) in column B2, B3, B4 or B5 it populates the data corresponding to a specified range from the reflected sheets.

Example:

If the Dropdown(Option1) in B2:
= Basic - it would populate data from the range A2:G11(Basic Option 1) from Sheet (Basic) to column E in the next blank row.
= Pro - it would populate data from the range A2:G12(Pro Option 1) from Sheet (Pro) to column E in the next blank row.
= Premium - it would populate data from the range A2:G14(Premium Option 1) from Sheet (Premium) to column E in the next blank row.
= Enterprise - it would populate data from the range A2:G17(Enterprise Option 1) from Sheet (Enterprise) to column E in the next blank row.
= None - it would not populate anything, or if data already exists from selecting any of the options in Dropdown(Option1), it would remove that data.

If the drop down value changes (from basic to pro, etc) then the previous data that was populate would be removed and replaced with the new data.

If the Dropdown(Option2) in B3:
= Basic - it would populate data from the range A13:G21(Basic Option 2) from Sheet (Basic) to column E in the next blank row.
= Pro - it would populate data from the range A14:G23(Pro Option 2) from Sheet (Pro) to column E in the next blank row.
Etc. etc.
= None - it would not populate anything, or if data already exists from selecting any of the options in Dropdown(Option2), it would remove that data.

If the drop down value changes (from basic to pro, etc) then the previous data that was populate would be removed and replaced with the new data.

The same logic would go with all of the other drop down options.

I believe once I have an idea of how the logic would work for the first two drop down selections and how I could get it so that the data is replaced when the values changed or removed when "none" is selected, I could get the rest figured out.

VBA Replace - Example.xlsm

**Mumps1** · 08-30-2022, 02:09 PM

Replace the current macro in the code module for your sheet "VBA Automated Comparison" with the one below. Please note that you no longer need the macros in Module1.

Please Login or Register  to view this content.

**Ctrl-Alt-Rage** · 08-30-2022, 04:50 PM

Thank you kindly for your assistance!

The functionality works fairly close to what I'm going for but I'm encountering a few issues:

- If I select "Basic" in a dropdown, it populates as intended. But if I change it "Pro" in the same dropdown, it leaves one extra row at the top of the data that was populated (screenshot included)

Attachment 794137

- If I add data two columns after where the data would populate and change a value from the same drop down, it does a funky offset thing.

(Before: When selecting Basic in dropdown1 - working as intended)

Attachment 794139

(After: When selecting a different value in the same dropdown)

Attachment 794140

Lastly, is there a way to remove the data the dropdowns reference when selecting "None" as an option?

Again - you're amazing and I REALLY appreciate you helping. I wish I could get my head around VBA faster but it's only been a few days for me.

**Ctrl-Alt-Rage** · 08-31-2022, 10:47 AM

I just wanted to say thank you very much for all of your help with this.

I was overcomplicating things and I ended up using pieces of your formula with mine and I just created a "Submit" and "Reset" button on the first sheet.

The submit populates the data based on the drop down and then reset just clears those columns.

**Mumps1** · 08-31-2022, 11:15 AM

This version should fix the "extra row" problem and will delete the data when you select "None".

Please Login or Register  to view this content.

I couldn't fix the problem regarding "add data two columns after where the data would populate" because your links don't work. If you attach a revised file showing the added data, I could have a look at the problem and possibly suggest a fix.

VBA Copy/Paste Based on Drop Down Value that Replaces Existing Paste if Value Changes

LinkBack

Thread Tools

Rate This Thread

Display

VBA Copy/Paste Based on Drop Down Value that Replaces Existing Paste if Value Changes

Re: VBA Copy/Paste Based on Drop Down Value that Replaces Existing Paste if Value Changes

Re: VBA Copy/Paste Based on Drop Down Value that Replaces Existing Paste if Value Changes

Re: VBA Copy/Paste Based on Drop Down Value that Replaces Existing Paste if Value Changes

Re: VBA Copy/Paste Based on Drop Down Value that Replaces Existing Paste if Value Changes

Re: VBA Copy/Paste Based on Drop Down Value that Replaces Existing Paste if Value Changes

Re: VBA Copy/Paste Based on Drop Down Value that Replaces Existing Paste if Value Changes

Thread Information

Users Browsing this Thread

Similar Threads

Create macro to copy-n-paste all data from an existing worksheet to another existing works

Create macro to copy-n-paste all data from an existing worksheet to another existing works

Copy column based on partial word match in first row and paste into existing workbook

Copy column based on partial word match in first row and paste into existing workbook

Copy and paste multiple cells based on drop down

[SOLVED] Cut (Not Copy)and Paste row from one sheet to another based on a drop down value in a cell

[SOLVED] copy multiple worksheets of a workbook, and paste onto a Word document ( either create new doc file or paste onto an existing file.) I need this done by VBA, Excel Macro

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions