Solved: linking to a range of cells

The solution to this may or may not be VBA but I'll post it anyway in the hope that I can avert the mind-deadening tedium:

1. I have multiple rows with 27 columns of Sales by product, running horizontally across the page.
2. For a Pivot table I need to link to these entire ranges, however the data for the Pivot table has to run vertically, in a single column, i.e. it needs to be transposed.
3. I tried to do this by naming the ranges but I may be doing it incorrectly because I get a #VALUE! error

It would have been easy enough if I simply had to transpose and copy the data however in this case I need to have live links. Is there a way to do this in an "automated" fashion without having to link to each cell individually by pointing and clicking?

Many thanks!

[vba]

Public Sub ProcessData()
Dim LastRow As Long
Dim LastCol As Long
Dim i As Long, j As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = LastRow To 2 Step -1

For j = LastCol To 2 Step -1

If .Cells(i, j).Value2 <> "" Then

.Rows(i + 1).Insert
.Cells(i, "A").Copy .Cells(i + 1, "A")
.Cells(1, j).Copy .Cells(i + 1, "B")
.Cells(i, j).Copy .Cells(i + 1, "C")
End If
Next j

.Rows(i).Delete
Next i
End With

End Sub
[/vba]

I have 24 rows with 27 columns to transpose and link to. Do I have to run the macro 24 times (once for each row)? Also the transposed data is on a different sheet than the original data.

No just once.

Copy it to a new sheet and run against that sheet.

The commands read "copy .cells," will this actually give me data links or just copy the data?

It will restructue the actual data.

Unfortunately it does not work for me. Perhaps I should have highlighted the fact that the rows that need to be transposed and linked to are not part of a contiguous data table. I am dealing of blocks of data and the rows in question occur at an interval of 6 (e.g. the first incidence is Row 9, the next is Row 15, the next Row 21, and so on). Thanks for trying to help.

I made up this to create a linked transposed table. Any use?

Quote:

---

Originally Posted by mdmackillop

I made up this to create a linked transposed table. Any use?

---

Extremely interesting, thanks. However, I am trying to do the reverse, i.e. take data arranged horizontally, across a row, and link to it creating a single column of data in. Also would this work if the row of data and the target column were in different worksheets of the same workbook?

Quote:

---

Originally Posted by K. Georgiadis

Unfortunately it does not work for me. Perhaps I should have highlighted the fact that the rows that need to be transposed and linked to are not part of a contiguous data table. I am dealing of blocks of data and the rows in question occur at an interval of 6 (e.g. the first incidence is Row 9, the next is Row 15, the next Row 21, and so on). Thanks for trying to help.

---

Why don't you post the workbook.

Quote:

---

Originally Posted by K. Georgiadis

Extremely interesting, thanks. However, I am trying to do the reverse, i.e. take data arranged horizontally, across a row, and link to it creating a single column of data in. Also would this work if the row of data and the target column were in different worksheets of the same workbook?

---

Yes

Quote:

---

Originally Posted by mdmackillop

Yes

---

It works like a charm! Many thanks!

Marked as "solved."

Quote:

---

Originally Posted by xld

Why don't you post the workbook.

---

It is too large a file and contains proprietary information. Meanwhile MdMcKillop's suggestion has solved the problem completely. Thanks again.