Macro to AutoFill DIFFERENT rows each week

[mustang]

I have a fairly large spreadsheet with sections for each of 15 business units (BU). There is a column for Invoices, Receipts, Plan Sales, and Backlog for each BU, by week. There are three major sections of this worksheet (current week, four-week average, YTD).

Every Monday, I manually go into this workbook and AutoFill the formulas into the row for the prior week for each of these 45 separate sections...needless to say, this is very cumbersome. For instance, this Monday I had to AutoFill cells A200:D200, A250:D250, G200:J200, G250:J250, S325:V325, and S400:V400. Next Monday, I will have to AutoFill rows 201, 251, 326, 401, etc...

Is there a way to create a Macro that will adjust the cells it operates on every week?

 

[jimboy]

You can use this :

Range("A199:D199").End(xlDown).Offset(1, 0).Resize(1, 4).FillDown

Just change the cell address for the other cells.

What happens when you fill all the cells down to row 250 from 200?

 

[mustang]

OK, sorry for being a bit dumb, but I don't know what to do with that code

To answer your question...250 isn't necessarily the end of a particular section. But in any case, there is one row for each week of the year in a given section. For instance, A-D200:A-D-252 are for Unit A;
G-J200:G-J-252 are for Unit B;
A-D260:A-D-312 are for Unit C;
and G-J260:G-J-312 are for Unit C.

 

[jimboy]

Open VB (Alt-F11) and insert a module (Insert>Module) then type "Sub test" and press enter ("End Sub" will appear below - this is where the macro ends) - paste the code betwenn Sub test and End Sub.

To run it, close VB and press Alt F8 in Excel and select test and Run.

If it works you can create a button on the work sheet to run the macro.

 

[mustang]

Wow...amazing... I don't know just yet how much time that will save me, but probably at least an hour every week... Thank you!!

 

[mustang]

Digging up a dead post... This macro is now giving me problems. I've edited the w/s to remove all of 2006's formulas except for those for the past week. Now I expected the macro to fill the subsequent weeks, one at a time, as it did during 2006. But I had to create a whole new macro to get it to work right. Regardless, I'm down to two remaining problems that I need to iron out by Wednesday morning:

- When I run the macro, it malfunctions (but does not result in an error message) on 13 lines which all look like this

Range("AX1332:B384").End(xlDown).Offset(1, 0).Resize(1, 5).FillDown

These lines are supposed to copy AX333 and paste into AX334, etc. Instead, the macro copies AX331:BB331 and pastes it into AX332:BX332, etc. There are other lines in the macro that perform the same task on different ranges, and they all work fine (and are written exactly the same way).

- I have 19 lines that mirror each of the codes below (with different ranges) that perform perfectly. However, when the macro gets to either of the lines below, it results in a "Run Time 1004" error.

Range("AX1160:AX212").End(xlDown).Offset(1, 0).Resize(1, 5).FillDown
Range("BH1160:BI212").End(xlDown).Offset(1, 0).Resize(1, 5).FillDown

Any ideas???