PDA

View Full Version : Solved: linking to a range of cells



K. Georgiadis
03-23-2010, 05:59 AM
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:

I have multiple rows with 27 columns of Sales by product, running horizontally across the page.
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.
I tried to do this by naming the ranges but I may be doing it incorrectly because I get a #VALUE! errorIt 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!

Bob Phillips
03-23-2010, 06:31 AM
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

K. Georgiadis
03-23-2010, 06:45 AM
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.

Bob Phillips
03-23-2010, 06:55 AM
No just once.

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

K. Georgiadis
03-23-2010, 07:59 AM
The commands read "copy .cells," will this actually give me data links or just copy the data?

Bob Phillips
03-23-2010, 08:39 AM
It will restructue the actual data.

K. Georgiadis
03-23-2010, 09:16 AM
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.

mdmackillop
03-23-2010, 10:41 AM
I made up this to create a linked transposed table. Any use?

K. Georgiadis
03-23-2010, 10:52 AM
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?

Bob Phillips
03-23-2010, 10:59 AM
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.

mdmackillop
03-23-2010, 11:03 AM
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

K. Georgiadis
03-23-2010, 11:41 AM
Yes
It works like a charm! Many thanks!

Marked as "solved."

K. Georgiadis
03-23-2010, 12:47 PM
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.