Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Adjusting Macro (Excel 2000)

    I am trying to record an Excel macro. Let
    H Lewton

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Adjusting Macro (Excel 2000)

    Here's a simple approach to set the source data area:

    Dim rngData As Range
    Set rngData = Intersect(ThisWorkbook.Worksheets("Vendors").Colum ns("A:M"), _
    ThisWorkbook.Worksheets("Vendors").UsedRange)
    ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabas e, SourceData:= _
    rngData).CreatePivotTable TableDestination:="", TableName:= _
    "PivotTable2", DefaultVersion:=xlPivotTableVersion10
    <etc.>
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adjusting Macro (Excel 2000)

    John,

    Thanks but I have to ask. Do I put this code at the very beginning of the macro? Can I insert rows as necessary within the range named "Data" allowing it to grow to whatever size is necessary and be included in the pivot table when it is recreated? I guess I can experiment to find out these answers, huh?
    H Lewton

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Adjusting Macro (Excel 2000)

    By convention, Dimming data variable Types should be stated at the very beginning. Setting the Range "rngData" must be done before the code that creates the PivotTable, since the Pivottable should now use the Range "rngData".
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adjusting Macro (Excel 2000)

    John,

    Thanks that is where I put it.
    H Lewton

  6. #6
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adjusting Macro (Excel 2000)

    John,

    For some reason this code seems to work in Excel 2003 but not in Excel 2000. Can it be altered to work for Excel 2000 as well as Excel 2003?

    Thanks.
    H Lewton

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Adjusting Macro (Excel 2000)

    What does the code do to indicate that it doesn't work?

    Steve

  8. #8
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adjusting Macro (Excel 2000)

    Steve,

    Dim rngData As Range
    Set rngData = Intersect(ThisWorkbook.Worksheets("Vendors").Colum ns("A:M"), _
    ThisWorkbook.Worksheets("Vendors").UsedRange)
    ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabas e, SourceData:= _
    rngData).CreatePivotTable TableDestination:="", TableName:= _
    "PivotTable2", DefaultVersion:=xlPivotTableVersion10

    I believe it executes to the point that I have bolded. It then stops with a "Compilation error: dialog box. When I hit OK I see the macro name is highlighted in yellow but the bolded entry is highlighted as if that is the place it actually quit executing. It does run in Excel 2003 but not 2000.

    Thanks.
    H Lewton

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Adjusting Macro (Excel 2000)

    I still use xl97 so I am making a "leap"

    Perhaps the parameter "defaultversion" was added in xl2003 and is not available in xl2000. What happens if you change your line of code to:
    <pre>ThisWorkbook.PivotCaches.Add(SourceType:=xlDa tabase, SourceData:= _
    rngData).CreatePivotTable TableDestination:="", TableName:= _
    "PivotTable2" ', DefaultVersion:=xlPivotTableVersion10</pre>


    commenting out the last parameter (you could delete it later if desired).

    Does the code work now?

    Steve

  10. #10
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adjusting Macro (Excel 2000)

    Steve,

    If I do that I get a Run-Time error '5':
    Invalid procedure call or argument

    And it highlights in yellow this entire portion of the code,
    ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabas e, SourceData:= _ rngData).CreatePivotTable TableDestination:="", TableName:= _ "PivotTable2" ', DefaultVersion:=xlPivotTableVersion10

    Thanks
    H Lewton

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Adjusting Macro (Excel 2000)

    Is it a word wrap issue? after each line extender (the underscore = "_" you must have a return):

    ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabas e, SourceData:= _
    rngData).CreatePivotTable TableDestination:="", TableName:= _
    "PivotTable2" ', DefaultVersion:=xlPivotTableVersion10

    Steve

  12. #12
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adjusting Macro (Excel 2000)

    Steve,

    I don't think it is a word wrap issue because all I did was put the ' in like this ', DefaultVersion:=xlPivotTableVersion10 because that is the only difference I saw in the code. I don't know how to see if it has a return after the extender in the macro editor but I checked the one you typed in Word and it does have the return so I copied it into the macro and the results were the same. I then went into the macro editor and put the returns in manually and still the results were the same.

    I believe it has something to do with the words "DefaultVersion:" but I don't know what to change it to.

    Thanks.
    H Lewton

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Adjusting Macro (Excel 2000)

    As I mentioned I don't use XL2000, or XL2003, but you could try the XL97 equivalent:

    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _
    SourceData:=rngdata, TableDestination:="", _
    TableName:="PivotTable2"

    Steve

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Adjusting Macro (Excel 2000)

    You have a syntax error in the following from your post above:

    ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabas e, SourceData:=<font color=red> _</font color=red> rngData).CreatePivotTable TableDestination:="", TableName:=<font color=red> _</font color=red> "PivotTable2" ', DefaultVersion:=xlPivotTableVersion10

    in that in two locations the line of code must break to the next line after the " _" space and line break.

    I don't have Excel 2000, but in Excel XP 'DefaultVersion' is an Optional Property of 'CreatPivotTable'. ('TableName' and 'ReadData' are also Optional.) I think Steve is correct, since it is optional, delete it, correct your syntax, and see if the code runs.
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adjusting Macro (Excel 2000)

    Steve,

    Thanks. I'll give it a try.
    H Lewton

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •