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

twinpowr

Board Regular
Joined
Mar 14, 2007
Messages
73
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,940
Office Version
  1. 365
Platform
  1. Windows
In what way doesn't it work?
 

twinpowr

Board Regular
Joined
Mar 14, 2007
Messages
73
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,940
Office Version
  1. 365
Platform
  1. Windows
In that case can you please post all the code.
 

twinpowr

Board Regular
Joined
Mar 14, 2007
Messages
73
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,940
Office Version
  1. 365
Platform
  1. Windows
This Set DestinationRange = Sheets("Data").Range("c")should be
VBA Code:
Set DestinationRange = Sheets("Data").Range("c" & Rows.Count).End(xlUp).Offset(1)
For two ranges you will need to do them individually.
 
Solution

twinpowr

Board Regular
Joined
Mar 14, 2007
Messages
73
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,940
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Forum statistics

Threads
1,182,107
Messages
5,933,697
Members
436,905
Latest member
Ibraeh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top