Copy data from one sheet to another based on Drop-Down box selection

earwig999

New Member
Joined
Jan 11, 2013
Messages
42
Not sure how to explain this but here goes.

I have a workbook containing 2 sheets. On sheet 2 I have a drop down (select from list) which allows you to choose a month.

On sheet 1 I have the full list of data relating to Jan - Dec.

What I want to do is if I select eg. January from the drop down box on sheet 2, then copy the data selected from under the January section of sheet 1 to a section on sheet 2.

Is this possible, and if so how please?

(Would I have to create ranges in sheet 1 for each month and then somehow get that range to copy over to sheet 2 based on the drop down selection?)
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,727
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
We would need a little more information to help. Where on sheet 2 is the drop down list? Is your data in sheet 1 arranged in columns with Jan. in column A, Feb. in column B, etc.? Where on sheet 2 do you want to copy the data?
 

earwig999

New Member
Joined
Jan 11, 2013
Messages
42
We would need a little more information to help. Where on sheet 2 is the drop down list? Is your data in sheet 1 arranged in columns with Jan. in column A, Feb. in column B, etc.? Where on sheet 2 do you want to copy the data?

Sheet 2 (Power) has drop down (showing month names) in cell X1
Sheet 1 (Input) has the data for January in B8:D20 - February in E8:G20 - March in H8:J20 and so on
I want the data copying from the January range (B8:D20) in Sheet1 (Input) and pasting in Sheet2 (Power) to cells B6:D18 - But only when X1 in Sheet 2 has January selected. If I select February I want the February range (E8:G20) pasting in the same location.

Hope this helps you.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,727
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Place the following code into the code module for your worksheet "Power" (not into a regular module). Create you validation list in X1 of sheet "Power". When you choose from the drop down list, the data will be copied. Let me know how it works out.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("X1")) Is Nothing Then Exit Sub
        Select Case Target.Value
            Case "January"
                Sheets("Input").Range("B8:D20").Copy Sheets("Power").Range("B6")
            Case "February"
                Sheets("Input").Range("E8:G20").Copy Sheets("Power").Range("B6")
            Case "March"
                Sheets("Input").Range("H8:J20").Copy Sheets("Power").Range("B6")
            Case "April"
                Sheets("Input").Range("K8:M20").Copy Sheets("Power").Range("B6")
            Case "May"
                Sheets("Input").Range("N8:P20").Copy Sheets("Power").Range("B6")
            Case "June"
                Sheets("Input").Range("Q8:S20").Copy Sheets("Power").Range("B6")
            Case "July"
                Sheets("Input").Range("T8:V20").Copy Sheets("Power").Range("B6")
            Case "August"
                Sheets("Input").Range("W8:Y20").Copy Sheets("Power").Range("B6")
            Case "September"
                Sheets("Input").Range("Z8:AB20").Copy Sheets("Power").Range("B6")
            Case "October"
                Sheets("Input").Range("AC8:AE20").Copy Sheets("Power").Range("B6")
            Case "November"
                Sheets("Input").Range("AF8:AH20").Copy Sheets("Power").Range("B6")
            Case "December"
                Sheets("Input").Range("AI8:AK20").Copy Sheets("Power").Range("B6")
        End Select
End Sub
 

earwig999

New Member
Joined
Jan 11, 2013
Messages
42

ADVERTISEMENT

Thanks for this! I have put this in as you stated but it doesn't seem to trigger the copying of the data. I have had to amend the code slightly as I have altered the ranges, but it's as if I need to do something else to make it run?

** My mistake, sorry I had moved the dropdown cell from X1 to K1, changed it and it's working! :LOL:
 
Last edited:

earwig999

New Member
Joined
Jan 11, 2013
Messages
42

ADVERTISEMENT

just on more question on this -

If I wanted to have another drop down box to change different cells on the same 'power' sheet and keep the existing one how would I code this?

