Macro to AutoFill DIFFERENT rows each week


New Member
Dec 27, 2005
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?

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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?
Upvote 0
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.
Upvote 0
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.
Upvote 0
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!!
Upvote 0
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???
Upvote 0

Forum statistics

Latest member

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
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 "".
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