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

[earwig999]

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

 

[mumps]

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]

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]

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.

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]

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!

 

[mumps]

Glad it worked out.

 

[earwig999]

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]

Hi earwig. Try this 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]

Thanks for this but doesn't seem to want to work?

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>

 

[earwig999]

It's working now??

I think I might have removed a bracket. All good

Thanks Mumps