Copy Paste to Every Nth Row

[alexb523]

Hello,

I am having issues copying the same thing 'n' amount of times to every 'n' row. i have a range of cells and would like to post it every over and over again (i have a code to get the respective values). I do this several different times and paste it a different amount of times. Here is the 'hard code' of what i need done:

    'Count of Mode
    Dim modeTable As Worksheet
    Set modeTable = Sheets.Add(After:=Sheets(Worksheets.Count))
    modeTable.Name = "modeTable"
    
    TableTemplates.Range("A67:G84").Copy
    
    modeTable.Range("A2").Select
    ActiveSheet.Paste
    modeTable.Range("A21").Select
    ActiveSheet.Paste
    modeTable.Range("A40").Select
    ActiveSheet.Paste
    modeTable.Range("A59").Select
    ActiveSheet.Paste
    modeTable.Range("A78").Select
    ActiveSheet.Paste
    modeTable.Range("A97").Select
    ActiveSheet.Paste
    modeTable.Range("A116").Select
    ActiveSheet.Paste
    modeTable.Range("A135").Select
    ActiveSheet.Paste
    modeTable.Range("A154").Select
    ActiveSheet.Paste
    modeTable.Range("A173").Select
    ActiveSheet.Paste
    modeTable.Range("A192").Select
    ActiveSheet.Paste
    modeTable.Range("A211").Select
    ActiveSheet.Paste
    modeTable.Range("A230").Select
    ActiveSheet.Paste
    modeTable.Range("A249").Select
    ActiveSheet.Paste

and then here is my attempt loop it. but i keep getting errors every which way.

    'count number of 'Count of Mode' table needed
    TableNames.Select
    Dim modeCount As Integer
    Dim tableRowDiff As Integer
    modeCount = Application.WorksheetFunction.CountIf(Range("A2:A" & lrTableNames), "Count of Mode") 'how many times i need to post a new table resides on a different spreadsheet
    tableRowDiff = 19
    
    For modeRow = 1 To modeCount
        modeTable.Range("A" & (modeRow * tableRowDiff)).Select
        ActiveSheet.Paste
    Next modeRow

 

[sheetspread]

Try for i = 2 to 249 step 19

 

[alexb523]

