I have a macro, that will (if it works!!!) save me lots of
time, the problem is, it will need to be used in multiple spreadsheets
all of which have exactly the same format but a unique set of values.

Basically a list cosisting of multple rows but only two columns needs
to be pivot tabled.

It works great on the sheet I created it in, but it fails on anyother.
I know I need to use somekind of active worksheet function or some
method that starts the macro in the active cell, but my macro code
refers to cells from the original sheet.

HELP - would be very much appreciated, then one day when your help has
made me clever enough, I could help a few people out also :)

Here is my macro; (I think the problem lies were I refer to cells and
tabs, I wonder if i can exchenge these references for ones which refer
solely to the active tab
as I have a button for this on my toolbar) - THANKS :)

Sub Pivot_V4()
'
' Pivot_V4 Macro
' Macro recorded 07/07/2005 by sherisg
'

'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"'0V4126'!R4C1:R25C2").CreatePivotTable TableDestination:= _
"'[Adopted Rec May 2005.xls]0V4126'!R6C5",
TableName:="PivotTable5", _
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable5").AddFields RowFields:="Stock
No"

ActiveSheet.PivotTables("PivotTable5").PivotFields ("Balance").Orientation
= _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
Range("F10").Select
ActiveSheet.PivotTables("PivotTable5").PivotFields ("Count of
Balance"). _
Function = xlSum
Range("A6:B6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("E8:F8").Select
Range(Selection, Selection.End(xlDown)).Select
Range("E8:F15").Select
Selection.Copy
Range("A6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Sort Key1:=Range("B6"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Columns("E:F").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub


--
Turin
------------------------------------------------------------------------
Turin's Profile: http://www.excelforum.com/member.php...o&userid=24987
View this thread: http://www.excelforum.com/showthread...hreadid=385195

Turin,

Here's a tidied up version of your macro. I've created a new worksheet
object variable to point to the worksheet in question.Should work OK!

Sub Pivot_V4()
Dim pt As PivotTable
Dim wsSourceSheet As Worksheet

'devine sheet for source data and pivot destination
Set wsSourceSheet = ActiveWorkbook.Sheets("0V4126")

'create pivottable
Set pt = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:=wsSourceSheet.Range("R4C1:R25C2")). _
CreatePivotTable(TableDestination:=wsSourceSheet.R ange("R6C5"),
TableName:="PivotTable5", _
DefaultVersion:=xlPivotTableVersion10)
pt.AddFields RowFields:="Stock No"

pt.PivotFields("Balance").Orientation = xlDataField

Application.CommandBars("PivotTable").Visible = False
Range("F10").Select
pt.PivotFields("Count of Balance").Function = xlSum
wsSourceSheet.Range(wsSourceSheet.Range("A6"),
wsSourceSheet.Range("B6").End(xlDown)).ClearConten ts

wsSourceSheet.Range("E8:F15").Copy Destination:=wsSourceSheet.Range("A6")
Application.CutCopyMode = False

With wsSource.Range("A6:B23")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
.Sort Key1:=Range("B6"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal
End With
Columns("E:F").Delete Shift:=xlToLeft
Range("A1").Select
End Sub

Cheers,
Dave

"Turin" wrote:


I have a macro, that will (if it works!!!) save me lots of
time, the problem is, it will need to be used in multiple spreadsheets
all of which have exactly the same format but a unique set of values.

Basically a list cosisting of multple rows but only two columns needs
to be pivot tabled.

It works great on the sheet I created it in, but it fails on anyother.
I know I need to use somekind of active worksheet function or some
method that starts the macro in the active cell, but my macro code
refers to cells from the original sheet.

HELP - would be very much appreciated, then one day when your help has
made me clever enough, I could help a few people out also :)

Here is my macro; (I think the problem lies were I refer to cells and
tabs, I wonder if i can exchenge these references for ones which refer
solely to the active tab
as I have a button for this on my toolbar) - THANKS :)

Sub Pivot_V4()
'
' Pivot_V4 Macro
' Macro recorded 07/07/2005 by sherisg
'

'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"'0V4126'!R4C1:R25C2").CreatePivotTable TableDestination:= _
"'[Adopted Rec May 2005.xls]0V4126'!R6C5",
TableName:="PivotTable5", _
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable5").AddFields RowFields:="Stock
No"

ActiveSheet.PivotTables("PivotTable5").PivotFields ("Balance").Orientation
= _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
Range("F10").Select
ActiveSheet.PivotTables("PivotTable5").PivotFields ("Count of
Balance"). _
Function = xlSum
Range("A6:B6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("E8:F8").Select
Range(Selection, Selection.End(xlDown)).Select
Range("E8:F15").Select
Selection.Copy
Range("A6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Sort Key1:=Range("B6"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Columns("E:F").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub


--
Turin
------------------------------------------------------------------------
Turin's Profile: http://www.excelforum.com/member.php...o&userid=24987
View this thread: http://www.excelforum.com/showthread...hreadid=385195