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!
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

codingnewbie

New Member
Joined
Feb 23, 2017
Messages
2
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

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
Top