VBA Copy-Paste based on Drop-Down menu selection in seperate worksheeet

codingnewbie

New Member
Joined
Feb 23, 2017
Messages
2
I'm just learning to improve my VBA skills. Made lots of progress yesterday but now I find myself faced with a momentary wall...

My dilema:

I have two sets of data in Sheet2: one set in array AT1:BU2000 and the second in array AB1:AR2000.
There is a drop-down menu in Sheet 1 containing a list of 7 text values.
If the menu contains these values: 30x20, 50x20, 180x20 or SL, then I need the values in Sheet 2 array AB1:AR2000 to be copied to Sheet 3 cell AS 1.
Otherwise, if the drop-down menu is selected to one of the 4 remaining options (50x20, 130x70, 150x50), them the code should copy values in Sheet2 array AT1:BU2000 to Sheet3 cell AS1.
Also, values (not formulas) should be copied to Sheet 3

Could anyone please help?

Thank you in advance!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
This is what I've come up with so far:

Sub Copy40xs10()
'Copy the data
score = Sheets("Input").Range("D19").Value
If score = "30 xs 20" Or "50 xs 20" Or "180 xs 20" Or "Stop-Loss" Then
Sheets("Layer Generator").Range("AT1:BJ2000").Copy
'Activate the destination worksheet
Sheets("Program").Activate
'Select the target range
Range("AS1").PasteSpecial xlPasteValues
Else
Sheets("Layer Generator").Range("AB1:AR2000").Copy
'Activate the destination worksheet
Sheets("Program").Activate
'Select the target range
Range("AS1").PasteSpecial xlPasteValues
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

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