A macros that will repeat a sheet. The row number will change in the formula each new shee

Hey guys,
I think I got the basics of a macros down. What I'm wanting to know how to do, or if it can be done is repeat a sheet over and over many times. Let's say the formula in Sheet1 cell B5 is ='Sheet25'N23. (That's probably not the correct formula, but,,,). What I need to do is for Sheet 2 cell B5 to have that same formula, but, I need it to change the row number in the formula to N22. Each new sheet I make will go up a row in N. Sheet 3 will use N21 and so on.


Is this something that is terribly hard to do? If it is a VBA thing only, let's just forget about it. I don't want anyone to put a lot of time into it. If it is something easy or not too hard to explain, I'd like to learn how to do it.


Thank you all so much!!!!!

I'm looking around and found this...

ActiveCell.FormulaR1C1 = "=RC[-2]"

Is this something that can be done to do what I need. I just need the cell reference to change is all. I'm not sure what this formula is, or how to use it though if it is a way to do what I'm asking.

See what happens if you try this on a copy of your workbook.
And yes, it is a VBA thingy but you're welcome to try it.

Please Login or Register  to view this content.

Thanks jolivanes!!! I'll see what it does. And if this works like I need, do I just put this in a different module with a different cell number and all that. I really don't know the first thing about VBA. I'll try it though and thank you very much for looking at this for me!!!

That would do it if I had enough sense to know how to change the cell reference. Which is actually in a whole different book. I just can't get the hang of the VBA quite yet.

I've got the code, but will have to play around with it to figure out how to even start changing it to the cells I need to get the data from.

Thank you a bunch jolivanes!!! I'll keep trying with it. That did work, but I'm just no where near able to know how to use it right.

Ok, I think I have a way to make that work and can possibly make it work. I just don't know enough about how it works. Would I need to put that in a module for every cell I needed to do that in? Like I said, I know nothing when it comes to this part of excel. Just barely writing formulas here.

Is there a way, with a macros that I can type in the formula -1 or something that will allow me to subtract from the row number each time? That anyone knows of. I don't know the best way to go about it. I have to make it do this for about 100 cells on each sheet, so by hand is going to take a really long time to do the way I'd like to. If that's what I gotta do, then that's what I gotta do.

Well, each sheet has about 100 cells that have just an =(book-sheet-cellnumber) formula in it that come from 4 different books is what makes this such a pain. I guess the best way to start it would be for me to copy all the columns that has my data into a sheet in the book all this goes into and hide it. Then I can probably figure out how to make the code you wrote work. I don't want anyone having to go thru and write something for everyone of them. It's not important enough to put one of you thru that much time. I just didn't know if I changed that code and put it in a module for each cell, with my reference number and all that in it.

What I'm looking for is what you wrote, but I need to do it many times per sheet. It's always the same column numbers per sheet, just the row number is what I need to change. I'll try it by getting all my data into one book, then using your code for each one. I'll work with it a while and see what I can do with it. That way there won't be any of you having to do put a lot of time into it.

I'm probably in over my head on this one.

I'm making a sheet that I'll upload and you can all see what I'm up against. Then you can tell me what's the best way to go about it. It'll take me a minute to finish it. If it's a bunch of code writing though, I really don't want anyone to put that much time into it.

Ok, now if it's a lot of trouble, just leave it.

What I'm needing is to repeat this over about 300 sheets. 100 if 300 is too many. I got tons of memory and plenty of drive space as far as all that goes.

Column A's cells will go up a row towards the top with each sheet. They're on 491 no, so the next sheet will be 490 and so on. Same with L and W.

C thru F, N thru Q and Y thru AB are the only other columns I need to change each sheet. The rest is the same sheet after sheet.

If there is a way that isn't too much trouble, that would be great. If not, that's ok to.

And thank you all that is even considering looking at what I need to do!!!!

It is well past my bedtime and tomorrow (today!) I will be busy so I hope someone will come with an answer for you.
How big (KB) is your workbook going to be with 300 sheets like the sheet in your attachment?

Bedtime , yes. Well, I don't know how big it would be. It doesn't have to be that big. I can make copies of the book for different sections if I have to no problem if that many is a problem. As far as space on my PC, I'm running 4 drives with a shadow backup, and still got tons of memory to carry it. I'm just looking for a way to speed up the making process.

Thank you very much for looking at it for me and hope you have a great, what's left of the night!!!

Has anyone had time to look at what i am needing? I was wondering if the "indirect function" would work? I am a newby with excel and any help is appreciated

Greenfox and i are working on this. Doesn't seem like anyone knows how to do what we are asking Will it benefit to start a new post ?