I'm not really sure where the 249 comes from. I like the idea of putting in a step. The error i'm currently getting is on

 [COLOR=#333333]modeTable.Range("A" & (modeRow * tableRowDiff)).Select[/COLOR]

where is throwing the error 'Select method of Range class failed'd

 

[sheetspread]

I'm not really sure where the 249 comes from.

What??!!!!

modeTable.Range("A249").Select

 

[alexb523]

o right. sorry. i'm trying to avoid knowing that number. how would i use the modeCount? in this case there are 14. but it varies over what i need my code for.

 

[alexb523]

So just to clarify what i am trying to do:

I have a range of cells

TableTemplates.Range("A67:G85").Copy

that i want to paste as many times as i "Count of Mode" appears in a specified sheet:

    TableTemplates.Range("A67:G85").Copy
    
    'count number of 'Count of Mode' table needed
    TableNames.Select
    Dim modeCount As Integer
    Dim tableRowDiff As Integer
    modeCount = Application.WorksheetFunction.CountIf(Range("A2:A" & lrTableNames), "Count of Mode")
    tableRowDiff = 19
    
    For modeRow = 2 To modeCount * 19 Step 19
        modeTable.Range("A" & modeRow).Select
        ActiveSheet.Paste
    Next modeRow

 

[sheetspread]

Can you post a link to the file so I can test?

 

[alexb523]

So lets say there is a table (not an actual table just a matrix) like the one below from A1:C6 on a tab called Tab1:

[TABLE="width: 192"]
<tbody>[TR]
[TD="colspan: 3"]Table With Formatting[/TD]
[/TR]
[TR]
[TD]Header1[/TD]
[TD]Header2[/TD]
[TD]Header3[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]z[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]

*NoteL the line 'Table With Formatting' is apart of the table

Then there is another tab, Tab2, with data in column A like the following:

[TABLE="width: 107"]
<tbody>[TR]
[TD]Column to Count[/TD]
[/TR]
[TR]
[TD]Other Text[/TD]
[/TR]
[TR]
[TD]Other Text[/TD]
[/TR]
[TR]
[TD]Other Text[/TD]
[/TR]
[TR]
[TD]Test Text[/TD]
[/TR]
[TR]
[TD]Test Text[/TD]
[/TR]
[TR]
[TD]Test Text[/TD]
[/TR]
[TR]
[TD]Count of Mode[/TD]
[/TR]
[TR]
[TD]Count of Mode[/TD]
[/TR]
[TR]
[TD]Count of Mode[/TD]
[/TR]
[TR]
[TD]Count of Mode[/TD]
[/TR]
[TR]
[TD]Count of Mode[/TD]
[/TR]
</tbody>[/TABLE]

There ends of being 5 instances of 'Count of Mode' (the number of instance will be unknown) which means i need to paste table, in this case, 5 times to a new sheet say called Tab3. The table would be pasted to A2, A9, A15, etc. which leave one row in between each table. This would be a straight copy-paste, formats and all.

The part which bugs in my previous code is the line:

[COLOR=#333333]modeTable.Range("A" & modeRow).Select[/COLOR]

which give the error 'Select method of Range class failed'

Thanks,
Alex

 

[sheetspread]

This?

Excel 2010
	A	B	C
1	Table With Formatting
2	Header1	Header2	Header3
3	x	1	5
4	y	2	6
5	z	3	7
6	f	4	9
Tab1

Excel 2010
	A
1	Column to Count
2	Other Text
3	Other Text
4	Other Text
5	Test Text
6	Test Text
7	Test Text
8	Count of Mode
9	Count of Mode
10	Count of Mode
11	Count of Mode
12	Count of Mode
Tab2

Sub pastetopage()
Dim i%, lr%, x%
lr = Sheets("Tab2").Cells(Rows.Count, 1).End(xlUp).Row
x = Application.CountIf(Sheets("Tab2").Cells(1, 1).Resize(lr), "Count of Mode")
For i = 2 To 2 + (x * 6) Step 7
Sheets("Tab1").Cells(1).CurrentRegion.Copy Destination:=Sheets("Tab3").Cells(i, 1)
Next
End Sub

Excel 2010
	A	B	C
1
2	Table With Formatting
3	Header1	Header2	Header3
4	x	1	5
5	y	2	6
6	z	3	7
7	f	4	9
8
9	Table With Formatting
10	Header1	Header2	Header3
11	x	1	5
12	y	2	6
13	z	3	7
14	f	4	9
15
16	Table With Formatting
17	Header1	Header2	Header3
18	x	1	5
19	y	2	6
20	z	3	7
21	f	4	9
22
23	Table With Formatting
24	Header1	Header2	Header3
25	x	1	5
26	y	2	6
27	z	3	7
28	f	4	9
29
30	Table With Formatting
31	Header1	Header2	Header3
32	x	1	5
33	y	2	6
34	z	3	7
35	f	4	9
Tab3

 

[alexb523]

I'm still having an issue when i put this in practical purpose. It seem to only be copying once to A14 to my modeTable tab.

    Dim modeTable As Worksheet
    Set modeTable = Sheets.Add(After:=Sheets(Worksheets.Count))
    modeTable.Name = "modeTable"
    


    'count number of 'Count of Mode' table needed
    TableNames.Select
    Dim modeCount As Integer
    modeCount = Application.WorksheetFunction.CountIf(Range("A2:A" & lrTableNames), "Count of Mode")
    
    For modeRow = 2 To 2 + (modeCount * 18) Step 19
        TableTemplates.Range("A67:G84").Copy Destination:=modeTable.Cells(modeCount, 1)
    Next