Transpose to every other cell

[pbt]

I have this as part of my code:

Sheets("Data").Range("I5:I9").Copy
Sheets("Totals").Range("G3").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True

How can I make it Paste to every other column starting in G3?

If I can get help on this part, I guess I can adapt it to copy the verticle range O5:O9 and Paste starting at H3 (every other col)

Thanks
Harry

 

[jindon]

Hi

Not clear to me.

Do you want to paste the same value to every other column?
or different values to every other column?

 

[pbt]

The same value from DATA sheet I5:I9 will go to Totals sheet starting at G3, I3, K3, etc.

That is on Totals sheet

G3 = I5 .Value
I3 = I6 .Value
K3 = I7 .Value
etc.

Harry

 

[jindon]

Ah, I see

If you want paste formula/formats, I think you need to copy one by one.

Dim r As Range, n As Long
For Each r In Range("i5:i9")
     r.Copy Range("g3").Offset(,n)
     n = n + 2
Next

Otherwise try

Dim a(), i As Long, n As Long
With Range("i5:i9")
     ReDim a(1 To .Cells.Count * 2 - 1) : n = 1
     For i = 1 To .Cells.Count
          a(n) = .Cells(i).Value
          n = n + 2
     Next
End With
Range("g3").Resize(,n).Value = a

 

[pbt]

Let me show the sheets and results:

I'm copying column I

Book1
	I	J	K	L	M	N	O
5	HARRY						10.93
6	JOHN						5.89
7	BILL						6.73
8	STEVE						5.89
9	FRANK						7.57
Data

And transposing to TOTAL sheet, trying to make it look like this

Book1
	G	H	I	J	K	L	M	N	O	P
1	NAME	AMT	NAME	AMT	NAME	AMT	NAME	AMT	NAME	AMT
2
3	HARRY		JOHN		BILL		STEVE		FRANK
4
5
Totals

Your code gives me this result, (close, but on wrong sheet, and too many columns)

Book1
	G	H	I	J	K	L	M	N	O	P	Q
3	HARRY		JOHN		BILL		STEVE		FRANK	#N/A	#N/A
Data

I don't know if you were trying to help me out with the "O" column on Data sheet or not.

Data sheet column "I" is formated as text and "O" is a formula. I need just the Values to be transposed to the Totals sheet.

EDIT: Values from "O" will go next to each name on Totals sheet
I tried the second code
Harry

 

[jindon]

If you want to copy Col.I & M at the same time then
try

Sub test2()
Dim a(1 To 10), n As Long, i As Long
n = 1
For i = 5 To 9
     a(n) = Range("i" & i).Value
     a(n + 1) = Range("o" & i).Value
     n = n + 2
Next
Range("g3").Resize(,10).Value = a
End Sub

 

[pbt]

That is giving the desired results still on the wrong sheet

It puts it on the Data sheet, but I'm trying to put it on the Totals sheet.

And I'm trying to interpet the code, can't figure out anything except:
For i = 5 To 9 < my range to copy

Harry

 

[jindon]

OK

Sub test2()
Dim a(1 To 10), n As Long, i As Long
n = 1
For i = 5 To 9
     a(n) = Sheets("data").Range("i" & i).Value
     a(n + 1) = Sheets("data").Range("o" & i).Value
     n = n + 2
Next
Sheets("totals").Range("g3").Resize(,10).Value = a
End Sub

 

[pbt]

Thanky you jindon

That works great. I don't understand each line of the code(as far as what it is doing) but I know that it is storing the info from the Data sheet before Pasteing to the Totals sheet. (seen by stepping through)

Unfortunatley I didn't bring my memory stick with the actual workbook home tonight. This procedurel will have to do a loop every 21 rows and do the same Copy and Paste.

I don't want to complicate things right now. I will take what I have now and try to implement it into the actual book.

Will post back if I have problems.

Would sure like to know what the lines of the code are doing, if you have the time and patients.

Harry

 

[jindon]

The code iswritten for particular range , I5:I9.

Sub test2()
Dim a(1 To 10), n As Long, i As Long
' a(1 To 10) because I5:I9 holds 5 Cells, then we need another 5 for Col.O
n = 1
For i = 5 To 9
     a(n) = Sheets("data").Range("i" & i).Value
     ' store col.I value to a(n)
     a(n + 1) = Sheets("data").Range("o" & i).Value
     ' store col.O value to a(n+1)
     n = n + 2  '<- increase the counter by 2
Next
Sheets("totals").Range("g3").Resize(,10).Value = a  ' outputting the data
End Sub

Here's a code without using array

Sub test2()
Dim r As Range, n As Long
With Sheets("data").Range("i5:i9")
     For Each r In .Cells
          r.Copy Sheets("result").Range("g3").Offset(,n)
          r.Offset(,6).Copy Sheets("result").Range("g3").Offset(,n+1)
          n = n + 2
     Next
End With
End Sub