VBA / Macro for Pasting Special Values on ranges with certain column conditions

[scouseboy]

I have a workbook that contains numerous sheets / tables, each of which has a date column and a week column alongside a range of further columns full of data (mainly links to cells in other workbooks). I need a macro / code that will paste special values over the range of data that falls in the following week (so if I run it in Week 8, it pastes values for all data in the rows when the Week = 9). There's a row for each date (and so seven rows for each week) if that information helps at all.

Preferably I'd like one macro that did the job on each of the appropriate sheets in the workbook (which is not all of the sheets, as some need to remain live). These sheets will always be the ones to be frozen, no need for selectors. The data ranges on each of these sheets is different (in columns across, not in rows down, as the rows will always be seven for the week).

Any help would be very much appreciated!

 

[sijpie]

Let me rephrase:

• You have a number of sheets with data, 7 rows to a week, varying number of columns
• The data on these sheets is in formulas
• You want to copy the the data and paste as value, but
• you want to paste the value over the next week.

Questions:

1. Do you want to carry out the copy paste just once, on all sheets? or
2. Do you want to do this week by week on a weekly basis?
3. If this is week 8 and I copy this weeks values to week 9, then what happens when it is week 9??
4. How does week 1 get filled?

 

[scouseboy]

Thanks for your response!

To clarify:

• You have a number of sheets with data, 7 rows to a week, varying number of columns -- CORRECT
• The data on these sheets is in formulas -- The data on these sheets are mostly links to cells on another sheet, with some formulas.
• You want to copy the the data and paste as value, -- CORRECT -- but
• you want to paste the value over the next week. -- Nope. I want the macro to paste the values of the NEXT week over the next week. So, if I run the macro in Week 8, it will paste the values of Week 9 over the links / formulas in Week 9.

And...

1. Do you want to carry out the copy paste just once, on all sheets? or
2. Do you want to do this week by week on a weekly basis? -- Preferably I'd run the macro once a week (eg in Week 8), and it would paste values over the current week + 1 (eg Week 9), on all the nominated sheets. I'd also be happy with running multiple macros, one per sheet, as the columns and ranges are different sizes for each sheet.
3. If this is week 8 and I copy this weeks values to week 9, then what happens when it is week 9?? - Week 9 would be pasted values over week 9 (when I ran the macro on Week 8). In Week 9, I run the macro and it pastes values of Week 10 over the formulas and links in Week 10.
4. How does week 1 get filled? -- It's basically a quarterly forecast report that is updated every twelve weeks. So in Week 1, we have forecast data for weeks 1-12. Each week, I need to "freeze" the following week's forecast as the final version, which can be measured against the actual figures found in other sheets in the workbook.

Thanks again for your help, hope it makes more sense now.

 

[sijpie]

Great, thanks for the explanation. I will work on it next week. Away for a few days...

 

[sijpie]

Just an additional question:

• How do I recognise the correct column? Are the columns labelled Week1 etc?
• Is there only one column per week or more? (screenshot)

Day	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

 

[sijpie]

Can you show a screenshot of a sheet?