VBA Excel - Paste Special (Transpose)

[Fortisdragon]

Hi everyone!

I've got another problem in excel vba (Excel 97)...
I want to (simply!) paste data from one sheet to another one in the same workbook. The famous so-called "HelpFile" show me this code for the "paste special format" function:

<expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)
Paste Optional Variant. The part of the range to be pasted. Can be one of the following XlPasteType constants: xlPasteAll, xlPasteFormulas, xlPasteValues, xlPasteFormats, xlPasteNotes, or xlPasteAllExceptBorders. The default value is xlPasteAll.
Transpose Optional Variant. True to transpose rows and columns when the range is pasted.The default value is False.>

...and I wrote the underlying lines to just test the &quot;past values&quot; method...

<Worksheets(&quot;parameters&quot.Range(&quot;k2&quot.Copy
Worksheets(&quot;simulation&quot.Range(&quot;h&quot; & iBeginW).PasteSpecial (xlPasteValues)>

... and it works pretty well!

But when I want to transpose the values the code should be the following:

<Worksheets(&quot;parameters&quot.Range(&quot;k2&quot.Copy
Worksheets(&quot;simulation&quot.Range(&quot;h&quot; & iBeginW).PasteSpecial (xlPasteValues, , ,True)>

... and the answer is: &quot;Compile Error Expected: =&quot; and make my linecode invalid!

I can't find any answer in the so-called &quot;Help File&quot;... so some help will be appreciated

Fortisdragon

 

[xlbo]

Always use descriptors
Pastespecial Paste:=values , Transpose:=true

There could be multiple options that can be true or false so how is vba gonna know which one unless you specify

Or - and this may be stretching it a bit - you could even use the &quot;Record&quot; functionality. Record the process and see the code that gets spewed out - just a suggestion of course but I get this:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

Kinda think it may be useful before you go dissing the helpfile

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Fortisdragon]

Well... I feel myself like Dobby (Harry Potter)... Stupid!

My goal is not to denigrate the HelpFile but sometimes it takes me hours to correct myself the synthax inside the &quot;Help File Examples&quot;.

So Thanks a lot to avoid me loosing too much time!

FD

 

[xlbo]

no worries - just remember ......&quot;Macro Recorder&quot; it is possibly the most helpful feature that M$ have ever implemented. If you have an issue with a code segment, just record yourself performing the action and the correct syntax will be recorded for you

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244