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

exceldumbass

New Member
Joined
Jun 13, 2018
Messages
3
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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:

Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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"

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

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