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?)
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,111
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
10,111
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
10,111
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,631
Members
414,082
Latest member
sasmita

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