Hi,

I had copied this VB script thanks to Ceci which I had seen on the
newsgroup sometime last year, as it looked like it applied to my own
work (script attched below). It converts list data in pivot table form
into a summary table when the cells in the value area are in string form
(instead of the standard numbers).

I've run it before so I'm overall sure it works. But somehow in my
copy, I think I put a typo somewhere because now there's an error
cropping up just after the following lines:

With Range("F1")
..PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _

The error is "compile error: subs or function not defined".

Does anyone know how to solve this?

Many thanks.

Matthew Kramer

Here's the script

Sub tconv1()
Dim LRow As Double
Dim LRowYear As Double
Dim LRowMcity As Double
Dim i As Double
Dim x As Double
Dim y As Double
Dim McityRng As Range
Dim YearRng As Range

LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("E1:F1").EntireColumn.Clear
Range("A1:A" & LRow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("E1"), Unique:=True
Range("B1:B" & LRow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("F2"), Unique:=True
LRowMcity = Range("E" & Rows.Count).End(xlUp).Row
LRowYear = Range("F" & Rows.Count).End(xlUp).Row
Range("F2:F" & LRowYear).Sort Key1:=Range("F3"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("F3:F" & LRowYear).Copy
With Range("F1")
..PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
End With
Application.CutCopyMode = False
Range(Cells(2, 6), Cells(LRowYear, LRowYear + 4)).Clear
Set McityRng = Range("E1:E" & LRowMcity)
Set YearRng = Range(Cells(1, 5), Cells(1, LRowYear + 3))
For i = 2 To LRow
x = Evaluate("Match(" & Range("A" & i).Address & "," & _
McityRng.Address & ", 0)")
y = Evaluate("Match(" & Range("B" & i).Address & "," & _
YearRng.Address & ", 0)") + 4
Cells(x, y).Value = Range("C" & i).Value
Next i
End Sub





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!