Transposing columns to rows with links

[Festus Hagen]

Hi all,

The timeout on this site bites ... It's rather annoying to lose your post because you can't type as fast as the system thinks you should!!!

Any ways ...

I have been trying to find a method to transpose columns from sheet1 to rows in sheet2 with references (links) to the original data in sheet1 ... NOT COPIED duplicate data as all the ones I find do ...

thus sheet2 would look like ...
Sheet1!A1 Sheet1!A2 Sheet1!A3
Sheet1!B1 Sheet1!B2 Sheet1!B3

Currently I am using an 'offset' method in sheet2, It's slow!

Any suggestions??

Thanks
-Enjoy
fh : )_~

 

[wigi]

For instance:

Sub Macro1()
    Dim rng As Range
    Sheets(2).Activate
    For Each rng In Sheets(1).Range("A1:B10").SpecialCells(2)
        rng.Copy
        Cells(rng.Column, rng.Row).Select
        ActiveSheet.Paste link:=True
    Next
End Sub

 

[Festus Hagen]

Created a new work book, it created sheet1, sheet2, sheet3, I copied and pasted Sheet1!A1:B37 data from the original workbook to Sheet1!A1:B37 of the new workbook.

pasted the code, made one change ... the range from "A1:B10" to "A1:B37".

Get an error: Unable to get the SpecialCells property of the Range class.

Could this be due to the fact that I forgot to mention my excel version ...
I am using Excel 97.

I didn't understand the '2' used in SpecialCells what constant does '2' equal??

-Enjoy
fh : )_~

 

[Festus Hagen]

For instance:

Sub Macro1()
    Dim rng As Range
    Sheets(2).Activate
    For Each rng In Sheets(1).Range("A1:B10").SpecialCells(2)
        rng.Copy
        Cells(rng.Column, rng.Row).Select
        ActiveSheet.Paste link:=True
    Next
End Sub

Got it ...

I ***_U_ME'ed: 2=xlCellTypeConstants

Changed:

• Cells(rng.Column, rng.Row).Select
• ActiveSheet.Cells(rng.Column, rng.Row).Select

Thus end results:

Sub Macro1()
  Dim rng As Range
  Sheets(2).Activate
  For Each rng In Sheets(1).Range("A1:B10").SpecialCells(xlCellTypeConstants)
    rng.Copy
    ActiveSheet.Cells(rng.Column, rng.Row).Select
    ActiveSheet.Paste link:=True
  Next
End Sub

Presto, It works!

Awesome! I am pleased, It popped my brain fart ... Many thanks!

-Enjoy
fh : )_~

 

[wigi]

According to me, both changes are not necessary (they do not add anything essential) and my version worked perfectly in my test.

But if it works now for you, I'm happy too.

 

[Festus Hagen]

Who knows ... For some reason my system doesn't understand the '2', I had to change it to one of the Excel constants. No biggie!

Any ways, It works perfect for what I asked for, It got me going, It does the job, thats all that matters... Of course I have changed it a bunch, it's dynamic now, using CurrentRegion. Love it, many thanks!

Tried to rep ya, couldn't find the ability!

QUESTION:
I have another use for it and have tried to tweak it to Skip columns on the paste.

So the destination (sheet2) looks like:
Sheet1!A1 EMPTYCELL Sheet1!A2 EMPTYCELL Sheet1!A3
Sheet1!B1 EMPTYCELL Sheet1!B2 EMPTYCELL Sheet1!B3

Can't figure it out, Any suggestions ??

Thanks a bunch.

-Enjoy
fh : )_~

 

[Festus Hagen]

QUESTION:
I have another use for it and have tried to tweak it to Skip columns on the paste.

So the destination (sheet2) looks like:
Sheet1!A1 EMPTYCELL Sheet1!A2 EMPTYCELL Sheet1!A3
Sheet1!B1 EMPTYCELL Sheet1!B2 EMPTYCELL Sheet1!B3

Can't figure it out, Any suggestions ??

Got it ...

Cells(rng.Column, rng.Row).Offset(0, rng.Row + 1).Select

Thanks

-Enjoy
fh <font color="#FF0000">:</font> )_~