Copy data range to another range on the same sheet if criteria is met

[Grizz]

Hi,

I have a range of data (C3:S36) that corresponds to a particular week. The week number is in cell A5. The range C3:S36 on my master file contains INDEX MATCH formulas bringing summary data in from another sheet and changes depending on the week selected in the other sheet from a dropdown. I have removed the formulas in the example file attached

What I would like to do is copy the the values from range (C3:S36) with code and paste it into pre-defined ranges below. So, if for example in my attached file the week number in cell A5 is 36 it would look down column A until it finds the matching week number (Cell A1263) and automatically copy range C3:S36 into range C1263:S1296.

Thanks very much for any help on this, my VBA knowledge is sadly lacking I'm afraid. I aim to do the Chandoo.org VBA course at some point soon but at the moment my work commitments won't allow me enough time unfortunately. I do appreciate any help you can give me.

Thanks

Grizz

[nilem]

Hi Grizz,
try it

Please Login or Register  to view this content.

[Grizz]

Hi nilem,

That worked great when I first tried it, it populated week 36's data C1263:S1296, then I tried Week 37 and I'd gone down to Week 37 already to see the data come in and again it worked. Then I typed in 38 to Cell A5 and then went down and there was nothing there. I looked at the code and realised it was getting the selected week from the combo box which I had called WEEK (I was just using that to navigate to the range). I would like it to get the week number from Cell A5 as that will automatically change when the user selects the week in the other sheet and all the data changes in the range C3 to S36. I guess instead of looking at the value in the combo box it needs to look at the the value in Cell A5, how would I change the code to do that?

Also, is it possible that I could put the code on a 'sheet change event' when cell A5 changes even though it won't be the active sheet when the week is selected on the other sheet? I'm trying to make things happen as automatically as possible as the workbook is for other people to use.

Thanks for your help..

[nilem]

Ok, then try to change the value in cell A1 on "Sheet1" and look at your "Weekly Summary1" on the corresponding row

[Grizz]

Hi nilem,

Sorry for the late reply...I had a rugby game to go to yesterday afternoon. Your solution works perfectly for what I need to do, thank you very much. I'll now study your code and see if I can work out what's going on to do that. I will mark the thread as solved and give you your deserved REP but would you mind if after looking at your code I ask you any questions about it, I am keen to learn VBA?