Transpose a variable range of columns to rows

[flarrie]

Hi all,

For my work I frequently need to transpose a range of columns to rows.
The amount of columns needed to transpose is variable for each time.

The data is in just one row and multiple columns and should go to multiple rows and just one column.
Using the regular transpose function works but takes to much time since we need to count the amount of columns (which varies between 300 and 1500) and select the same amount of rows in sheet2

Is there a possibility to create an excel sheet where on sheet1 row 1 we paste the data and then click on a button and excel transposes the data to column A of sheet2?

Thanks in advance.

Flarrie

 

[flarrie]

Hi all,

What I would like to achieve looks like this

Sheet 1
A B C D E [X]
1 ! @ # $ % ...

Sheet 2

A1 !
2 @
3 #
4 $
5 %
[X] ...

The amount of columns will be variable with each run of the macro/VBA script

 

[Gary McMaster]

Maybe this:

In a standard module:

Public Sub Test()

Worksheets("Sheet1").Range("1:1").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

End Sub

 

[flarrie]

Hi Gary,

That part works perfectly thanks.
I've configured it so far it opens sheet 2 after pasting as well.
Would there be a possibility that it automatically copies the variable range of cells in column A?

we always need to paste it into a different application after transposing it.

 

[Gary McMaster]

If I understand your question, Maybe this:

Public Sub Test()

Worksheets("Sheet1").Range("1:1").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Worksheets("Sheet2").Range("A1:A" & Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row).Copy

End Sub

On the clipboard is what you're asking???

 

[flarrie]

Hi Gary,

Yes indeed copy to clipboard.
The solution you gave works perfectly for what I need

Thanks very much.