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

 

[pbt]

Ummm

I'm trying to understand the codes.

I just tried the code without using array and because col O has formulas, it gives me a #VALUE! when pasted to the Totals sheet.

 

[jindon]

If you want to copy/paste formula then you need to use PasteSpecial, I think...

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).PasteSpecial xlPasteFormulas
          n = n + 2
     Next
End With
End Sub

 

[jindon]

If the above doesn't work, try

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).Formula
     n = n + 2
Next
Sheets("totals").Range("g3").Resize(,10).Value = a
End Sub

Note
In this case, forumal should be written with the sheet reference clearly even the formula is referencing the cell(s) within the same sheet.

 

[pbt]

No I do not want formulas........just values

 

[jindon]

Then change

xlPasteFormulas to

xlPasteValues

 

[jindon]

Then change

xlPasteFormulas to

xlPasteValues

Or

Sub test()
Dim r As Range, n As Long
For Each e In Sheets("data").Range("i5:i9")
     Sheets("totals").Range("g3").Offset(,n).Value = r.Value
     Sheets("totals").Range("g3").Offset(,n+1).Value = r.Offset(,6).Value
     n = n + 2
Next
End Sub

 

[pbt]

Sorry, I think this thread is getting a little too long and confusing.

This works just fine:

Sub test4()
    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

I was just trying to understand your without using array formula:

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

Which gave me the #VALUE! error from col "O" because it is a formula.

 

[jindon]

Have tried this one?

Sub test()
Dim r As Range, n As Long
For Each e In Sheets("data").Range("i5:i9")
     Sheets("totals").Range("g3").Offset(,n).Value = r.Value
     Sheets("totals").Range("g3").Offset(,n+1).Value = r.Offset(,6).Value
     n = n + 2
Next
End Sub