Copying moving range based on moving cell reff.

**Varmark** · 05-19-2011, 01:28 AM

Hi all,

I am in a bit of a pickle on this one, so here it goes:

I would like to copy a range from the 'Planning' sheet into a 'Forecast' sheet.
In the planning sheet, column A is a mix of item numbers (e.g. 12-56-40) and week numbers (e.g. Week 2). The item numbers that are before the week number are a group of items for that week.
The week numbers are ascending, but they do not necessarily start in week 1.

The Forecast sheet only holds the week numbers from week 1 until 52 in every third column (A1:EZ1).

When the macro is enabled, it should find the first week number in the 'Planning' sheet, and copy the data that belongs to the same week in column D and P, and paste it below the same week number in the 'Forecast' sheet.

I have been around the world wide, and have only come up with the following:

Please Login or Register  to view this content.

Shortcomings: - 1 If there is a week in one of the sheets that is not in the other it will return an error -2 It does not copy the range that I would like (haven't figured out how to set the moving range on that one)

It is properly completely the wrong approach to solve my issue, but it is the best I have come up with so fare from piecing together what I wound in forums.

attached should be an example

Thanks in advance

**watersev** · 05-19-2011, 04:02 AM

hi, Varmark, please check attachment, run code "test"

**Varmark** · 05-19-2011, 04:27 AM

Amazing! Thank you tons, works like a charm! I did think I was on the wrong track. I will spend the next couple of hours figuring out 'how' it works, cheers Watersev