Avoiding Clipboard

[Jlascu]

Hi to all,

Apologies if this has been addressed before.
I've been trying to copy values from one workbook and paste them transposed on a different workbook.
The current macro using copy/paste transpose works well and does the trick, but takes forever to run.
is there a way to avoid using the clipboard?

many thanks in advance!

Jose

 

[Fluff]

Hi & welcome to the board.
Something like

Sub CopyTrans()
Range("L2:R2").Value = Application.Transpose(Range("K2:K8"))
End Sub

 

[Jlascu]

Many thanks!! Looking forward to help in any way I can! And thanks for the help. tried it but have a few errors.
This is the code using the clipboard:

[I]                        'Copy data from[/I]
[I]                        Windows(sSPVFileName).Activate[/I]
[I]                        Worksheets("" & (a) & "").Select[/I]
[I]                        'Range(sImportRangeCopy).Select[/I]
[I]                        Range(Cells(ir1, ic1), Cells(ir2, ic2)).Select[/I]
[I]                        Selection.Copy[/I]

[I]                        'Copy data to
[/I][I]                        Windows(sDPMPName).Activate[/I]
[I]                        Worksheets("Exp").Select[/I]
[I]                        Range(sImportRangePaste).Select[/I]
[I]                        Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True[/I]

I tried many different ways with the suggestions, but none seem to work. Many thanks again!

 

[Fluff]

If sImportRangePaste is a single cell, try

'Copy data from
Dim rng As Range
   Windows(sSPVFileName).Activate
   Set rng = Worksheets("" & (a) & "").Range(Cells(ir1, ic1), Cells(ir2, ic2))
   
   'Copy data to
   Windows(sDPMPName).Worksheets("Exp").Range(sImportRangePaste).Resize(rng.Columns.Count, rng.Rows.Count).Value = Application.Transpose(rng)

 

[Jlascu]

Many thanks again! ... been trying several combinations, but get "Run-time error 438: Object doesn't support this property or method"
Not sure what is happening, any ideas?

 

[Fluff]

What is sImportRangePaste?

 

[Jlascu]

Many thanks for all the help, you have no idea how much this is helping!!
sImportRangePaste is a string variable. it has a value of "A2" when macro is running

 

[Fluff]

What line gets highlighted, when you et the error?

 

[Jlascu]

This line

'Copy data to
Windows(sDPMPName).Worksheets("Exp").Range(sImportRangePaste).Resize(rng.Columns.Count, rng.Rows.Count).Value = Application.Transpose(rng)

sImportRangePaste = "A2"
rng.Columns.Count = 26
rng.Rows.Count = 7

 

[Fluff]

Try

Windows(sDPMPName).Activate
Worksheets("Exp").Range(sImportRangePaste).Resize(rng.Columns.Count, rng.Rows.Count).Value = Application.Transpose(rng)