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

 

[jim may]

Your current codes looks like it is Copying Row 2 to row 16 then
Copying Row 2 OVER your previously pasted row 16 then
Copying Row 3 OVER your previously pasted row 16...(*&$()*$#@@

Am I reading this right?

 

[jim may]

Correction, (Should have read)...

Your current codes looks like it is Copying Row 2 to row 16 then
Copying Row 3 OVER your previously pasted row 16 then
Copying Row 4 OVER your previously pasted row 16...(*&$()*$#@@

 

[Kurt]

Hello Jim and all:

I found this code while doing a search on Mr. Excel

Sub concat()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range(Cells(1, 3), Cells(LR, 3))
    .Formula = "=A1&B1"
    .Value = .Value
End With
End Sub

Somthing like this I think but with the copy and paste feature.

From this link:

http://www.mrexcel.com/forum/showthread.php?t=576204&highlight=concatenate+columns+copy+paste

 

[p45cal]

Could you answer jim may's qustion. In addition, below shows the result of your macro on a sheet where I've put cell addresses as data in the cell range you're copying from, and highlighted in green the pattern of cells used in each row. Could you confirm two things:
1. That the source pattern is exactly as shown and not intended to be a regular alternate columns pattern.
2. That the destination range misses out column I as shown.

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
15
16	A4	D4	F4	H4	J4	L4	N4	P4		R4	T4	V4	X4
Sheet1

 

[jim may]

It appears in your original code that some cells which are being Pasted (with the copy) ARE IMMEDIATELY Being Re-Pasted (Overwritten), by a LATER PASTE..., But Anyway, try something like this on A COPY (not your Original file). Stepping thru the code (Using the F8 key might be helpfule to you..

In a STANDARD MODULE:

Sub concat()
Dim LC As Long, t As Long, i As Long
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
For i = 4 To LC Step 2
Cells(2, i).Copy Destination:=Cells(16, t)
t = t + 1
Next i
End Sub

 

[Kurt]

Many thanks for your efforts!

We are almost there.

Instead of copying only row 16, I need it to copy all the way down column A. In this case there are 9 rows, but these will vary.

Almost there!

 

[Kurt]

I am trying to add this lastrow.

Can someone show me what I amd doing wrong?

Sub concat()
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("A2").Row.Cells(Rows.Count, "A").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
    For i = 4 To LC Step 2
Cells(2, i).Copy Destination:=Cells(16, t)
t = t + 1
Next i
End Sub

I think this lastrow will get to where I want this to be.

 

[jim may]

I'm still UNCERTAIN as to some thngs you are doing (as once pated cells get overwritten), but here is the Latest - It strings your copied cells continually OUT to the Right as many
columns as are necessary. TRY THIS ON A BACKUP COPY OF YOUR FILE!!! Jim

Sub concat3()
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
Do Until ActiveCell.Address = "$A$" & lastrow + 1
    For i = 4 To LC Step 2
Cells(ActiveCell.Row, i).Copy Destination:=Cells(16, t)
t = t + 1
Next i
ActiveCell.Offset(1).Select
Loop
End Sub

 

[Kurt]

There are 8 items in column A.

I want it to loop in column A to the end and use your copy and paste routine.

I hope this helps clarify.

This still only copies one line of code.

Thanks for your effort!