Autofill formula every 105 rows, while repeating

Hello Everyone!

This forum has been a lifesaver!

I have an issue that i cannot find the resolution anywhere, i am hoping someone here can help!

I have a spreadsheet that i am pulling data from another tab (='Retailers '!$C$3), on the "retailers" tab i have over 400 rows, and each row is a different retailer. On the sheet i am working on i want to pull that for each row but repeat 105 times. I have been filling and changing the row number manually, but if i have to do this over 400 times it will take forever, and i am sure there is an easy way to do this.

I am going to give a little example here in a small scale

TAB 1 (Source)

Walmart
Target
Lowes

TAB 2 (i will do repeat formula 2 times vs 105 to make it easy)


='TAB 1'!$A$1 (Walmart)
='TAB 1'!$A$1 (Walmart)
='TAB 1'!$A$2 (Target)
='TAB 1'!$A$2 (Target)
='TAB 1'!$A$3 (Lowes)
='TAB 1'!$A$3 (Lowes)

Not sure if i was clear, hope i was. Thank You in advance!

I have now attached a example.

The retailers tab on my original i have over 400 rows (i only included 4 in the sample), i need to pull each roll from the retailers tab 105 times, then on the 106 it comes the next one down the line and so on and so forth. I then have on the Sheet 1 a simple formulas pulling info using the retailer as a reference. The issue is i have users that may change the name or the way the name shows and it messes up my formula. I had previously done manually each row and dragging to 105 lines.

Thank You so much!

As a simple example, this code goes through the retailers in Sheet1 from A1:A??? and for each retailer it generates the formula back to each one 105 times.

A formula pointing to Sheet1!A1 will appear in Sheet2!A1:A105
A formula pointing to Sheet1!A2 will appear in Sheet2!A106:A210
etc...

Please Login or Register  to view this content.

Updated code to work with your sample workbook:

Please Login or Register  to view this content.

On the Retailers tab (source) i have 470 rows of data, each of those need to repeat 105 times. If possible i would like to not stop at line 470 in case the user adds new locations. I am hoping i was clear...

Each time you run the macro it will find the new last row, whether it's row 4 or 4000.

It is giving me an error "Out of Range" i have the data in the column C and i changed and still happened. Attached is the actual file, i need each of those "CStore Chain" on column C, to repeat 105 times on "Sheet 1"

Downloaded your file from post #8 above. A few things...
1. Your "Retailers" worksheet is actually named "Retailers " (with a space at the end). That's why you're getting an 'Out of Range' error. Remove the trailing space.
2. Your retailer list starts in C6 of that sheet, so I've adjusted the For...Next loop to span "C6:C" & ret instead of "C3:C" & ret
3. I changed the 'Sheets("Sheet 2")' reference to 'Sheets("Sheet1")' since that's what your worksheets are named.

Please Login or Register  to view this content.

It worked! Thank You so much!!!!!!

You're welcome!

...oddly enough, the 'Reputation' boost you gave put me at exactly 105. What are the odds of that?