transpose data from 2 sheets

1
votes

there are, in a sense, two answers to this question here at overflow. So, I'd like to ask which is "better"

First solution can be found here Excel VBA - Range.Copy transpose paste and essentially it suggests this

Worksheets("Sheet1").Range("A1:A5").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True

I haven't yet tied this, but it looks as if it should work

Next solution can be found here Transpose a range in VBA and uses this method

Sub transposeTest()
Dim transposedVariant As Variant
Dim sourceRowRange As Range
Dim sourceRowRangeVariant As Variant

    Sheets("Sheet1").Select  ' NOTE: I added this

    Set sourceRowRange = Range("A1:H1") ' one row, eight columns
    sourceRowRangeVariant = sourceRowRange.Value
    transposedVariant = Application.Transpose(sourceRowRangeVariant)

    Sheets("Sheet2").Select  ' NOTE: I added this

    Dim rangeFilledWithTransposedData As Range
    Set rangeFilledWithTransposedData = Range("I1:I8") ' eight rows, one column
    rangeFilledWithTransposedData.Value = transposedVariant
End Sub

I have employed this and yes it does work, although I had to use Sheets("Sheet1").Select and Sheets("Sheet2").Select in the blank space above. I tried all in one statement like Set sourceRowRange = Sheets("Sheet1").Range(Cells(1,1), Cells(1, 8)), but apparently the sheet must be selected before a range can be Set to it.

I'm hoping some one could explain why one is better than the other.

2
exceltransposevba
have you thought about trying them both and see which one fits better with your current situation, keeping in mind understanding of what the code is doing and if it's something you can modify given your current ability? If you work through that, you should have an idea which is "better" than the other. – sous2817

2 Answers

1
votes

Not an answer but too big for a comment...

Cells() without a worksheet qualifier always defaults to the current Active sheet, so you should do something like this:

With Sheets("Sheet1")
    Set sourceRowRange = .Range(.Cells(1,1), .Cells(1, 8))
End With

Or (simpler):

Set sourceRowRange = Sheets("Sheet1").Cells(1,1).Resize(1,8)
1
votes

On such a small set of data, you can't say which is better. In your scenario, my personal choice would be Option1. Why? Because it is less code. :)

Pros of Option 1

  1. Less code. Just two lines.
  2. It is faster.

Cons of Option 1

Uses the clipboard so that may create problems if not used judiciously.

For example

Worksheets("Sheet1").Range("A1:A5").Copy
Worksheets("Sheet1").Range("X1").Value= "Sid"
Worksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True

The problem with this is Excel clears the clipboard if you perform any action (Such as writing to a cell) and hence the next PasteSpecial line will fail as it doesn't find anything in the clipboard. There have been instances when even the below two lines will fail on large set of data.

Worksheets("Sheet1").Range("A1:A5").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True

Reason being, the data has not been fully placed in the clipboard and the next line is executed and it fails as it doesn't find anything in the clipboard. So to counter it, one can use the below method

Worksheets("Sheet1").Range("A1:A5").Copy
DoEvents
Worksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True

Pros of Option 2

Doesn't use the clipboard. The data is stored in the array. Once the data is stored, it can be used many times. You don't have to "Copy" the data again and again as you may have to do in case of Option1 if the clipboard gets cleared.

Cons of Option 2

As compared to Option1, it has more lines of code.