Copy two vertical ranges on two sheets and paste to one horizontal range with last row.

[twinpowr]

hi guys

this one is driving me nuts. i can find last empty row with lastrow etc, i can copy the vertical ranges, but i need to covert them from vertical to horizontal

two ranges sheet 1 c4:c8, sheet 2 g10:g29

then copy and paste on the next free row on sheet 3 column C

i have tried various different methods

TmpArray = Application.Transpose(SourceRange.Value)
' DestinationRange.Resize(SourceRange.Columns.Count, SourceRange.Rows.Count).Value = TmpArray

and combinations of

pasteSheet.Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues

but i cant seem to put it all together

any help would be great before i lose all my hair.

 

[Fluff]

In what way doesn't it work?

 

[twinpowr]

its not pasting correctly or at all, or i get errors all over the place, i just cant seem to get the syntax correct.

 

[Fluff]

In that case can you please post all the code.

 

[twinpowr]

hi thanks

Sub TransposeHorizontalRangeValuesToVerticalRangeInAnotherSheet()
' Dim TmpArray() As Variant, SourceRange As Range, DestinationRange As Range
'
' Set SourceRange = Sheets("Quality Form").Range("c4:c8") '
' Set DestinationRange = Sheets("Data").Range("c") ' *this is the line that i cant seem to work out. last row instead of the range
'
' TmpArray = Application.Transpose(SourceRange.Value)
' DestinationRange.Resize(SourceRange.Columns.Count, SourceRange.Rows.Count).Value = TmpArr

this works for one range, but i cant get it to work for the two ranges and to only post on the next free row

 

[Fluff]

This Set DestinationRange = Sheets("Data").Range("c")should be

Set DestinationRange = Sheets("Data").Range("c" & Rows.Count).End(xlUp).Offset(1)

For two ranges you will need to do them individually.

 

[twinpowr]

thanks so much worked a treat. it was this bit Range("c" & Rows.Count).End(xlUp).Offset(1) that was tripping me up.

you are a star.

 

[Fluff]

Glad to help & thanks for the feedback.