How to copy formula across multiple worksheets but in different cell locations?

I have a formula on Sheet1/cell D330 and I want to paste the exact formula across several worksheets at once. Although every worksheet will need this formula in column D, the row will be different for every worksheet (which is why I can’t simply highlight all worksheets simultaneously and copy/paste). On each worksheet, the formula needs to go after the last populated cell in column D.

For example, I need the same formula from Sheet1/cell D330 pasted to Sheet2/cell D202 and Sheet3/cell D121 (because Sheet2/cell D201 and Sheet3/cell D120 are already populated with data).

Here’s my current process, which I’m hoping can be improved upon: I put a formula on Sheet1 in cells D330, D331, D332 (different formulas in each cell). I copy rows 330, 331 and 332, then go to Sheet2 and find the last row of data (row 201), then paste into cell A202. I manually repeat the same process on Sheet3, Sheet4, etc.

Is there a more efficient way to do this, preferably using formulas or ASAP Utilities (or another add-in), though I’m familiar with basic VBA as well. Thanks in advance!

I think the efficient way to do this is with VBA. Try the following:

Please Login or Register  to view this content.

Thanks for the quick reply, but I'm having trouble applying this code. I thought I could provide a general idea of my workbook setup then modify the code as needed; looks like I should have been more specific! Here's my exact current process, would appreciate your help trying to adapt the VBA:

On sheet "AA", I put the following new data in the specified cells:
A330: "January" | B330: "2016" | C330: =C329*(1+(D330/100)) | D330: =VLOOKUP(REPLACE(CELL("filename",$A$1),1,FIND("]",CELL("filename",$A$1)),""),Mnthly,4,0)
A331: "February" | B331: "2016" | C331: =C330*(1+(D331/100)) | D331: =VLOOKUP(REPLACE(CELL("filename",$A$1),1,FIND("]",CELL("filename",$A$1)),""),Mnthly,5,0)
A332: "March" | B332: "2016" | C332: =C331*(1+(D332/100)) | D332: =VLOOKUP(REPLACE(CELL("filename",$A$1),1,FIND("]",CELL("filename",$A$1)),""),Mnthly,6,0)

Now sheet AA is complete. I copy rows 330, 331 and 332, then go to the next sheet named "NHYF" and find the last row of data (row 201), then paste the three rows starting in cell A202. I manually repeat the same process for the next 103 sheets, which each have different names (I have another sheet called "Code Listing" with all the tab names in one column if that helps). And 3 months from now, in early July, I'll update each sheet with the next three months of data: sheet AA, I'll start in cell A333: "April", etc, etc.

I hope this is clear. Thanks again for the help!

Give the procedure below a try. It should take A:D of the last three rows on the first sheet and copy them to A:D at the end of every subsequent sheet. Let me know how it works...

Please Login or Register  to view this content.

Originally Posted by cantosh

Give the procedure below a try. It should take A:D of the last three rows on the first sheet and copy them to A:D at the end of every subsequent sheet. Let me know how it works...

Please Login or Register  to view this content.

This worked perfectly, thank you very much! Saved me lots of time and potential human error, and I learned a bit more about VBA.