Loop until the end and more eloquent way to write this code

[Kurt]

I have the following code:

Sub PivotGraph2()

With Worksheets("Sheet1")
    Range("A2:B2").Copy Range("A16")
    Range("D2").Copy Range("B16")
    Range("F2").Copy Range("C16")
    Range("H2").Copy Range("D16")
    Range("J2").Copy Range("E16")
    Range("L2").Copy Range("F16")
    Range("N2").Copy Range("G16")
    Range("P2").Copy Range("H16")
    Range("R2").Copy Range("J16")
    Range("T2").Copy Range("K16")
    Range("V2").Copy Range("L16")
    Range("X2").Copy Range("M16")
    Range("A3:B3").Copy Range("A16")
    Range("D3").Copy Range("B16")
    Range("F3").Copy Range("C16")
    Range("H3").Copy Range("D16")
    Range("J3").Copy Range("E16")
    Range("L3").Copy Range("F16")
    Range("N3").Copy Range("G16")
    Range("P3").Copy Range("H16")
    Range("R3").Copy Range("J16")
    Range("T3").Copy Range("K16")
    Range("V3").Copy Range("L16")
    Range("X3").Copy Range("M16")
    Range("A4:B4").Copy Range("A16")
    Range("D4").Copy Range("B16")
    Range("F4").Copy Range("C16")
    Range("H4").Copy Range("D16")
    Range("J4").Copy Range("E16")
    Range("L4").Copy Range("F16")
    Range("N4").Copy Range("G16")
    Range("P4").Copy Range("H16")
    Range("R4").Copy Range("J16")
    Range("T4").Copy Range("K16")
    Range("V4").Copy Range("L16")
    Range("X4").Copy Range("M16")
    
      
End With

End Sub

I want it to loop through the end in column A until the end and grab all the months in these ranges associated with Column A. From what Smitty has told me, and I have seen it before is there a way to concantenate all these ranges in one line of code instead of doing it this way?

I am trying to use Excel Jeanie, but I am at work right now, I will try to show the sheet later from my home computer.

If you ened further clarification, please let me know.

Kurt

 

[p45cal]

if you were to answer the questions put to you then…

 

[Kurt]

Hello pcal45,

Jim has the part correct on getting the cells on the row.

I want it to loop through until the end of column A and copy and past every one startign at row 16, not just the first line.

I hope this helps clarifies.

Please let me know if you need something further.

 

[Kurt]

I see what is happening.

After 12 columns from Column A, I want it to break and paste on the next line.

I think this will clarify.

 

[jim may]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	1	2		3		4		5		6		7
3	2	4		6		8		10		12		14
4	6	12		18		24		30		36		42
5	8	4		6		8		10		12		14
6	9	12		18		24		30		36		42
7
8
9
10
11
12
13		After Running the attached Macro "concat4"
14
15
16	1	2
17		6	8	10	12	14
18		18	24	30	36	42
19		6	8	10	12	14
20		18	24	30	36	42
Sheet1

Excel 2007

