Excel 97 macro error

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Gram123

Thread Starter
Joined
Mar 15, 2001
Messages
1,829
Hi.
I created a macro for one of my co-wokers that:

Refreshes data using MS Query to pull data from an Access database.
It then formats fonts and headers and stuff correctly on that worksheet.
It then creates a pivot table on a new sheet (using the wizard) based on this refreshed info (it's actually based on a cell range 8 columns wide and 10,000 rows long excluding the header, just in case we get a huge number of claims on one ship).
It then formats the pivot table correctly and renames the worksheet.
Finally, it moves the worksheet to where it should be (still in same workbook).

This worked the first time, and still works now, except for the last part, where it moves the sheet. I now receive an error:

Run-time error '1004':
Application-defined or object-defined error.

I hit Debug to see where the problem was (I know very little VB) and this is what was there. The Bold part is what was highlighted in yellow in the VB window:

Sub Refresh_Pivot()
'
' Refresh_Pivot Macro
' Macro recorded 04/09/01 by Liz Clark
'
Range("A10").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("A10:H10").Select
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.ColorIndex = 11
With Selection.Font
.Name = "Times New Roman"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 11
End With
Selection.Font.Italic = True
Range("A11").Select
ActiveWindow.SmallScroll ToRight:=3
Columns("E:E").ColumnWidth = 10
Columns("F:F").ColumnWidth = 10
Columns("G:G").ColumnWidth = 10
Columns("H:H").ColumnWidth = 10
ActiveWindow.SmallScroll ToRight:=-3
Range("A10:H10000").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Detailed Report'!R10C1:R10000C8", TableDestination:="", TableName:= _
"PivotTable1"
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Chassis No"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Total")
.Orientation = xlDataField
.Name = "Sum of Total"
.Function = xlSum
End With
Columns("B:B").Select
Selection.NumberFormat = "$#,##0.00"
Selection.Font.Bold = True
Range("D10").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Pivot Table"
Sheets("Pivot Table").Select

ActiveSheet.PivotTables("PivotTable1").PivotSelect _
"VF1CB05CF25064436:VF1CB05CF25109478", xlDataAndLabel


Range("C23").Select
Sheets("Pivot Table").Select
Sheets("Pivot Table").Move After:=Sheets(2)
Sheets("Detailed Report").Select
Range("A1").Select
End Sub

Now it seems to me that the VB is looking for specific Chassis numbers in a range ("VF1CB05CF25064436:VF1CB05CF25109478"), and this is causing (or at least part of) the problem. These chassis numbers will be different every time the macro is run.
It is not vital that this sheet is moved, but it would be preferable.

Any help is greatly appreciated!

Gram
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,746
Why is it using chassis numbers instead of a cell refs? Doh! Anyway, if it's always a specific range of cells you're looking at, give it a named range instead. See if that helps.
 

Gram123

Thread Starter
Joined
Mar 15, 2001
Messages
1,829
How do I do that?
I've messed around with it a bit, but to no avail...
It just gave me "subscript out of range errors" instead.

Thanks again,

Gram
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,746
Select the range of cells and hit Insert-Name-Define and type a name in.

In your code, refer to the named range. If you don't know how, record it to get the code by recording:

Edit-Goto and double-click the name you gave your range.
 

Gram123

Thread Starter
Joined
Mar 15, 2001
Messages
1,829
Thanks Dreamboat.

I re-recorded the macro using a named range and that solved that problem. However, then I got another error between it moving the pivot table sheet and renaming it.
I guess this is to do with the default worksheet names (Sheet 1, Sheet 2, etc).
So, I just renamed the sheet first and then moved it.
Iv'e tested it on different ships, with different numbers of claims etc and it seems to be working fine.

Thanks for all your help.

Gram
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top