Copy and Paste

[Shane50GT]

I am hoping to get some help on copy and paste! Seems like something simple and probably is but here goes....I need to repeat several data sets that have 6 rows of data down a single column, the data sets point to various other sheets but the constant is that I need each row within the data set to increase by 1 each time I paste the data set, however, it is increasing each row 6 due to the 6 individual lines of data...See example below..... (I will need to copy and paste the data sets around 60 times).

Direct Craft Description	=Sheet2!$B9
Number of Craft	=Sheet2!$D9
S.T. Pay Rate	=Sheet2!$K9
O.T Pay Rate	=Sheet2!$K88
S.T. Billable Rate	=Sheet3!$R9
O.T. Billable Rate	=Sheet3!$S9
COPIED AND PASTED FROM ABOVE
Direct Craft Description	=Sheet2!$B17	<<<How can I make this paste as =Sheet2!$B10
Number of Craft	=Sheet2!$D17	<<<How can I make this paste as =Sheet2!$D10
S.T. Pay Rate	=Sheet2!$K17	<<<How can I make this paste as =Sheet2!$K10
O.T Pay Rate	=Sheet2!$K96	<<<How can I make this paste as =Sheet2!$K89
S.T. Billable Rate	=Sheet3!$R17	<<<How can I make this paste as =Sheet3!$R10
O.T. Billable Rate	=Sheet3!$S17	<<<How can I make this paste as =Sheet3!$S10

 

[Eric W]

Welcome to the MrExcel forum!

Here's one way:

Book1
	A	B	C
1
2	Direct Craft Description	b9
3	Number of Craft	d9
4	S.T. Pay Rate	k9
5	O.T Pay Rate	k88
6	S.T. Billable Rate	r9
7	O.T. Billable Rate	s9
8	Direct Craft Description	b10	<<<How can I make this paste as =Sheet2!$B10
9	Number of Craft	d10	<<<How can I make this paste as =Sheet2!$D10
10	S.T. Pay Rate	k10	<<<How can I make this paste as =Sheet2!$K10
11	O.T Pay Rate	k89	<<<How can I make this paste as =Sheet2!$K89
12	S.T. Billable Rate	r10	<<<How can I make this paste as =Sheet3!$R10
13	O.T. Billable Rate	s10	<<<How can I make this paste as =Sheet3!$S10
Sheet1

Cell Formulas
Range	Formula
B2,B8	B2	=INDEX(Sheet2!$B:$B,INT((ROWS($B$2:$B2)-1)/6)+9)
B3,B9	B3	=INDEX(Sheet2!$D:$D,INT((ROWS($B$2:$B2)-1)/6)+9)
B4,B10	B4	=INDEX(Sheet2!$K:$K,INT((ROWS($B$2:$B2)-1)/6)+9)
B5,B11	B5	=INDEX(Sheet2!$K:$K,INT((ROWS($B$2:$B2)-1)/6)+88)
B6,B12	B6	=INDEX(Sheet2!$R:$R,INT((ROWS($B$2:$B2)-1)/6)+9)
B7,B13	B7	=INDEX(Sheet2!$S:$S,INT((ROWS($B$2:$B2)-1)/6)+9)

 

[Shane50GT]

Wow, that works! I need to study that formula to understand what you did there but it works. Haven't used INDEX much and trying to figure out why you used INT, I will go study. I was thinking it would be something to account for the lines in between but I wouldn't have gotten that figured out for a while. Thank you fella.

 

[Eric W]

Actually, in this case you don't need to use INT. The ROWS(...)-1 will always be a multiple of 6. Anyway, let me know if you have any questions!

 

[Shane50GT]

Eric,
I found an issue with the formula and I'm not sure how to fix it. When I paste the data sets for the 3rd time and beyond I am having to manually calculate how much to subtract because the "-1" is not enough to get where I need it to be. So for example, I changed it to -1.1 on the 4th paste and that worked, on the 5th paste I had to change it to -3.3, on the 6th paste I had to change it to -6.6, on the 7th paste I had to change it to -9.9, and on the 8th I had to change it to -10....Do you know of a way to account for the increased number of rows as I continue to paste the data sets down the sheet? Thank you for your time in advance.

 

[Eric W]

If you paste the formulas every 6 lines, you shouldn't have to adjust the offset. Are you adding more rows? Do you have headers and/or empty lines between each group of 6?

 

[Shane50GT]

If you paste the formulas every 6 lines, you shouldn't have to adjust the offset. Are you adding more rows? Do you have headers and/or empty lines between each group of 6?

That's what it is, I have 2 blank lines between each data set. Leaving space because I might have to add some additional lines to the data set in the future. Is there something we can add to the formula to account for those 2 blank lines between each set?

 

[Shane50GT]

That's what it is, I have 2 blank lines between each data set. Leaving space because I might have to add some additional lines to the data set in the future. Is there something we can add to the formula to account for those 2 blank lines between each set?

Just make the divider 8 right?

 

[Eric W]

Yes, that should work. Since you have Excel 365, there might be another way too. I'll let you know if I figure that out.

 

[Shane50GT]

That was it, made did find and replace the /6 to /8 and changed all of the subtractions back to 1. Thank you again for the help.