Copy Row of Data from one sheet to another sheet based on a Drop Down Value

[exceldumbass]

Hoping someone can help...

I have a workbook with individual sheets named January, February, March etc through to December. I have another tab named 'Hive DR'.

In the monthly tabs (January, February etc) I would like to copy Row 15, 26, 36, 40 and 41 from Column D through to AH and paste this data in the 'Hive DR' sheet into Row 10, 11, 12, 13 and 14 from starting in column C through to AG however I would like this based on a Drop Down box which I have created in B7 with each month on the Drop Down list.

For example if B7 shows 'January', Get rows 15, 26, 36, 40 and 41 from Columns D to AH on the 'January' sheet and copy to 'Hive DR' into Rows 10, 11, 12, 13 and 14 from column C to AG.

I hope someone can help with some form of code/formula I can use for this.

My Excel knowledge is not great so any help is really appreciated!

Thanks

 

[MisterProzilla]

Hi there,

You'll probably want to use VBA. If you're not sure how to set up a macro let me know and I can walk through it.

This should do what you need, assuming that the month names in your dropdown are exactly the same as the sheet name months:

Sub CopyMth()


Dim Mth As String
Mth = ThisWorkbook.Sheets("Hive DR").Range("B7").Value


Sheets(Mth).Range("D15:AH15").Copy Destination:=Sheets("Hive DR").Range("C10:AG10")
Sheets(Mth).Range("D26:AH26").Copy Destination:=Sheets("Hive DR").Range("C11:AG11")
Sheets(Mth).Range("D36:AH36").Copy Destination:=Sheets("Hive DR").Range("C12:AG12")
Sheets(Mth).Range("D40:AH40").Copy Destination:=Sheets("Hive DR").Range("C13:AG13")
Sheets(Mth).Range("D41:AH41").Copy Destination:=Sheets("Hive DR").Range("C14:AG14")

end Sub

 

[exceldumbass]

Hi Mister Prozilla,

Thank you for such a quick response and sparing your time to help me with this! I really appreciate it.

I was unsure how to add the code however I have found out pressing Alt+F11 to get Visual Basic, I then copied your code and pasted this in and then saved as a Macro-Enabled Workbook but this isn't doing anything when I change the content of field B7.

Do I need to do something else to enable this script to be running or could there be a slight error in the script possibly?

I do not mean to be doubting your script so apologies but maybe just a possibility?

Thanks

 

[MickG]

Try this:-
This code changes results Based on Sheets "Hive DR" cell "B7"
To save and Run code :-
Right click Sheet tab of Hive DR, select "View Code" Vb window appears.
Paste code into VBwindow
Close Vbwindow
To run code select a Sheet from your List of Sheet in cell "B7"

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "B7" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Sheets(Target.Value).Range("D15:AH15,D26:AH26,D36:AH36,D40:AH40,D41:AH41")
    Rng.Copy Sheets("Hive DR").Range("C10")
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[exceldumbass]

Hi Mick,

Thank you very much Mick, this has worked wonderfully exactly how I hoped!!

Regards
ExcelDumbass

 

[MickG]

You're welcome