So replicate exactly what you have and copy and paste it beneath the other code but change the K1 ref to K40 and the ranges to match the new ones.
I tried this but it didn't like it. I tried adding an ELSE IF statement too, but no luck.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,727
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Hi earwig. Try this code:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("K1,K40")) Is Nothing Then Exit Sub
    If Target.Address = "$K$1" Then
        Select Case Target.Value
            Case "January"
                Sheets("Input").Range("B8:D20").Copy Sheets("Power").Range("B6")
            Case "February"
                Sheets("Input").Range("E8:G20").Copy Sheets("Power").Range("B6")
            Case "March"
                Sheets("Input").Range("H8:J20").Copy Sheets("Power").Range("B6")
            Case "April"
                Sheets("Input").Range("K8:M20").Copy Sheets("Power").Range("B6")
            Case "May"
                Sheets("Input").Range("N8:P20").Copy Sheets("Power").Range("B6")
            Case "June"
                Sheets("Input").Range("Q8:S20").Copy Sheets("Power").Range("B6")
            Case "July"
                Sheets("Input").Range("T8:V20").Copy Sheets("Power").Range("B6")
            Case "August"
                Sheets("Input").Range("W8:Y20").Copy Sheets("Power").Range("B6")
            Case "September"
                Sheets("Input").Range("Z8:AB20").Copy Sheets("Power").Range("B6")
            Case "October"
                Sheets("Input").Range("AC8:AE20").Copy Sheets("Power").Range("B6")
            Case "November"
                Sheets("Input").Range("AF8:AH20").Copy Sheets("Power").Range("B6")
            Case "December"
                Sheets("Input").Range("AI8:AK20").Copy Sheets("Power").Range("B6")
        End Select
        
    ElseIf Target.Address = "$K$40" Then
        Select Case Target.Value
            'your new ranges here
        End Select
    End If
End Sub
 

earwig999

New Member
Joined
Jan 11, 2013
Messages
42
Thanks for this but doesn't seem to want to work?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("K1,K40")) Is Nothing Then Exit Sub
    
    If Target.Address = "$K$1" Then
        Select Case Target.Value
            Case "January"
                Sheets("Input").Range("B8:H24").Copy Sheets("Power").Range("B6")
            Case "February"
                Sheets("Input").Range("I8:O24").Copy Sheets("Power").Range("B6")
            Case "March"
                Sheets("Input").Range("P8:V24").Copy Sheets("Power").Range("B6")
            Case "April"
                Sheets("Input").Range("W8:AC24").Copy Sheets("Power").Range("B6")
            Case "May"
                Sheets("Input").Range("AD8:AJ24").Copy Sheets("Power").Range("B6")
            Case "June"
                Sheets("Input").Range("AK8:AQ24").Copy Sheets("Power").Range("B6")
            Case "July"
                Sheets("Input").Range("AR8:AX24").Copy Sheets("Power").Range("B6")
            Case "August"
                Sheets("Input").Range("AY8:BE24").Copy Sheets("Power").Range("B6")
            Case "September"
                Sheets("Input").Range("BF8:BL24").Copy Sheets("Power").Range("B6")
            Case "October"
                Sheets("Input").Range("BM8:BS24").Copy Sheets("Power").Range("B6")
            Case "November"
                Sheets("Input").Range("BT8:BZ24").Copy Sheets("Power").Range("B6")
            Case "December"
                Sheets("Input").Range("CA8:CG24").Copy Sheets("Power").Range("B6")
        
        End Select
        
     ElseIf Target.Address = "$K$40" Then
        Select Case Target.Value
            Case "January"
                Sheets("Input").Range("P51:R68").Copy Sheets("Power").Range("L46")
            Case "February"
                Sheets("Input").Range("S51:U68").Copy Sheets("Power").Range("L46")
            Case "March"
                Sheets("Input").Range("V51:X68").Copy Sheets("Power").Range("L46")
            Case "April"
                Sheets("Input").Range("Y51:AA68").Copy Sheets("Power").Range("L46")
            Case "May"
                Sheets("Input").Range("AB51:AD68").Copy Sheets("Power").Range("L46")
            Case "June"
                Sheets("Input").Range("AE51:AG68").Copy Sheets("Power").Range("L46")
            Case "July"
                Sheets("Input").Range("AH51:AJ68").Copy Sheets("Power").Range("L46")
            Case "August"
                Sheets("Input").Range("AK51:AM68").Copy Sheets("Power").Range("L46")
            Case "September"
                Sheets("Input").Range("AN51:AP68").Copy Sheets("Power").Range("L46")
            Case "October"
                Sheets("Input").Range("AQ51:AS68").Copy Sheets("Power").Range("L46")
            Case "November"
                Sheets("Input").Range("AT51:AV68").Copy Sheets("Power").Range("L46")
            Case "December"
                Sheets("Input").Range("AW51:AY68").Copy Sheets("Power").Range("L46")
        End Select
    
    End If
           
    End Sub

</PRE>
 
Last edited:

earwig999

New Member
Joined
Jan 11, 2013
Messages
42
It's working now??

I think I might have removed a bracket. All good

Thanks Mumps
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,277
Messages
5,836,354
Members
430,422
Latest member
sandyandy5

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