Unsure of Loop

[Davebro]

I am looking to find a loop through my macro.

Sub Macro1()
'


'
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(8, -1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
ActiveCell.Select
Selection.End(xlDown).Select
ActiveCell.Offset(4, 1).Range("A1").Select

End Sub

 

[SirQuej]

I asked the same question but have figured it out....

Try this (Will Loop the Macro 100 Times so change the To number to suit your needs...)

Sub Macro1()
'


'
Dim Index As Integer

For Index = 1 To 100
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(8, -1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
ActiveCell.Select
Selection.End(xlDown).Select
ActiveCell.Offset(4, 1).Range("A1").Select
Next

End Sub

 

[Davebro]

I asked the same question but have figured it out....

Try this (Will Loop the Macro 100 Times so change the To number to suit your needs...)

Sub Macro1()
'


'
Dim Index As Integer

For Index = 1 To 100
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(8, -1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
ActiveCell.Select
Selection.End(xlDown).Select
ActiveCell.Offset(4, 1).Range("A1").Select
Next

End Sub

Regret this goes to the last row in the spreadsheet with
debug error

ActiveCell.Offset(4, 1).Range("A1").Select

 

[SirQuej]

Ah, shame...

It works for what I needed....the fact that it does that doesn't affect what I wanted from it...

You would probably need to use the "While" loop then maybe which uses a condition.

 

[Joe4]

Davebro,

Can you explain in more detail exactly what you are trying to do?
What is the structure of your data?
Where should the loop start, and when should it stop?

Note that while you cannot attach spreadsheets to posts, you can post images, if you feel that would be helpful in explaining your problem.
See this here for details on that: XL2BB - Excel Range to BBCode

 

[Davebro]

Davebro,

Can you explain in more detail exactly what you are trying to do?
What is the structure of your data?
Where should the loop start, and when should it stop?

Note that while you cannot attach spreadsheets to posts, you can post images, if you feel that would be helpful in explaining your problem.
See this here for details on that: XL2BB - Excel Range to BBCode

This macro is the first of two I use to sort data so I can use a pivot table.
Macro 1 starts at ActiveCell B4 and transfers the data in that cell to A12 and the variable end of that section
It then goes down to next equivalent of B4, the gap is always the same as between B4 and A12. 8 rows.

ActiveCell.Select
Selection.Copy
ActiveCell.Offset(8, -1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
ActiveCell.Select
Selection.End(xlDown).Select
ActiveCell.Offset(4, 1).Range("A1").Select

 

[Joe4]

It then goes down to next equivalent of B4, the gap is always the same as between B4 and A12. 8 rows.

I am not sure what you mean by "next equivalent".
I think this is where seeing an actual example may be really helpful, since you seem to be having trouble explaining it.

 

[Davebro]

Book1
	A	B	C
1
2	Data 1	Data 1
3	Data 1	Data 1
4	Data 1	ITEM 1
5	Data 1	Data 1
6	Data 1	Data 1
7	Data 1	Data 1
8	Data 1	Data 1
9	Data 1	Data 1
10			Data 1
11	Data	Data 1	Data 1
12	1	Data 1	Data 1
13	2	Data 1	Data 1
14	3	Data 1	Data 1
15	4	Data 1	Data 1
16	5	Data 1	Data 1
17	6	Data 1	Data 1
18	7	Data 1	Data 1
19	8	Data 1	Data 1
20	9	Data 1	Data 1
21	10	Data 1	Data 1
22	11	Data 1	Data 1
23	12	Data 1	Data 1
24
25	Data 2	Data 2
26	Data 2	Data 2
27	Data 2	ITEM 2
28	Data 2	Data 2
29	Data 2	Data 2
30	Data 2	Data 2
31	Data 2	Data 2
32	Data 2	Data 2
33			Data 2
34	Data 2	Data 2	Data 2
35	1	Data 2	Data 2
36	2	Data 2	Data 2
37	3	Data 2	Data 2
38	4	Data 2	Data 2
39	5	Data 2	Data 2
40	6	Data 2	Data 2
41	7	Data 2	Data 2
42	8	Data 2	Data 2
43	9	Data 2	Data 2
44	10	Data 2	Data 2
45
46	Data 3	Data 3
47	Data 3	Data 3
48	Data 3	ITEM 3
49	Data 3	Data 3
50	Data 3	Data 3
51	Data 3	Data 3
52	Data 3	Data 3
53	Data 3	Data 3
54			Data 3
55	Data 3	Data 3	Data 3
56	1	Data 3	Data 3
57	2	Data 3	Data 3
58	3	Data 3	Data 3
59	4	Data 3	Data 3
60
61
62	After
63
64	Data 1	Data 1
65	Data 1	Data 1
66	Data 1	ITEM 1
67	Data 1	Data 1
68	Data 1	Data 1
69	Data 1	Data 1
70	Data 1	Data 1
71	Data 1	Data 1
72			Data 1
73	Data	Data 1	Data 1
74	ITEM 1	Data 1	Data 1
75	ITEM 1	Data 1	Data 1
76	ITEM 1	Data 1	Data 1
77	ITEM 1	Data 1	Data 1
78	ITEM 1	Data 1	Data 1
79	ITEM 1	Data 1	Data 1
80	ITEM 1	Data 1	Data 1
81	ITEM 1	Data 1	Data 1
82	ITEM 1	Data 1	Data 1
83	ITEM 1	Data 1	Data 1
84	ITEM 1	Data 1	Data 1
85	ITEM 1	Data 1	Data 1
86
87	Data 2	Data 2
88	Data 2	Data 2
89	Data 2	ITEM 2
90	Data 2	Data 2
91	Data 2	Data 2
92	Data 2	Data 2
93	Data 2	Data 2
94	Data 2	Data 2
95			Data 2
96	Data 2	Data 2	Data 2
97	ITEM 2	Data 2	Data 2
98	ITEM 2	Data 2	Data 2
99	ITEM 2	Data 2	Data 2
100	ITEM 2	Data 2	Data 2
101	ITEM 2	Data 2	Data 2
102	ITEM 2	Data 2	Data 2
103	ITEM 2	Data 2	Data 2
104	ITEM 2	Data 2	Data 2
105	ITEM 2	Data 2	Data 2
106	ITEM 2	Data 2	Data 2
107
108	Data 3	Data 3
109	Data 3	Data 3
110	Data 3	ITEM 3
111	Data 3	Data 3
112	Data 3	Data 3
113	Data 3	Data 3
114	Data 3	Data 3
115	Data 3	Data 3
116			Data 3
117	Data 3	Data 3	Data 3
118	ITEM 3	Data 3	Data 3
119	ITEM 3	Data 3	Data 3
120	ITEM 3	Data 3	Data 3
121	ITEM 3	Data 3	Data 3
Sheet1

 

[Joe4]

OK, based on your example posted, can you explain exactly what is supposed to happen?

 

[Davebro]

Book1
	A	B	C
1
2	Data 1	Data 1
3	Data 1	Data 1
4	Data 1	ITEM 1
5	Data 1	Data 1
6	Data 1	Data 1
7	Data 1	Data 1
8	Data 1	Data 1
9	Data 1	Data 1
10			Data 1
11	Data	Data 1	Data 1
12	1	Data 1	Data 1
13	2	Data 1	Data 1
14	3	Data 1	Data 1
15	4	Data 1	Data 1
16	5	Data 1	Data 1
17	6	Data 1	Data 1
18	7	Data 1	Data 1
19	8	Data 1	Data 1
20	9	Data 1	Data 1
21	10	Data 1	Data 1
22	11	Data 1	Data 1
23	12	Data 1	Data 1
24
25	Data 2	Data 2
26	Data 2	Data 2
27	Data 2	ITEM 2
28	Data 2	Data 2
29	Data 2	Data 2
30	Data 2	Data 2
31	Data 2	Data 2
32	Data 2	Data 2
33			Data 2
34	Data 2	Data 2	Data 2
35	1	Data 2	Data 2
36	2	Data 2	Data 2
37	3	Data 2	Data 2
38	4	Data 2	Data 2
39	5	Data 2	Data 2
40	6	Data 2	Data 2
41	7	Data 2	Data 2
42	8	Data 2	Data 2
43	9	Data 2	Data 2
44	10	Data 2	Data 2
45
46	Data 3	Data 3
47	Data 3	Data 3
48	Data 3	ITEM 3
49	Data 3	Data 3
50	Data 3	Data 3
51	Data 3	Data 3
52	Data 3	Data 3
53	Data 3	Data 3
54			Data 3
55	Data 3	Data 3	Data 3
56	1	Data 3	Data 3
57	2	Data 3	Data 3
58	3	Data 3	Data 3
59	4	Data 3	Data 3
60
61
62	After
63
64	Data 1	Data 1
65	Data 1	Data 1
66	Data 1	ITEM 1
67	Data 1	Data 1
68	Data 1	Data 1
69	Data 1	Data 1
70	Data 1	Data 1
71	Data 1	Data 1
72			Data 1
73	Data	Data 1	Data 1
74	ITEM 1	Data 1	Data 1
75	ITEM 1	Data 1	Data 1
76	ITEM 1	Data 1	Data 1
77	ITEM 1	Data 1	Data 1
78	ITEM 1	Data 1	Data 1
79	ITEM 1	Data 1	Data 1
80	ITEM 1	Data 1	Data 1
81	ITEM 1	Data 1	Data 1
82	ITEM 1	Data 1	Data 1
83	ITEM 1	Data 1	Data 1
84	ITEM 1	Data 1	Data 1
85	ITEM 1	Data 1	Data 1
86
87	Data 2	Data 2
88	Data 2	Data 2
89	Data 2	ITEM 2
90	Data 2	Data 2
91	Data 2	Data 2
92	Data 2	Data 2
93	Data 2	Data 2
94	Data 2	Data 2
95			Data 2
96	Data 2	Data 2	Data 2
97	ITEM 2	Data 2	Data 2
98	ITEM 2	Data 2	Data 2
99	ITEM 2	Data 2	Data 2
100	ITEM 2	Data 2	Data 2
101	ITEM 2	Data 2	Data 2
102	ITEM 2	Data 2	Data 2
103	ITEM 2	Data 2	Data 2
104	ITEM 2	Data 2	Data 2
105	ITEM 2	Data 2	Data 2
106	ITEM 2	Data 2	Data 2
107
108	Data 3	Data 3
109	Data 3	Data 3
110	Data 3	ITEM 3
111	Data 3	Data 3
112	Data 3	Data 3
113	Data 3	Data 3
114	Data 3	Data 3
115	Data 3	Data 3
116			Data 3
117	Data 3	Data 3	Data 3
118	ITEM 3	Data 3	Data 3
119	ITEM 3	Data 3	Data 3
120	ITEM 3	Data 3	Data 3
121	ITEM 3	Data 3	Data 3
Sheet1

Move Item 1 to (B4) to A12 onwards, this may be be any length, then move on to next section. As in the after example in the sample.