pivot table truncation memo field

[wynnyderpooh]

Excel Pivot Tables truncate cells to 255 characters when you double-click to show detail. I need VBA code to retrieve the complete cell value from the original detail tab:

My Code:

Sub fetch_Comments()

Dim rngCurrent As Range
Dim rngCell As Range
Dim rowct As Integer

Sheets("Research").Select
Range("q9").Select

Set rngCurrent = Research.Range("q9").CurrentRegion 
'here I get an Error Message;"object required"; in the watch window Current Region does not populate a value

rowct = CurrentRegion.Rows.Count
rngCurrent = Sheets("Research").Range("q9:q rowCt")

rngCurrent.Select
For Each rngCell In rngCurrent
    If Len(rngCell) > 255 Then
        rngCell = Sheets("Details with Comments").Range("q rowCt")
    End If
Next rngCell

End Sub

[dc42]

Hi

Have had a play with the code, and changed a little bit of your syntax. Try this:

Sub fetch_Comments()

Dim rngCurrent As Range
Dim rngCell As Range
Dim rowct As Integer

Sheets("Research").Select
Range("q1").Select

Set rngCurrent = ActiveCell.CurrentRegion

rowct = ActiveCell.CurrentRegion.Rows.Count
Sheets("Research").Range("q1:q" & rowct).Select

For Each rngCell In rngCurrent
    If Len(rngCell) > 255 Then
        rngCell = Sheets("Details with Comments").Range("a rowCt")
    End If
Next rngCell

End Sub

Once the cell is selected then ActiveCell can be used to identify those cells occupied within the current region.

Hope this helps

Debs

[dc42]

OOps.. i changed some of the cell references for testing. Here's a version you can copy to try

Sub fetch_Comments()

Dim rngCurrent As Range
Dim rngCell As Range
Dim rowct As Integer

Sheets("Research").Select
Range("q9").Select

Set rngCurrent = ActiveCell.CurrentRegion

rowct = ActiveCell.CurrentRegion.Rows.Count
Sheets("Research").Range("q9:q" & rowct).Select

For Each rngCell In rngCurrent
     If Len(rngCell) > 255 Then
         rngCell = Sheets("Details with Comments").Range("q rowCt")
     End If
Next rngCell

End Sub