MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Returning values instead of function in Transpose Macro


Posted by Tom on March 04, 2001 8:48 PM

Hello,

I'm a VBA & macro rookie (just started this weekend), and I found a macro on this website to tranpose data that a pivot table can more readily use.

I've modified it to the point where it almost works, except that it returns the function from the source data, rather than the values. I need the latter.

I'm sure there is a simple command that will perform this, but given my naivete, and after searching several sources, I humbly throw in the towel.

Here's the code:

' Copy the data for this month to column D
Range(ThisCol & "4:" & ThisCol & FinalRow).Copy _
Destination:=Sheets("OutTranspose").Range("D" & NextRow


The source data is a Vlookup function, referencing another worksheet. I want to transpose the "values only", so that I can port it to a pivottable.

Many Thanks,

Tom


Posted by Dave Hawley on March 05, 2001 4:56 AM


Hi Tom

Not too sure I follow you, but you can PasteSpecial as Values only like below:

Copy the data for this month to column D
Range(ThisCol & "4:" & ThisCol & FinalRow).Copy
Sheets("OutTranspose").Range("D" & NextRow).PasteSpecial _
(xlPasteValues)


The Paste Special function also has a "Transpose" option wich you may be able to use like this:

Copy the data for this month to column D
Range(ThisCol & "4:" & ThisCol & FinalRow).Copy
Sheets("OutTranspose").Range("D" & NextRow).PasteSpecial _
xlPasteValues, Transpose:=True


Dave


OzGrid Business Applications