VBA copy/paste range based on two dropdowns

thomasuponor

New Member
Joined
Sep 13, 2018
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
I have 2 dropdowns and would like to copy/paste different ranges based on selected values from the dropdowns (I know how to copy/paste but not based on dropdowns).

First dropdown values:
-BD
-AO
-LM

Second dropdown values:
-UFH
-PEX
-MLC

Ranges:
BD UFH = Sheet10 B2:C10
BD PEX = Sheet10 D2:E10
BD MLC = Sheet10 F2:G10

AO UFH = Sheet11 B2:C10
AO PEX = Sheet11 D2:E10
AO MLC = Sheet11 F2:G10

LM UFH = Sheet12 B2:C10
LM PEX = Sheet12 D2:E10
LM MLC = Sheet12 F2:G10

Any tips on how to do this?
Thx
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Here's an example, I hope it helps.

VBA Code:
Sub copy_paste_range()
  Dim combo As String
  Dim rng As Range
  
  With Sheets("Sheet1")   'Sheet with dropdowns
    'cells with dropdowns
    combo = .Range("B2").Value & "|" & .Range("C2").Value
     
    Select Case combo
      Case "BD|UFH": Set rng = Sheet10.Range("B2:C10")
      Case "BD|PEX": Set rng = Sheet10.Range("D2:E10")
      Case "BD|MLC": Set rng = Sheet10.Range("F2:G10")
      
      Case "AO|UFH": Set rng = Sheet11.Range("B2:C10")
      Case "AO|PEX": Set rng = Sheet11.Range("D2:E10")
      Case "AO|MLC": Set rng = Sheet11.Range("F2:G10")
      
      Case "LM|UFH": Set rng = Sheet12.Range("B2:C10")
      Case "LM|PEX": Set rng = Sheet12.Range("D2:E10")
      Case "LM|MLC": Set rng = Sheet12.Range("F2:G10")
    End Select
    
    If Not rng Is Nothing Then
      rng.Copy    'copy range
    End If
    
    .Range("G2").PasteSpecial xlPasteAll
  End With
  
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

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