Currently, this MAY NOT have the first row (#2) rightly copied, but it does for the remaining ones. COMMENT? Jim

Sub concat4()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim LC As Long, t As Long ', i As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
    LC = Cells(2, Columns.Count).End(xlToLeft).Column
    With Range(Cells(2, 1), Cells(2, 2))
        .Copy Destination:=Cells(16, 1)
    End With
    t = 2
Range("A3").Select
j = 17
Do Until ActiveCell.Address = "$A$" & lastrow + 1
    For i = 4 To LC Step 2
Cells(ActiveCell.Row, i).Copy Destination:=Cells(j, t)
t = t + 1
Next i
ActiveCell.Offset(1).Select
j = j + 1
t = 2
Loop
End Sub

 

[Kurt]

Yes sir, we are definitely almost there.

I also want the info in Column A copied.

It is now stopping on the first month.

You almost have it!

Why is it chopping off the first line now?

FL - Hyster - H50FT 38
7 8 7 8 7 8 4 7 4 7 4 79
6 9 6 9 6 9 4 7 4 7 4 80
20 46 20 46 20 46 24 28 24 28 24 372
1 19 1 19 1 19 4 7 4 7 4 96
9 17 2 17 2 17 5 7 5 7 5 103
7 29 7 29 7 29 16 14 16 14 16 213
3 16 3 16 3 16 9 7 9 7 9 114

 

[jim may]

Should it (In My Example) HAVE this Completed Look?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	1	2		3		4		5		6		7
3	2	4		6		8		10		12		14
4	6	12		18		24		30		36		42
5	8	4		6		8		10		12		14
6	9	12		18		24		30		36		42
7
8
9
10
11
12
13		After Running the attached Macro "concat4"
14
15
16	1	2
17		3	4	5	6	7
18		6	8	10	12	14
19		18	24	30	36	42
20		6	8	10	12	14
21		18	24	30	36	42
Sheet1

Excel 2007

 

[Kurt]

Yes, but with the contents of cell A copied going across 12 months plus the total.

 

[jim may]

I am LIMITED in what I Can SHOW you on the Screen here do the the limitations of the HTML Maker Software (Can't go past Column M), but the code should work.
Here's my final attempt. Good Luck Jim

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	1	2		3		4		5		6		7
3	2	4		6		8		10		12		14
4	6	12		18		24		30		36		42
5	8	4		6		8		10		12		14
6	9	12		18		24		30		36		42
7
8
9
10
11
12
13		After Running the attached Macro "concat4"
14
15
16	1	2
17		2	3	4	5	6	7
18		4	6	8	10	12	14
19		12	18	24	30	36	42
20		4	6	8	10	12	14
21		12	18	24	30	36	42
Sheet1

Excel 2007

Using this Code:

Sub concat4()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim LC As Long, t As Long ', i As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
    LC = Cells(2, Columns.Count).End(xlToLeft).Column
    t = 2
    
    With Range(Cells(2, 1), Cells(2, 2))
        .Copy Destination:=Cells(16, 1)
    End With
Range("A2").Select
j = 17
Do Until ActiveCell.Address = "$A$" & lastrow + 1
    For i = 2 To LC Step 2
Cells(ActiveCell.Row, i).Copy Destination:=Cells(j, t)
t = t + 1
Next i
ActiveCell.Offset(1).Select
j = j + 1
t = 2
Loop
End Sub

 

[Kurt]

I took some more time to understand your code:

I changed the following line

For i = 1 To LC Step 1

This adds the names in column A but it also copies an extra column.

What can you change to paste the items in column A and just keep the totals? The next column will be dollar amounts. I just want the totals.

Very, very nearly there!

 

[p45cal]

I want it to loop through until the end of column A and copy and past every one startign at row 16, not just the first line.

I hope this helps clarifies.

Please let me know if you need something further.

No it does not! So this is a guess:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2	A2	B2	C2	D2	E2	F2	G2	H2	I2	J2	K2	L2	M2	N2	O2	P2	Q2	R2	S2	T2	U2	V2	W2	X2
3	A3	B3	C3	D3	E3	F3	G3	H3	I3	J3	K3	L3	M3	N3	O3	P3	Q3	R3	S3	T3	U3	V3	W3	X3
4	A4	B4	C4	D4	E4	F4	G4	H4	I4	J4	K4	L4	M4	N4	O4	P4	Q4	R4	S4	T4	U4	V4	W4	X4
5
14		after running blah:
15
16	A2	D2	F2	H2	J2	L2	N2	P2	R2	T2	V2	X2
17	A3	D3	F3	H3	J3	L3	N3	P3	R3	T3	V3	X3
18	A4	D4	F4	H4	J4	L4	N4	P4	R4	T4	V4	X4
Sheet1

from

Sub blah()
Set Pattern = Range("A1,D1,F1,H1,J1,L1,N1,P1,R1,T1,V1,X1") 'adjust column letters only
DestRow = 16
LR = Cells(Rows.Count, 1).End(xlUp).Row
For rw = 2 To LR
    Pattern.Offset(rw - 1).Copy Cells(DestRow, 1)
    DestRow = DestRow + 1
Next rw
End Sub

Adjust the line:
Set Pattern = Range("A1,D1,F1,H1,J1,L1,N1,P1,R1,T1,V1,X1")
if the columns I've chosen are wrong (highlighted in green in row 2 determined from the result of your original macro running), you can add more, remove some, but retain the refs to row 1 (it's only a pattern of columns).
I have assumed that (1) in post #5 wsa confirmed here, and that (2) was NOT confirmed, but these are only guesses.