Runtime Error 1004 "Method Range Of Object Global Failed"

[CoreyReynolds]

Hey all,

I have a piece of code that dumps a bunch of data into a spreadsheet. Also rearranges it into a pivot table and then graphs the pivot table as well so my boss can get a clear view of the data.

This question is two part. One, I seem to be getting the title error at the starting line stating:

        oSheet.Range(rng).Select
         With Selection
             shp.Left = .Left
             shp.Top = .Top
             shp.Width = .Width
             shp.Height = .Height
         End With

What is driving me insane, is that it only happens sometimes. Like half the time.

The other issue I'm having (and I am very novice I will admit) is that I don't know how to properly place the pivot chart I am creating at the end of the below code... what I would like to do is have it on a different sheet but I have tried a few things without results. I'm just very confused, feeling burnt out and I have a day off work here so I was wondering if maybe some of you can confuse me!

Public Sub CreateDAOChartM(strSourceName As String, _
      strChartLabel As String)
'On Error GoTo unexError
Dim i As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(strSourceName)
 
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oSheetMain As Excel.Worksheet
 
Dim FinalRow As Long
Dim FinalCol As Long
 
Dim oChartObj As Excel.Chart
Dim oColPoint As Excel.Point
Dim oSourceRange As Excel.Range
 
 
Set oBook = oApp.Workbooks.Add
 
'This used to set the Worksheet to the first page of the sheet
Set oSheet = oBook.Worksheets(1)
 
'OLD CREATION
'populate sheet 2 with data
    Dim iNumCols As Integer
    iNumCols = rs.Fields.Count
    For i = 1 To iNumCols
        oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
    Next
    'Add Data to sheet
    oSheet.Range("A2").CopyFromRecordset rs
    'format header as bold and autofit columns
    With oSheet.Range("a1").Resize(1, iNumCols)
        .Font.Bold = True
        .EntireColumn.AutoFit
    End With
 
Dim pt As PivotTable
Dim pf As PivotField
 
 
'oBook.PivotCaches.Create(xlDatabase, oSheet.UsedRange).CreatePivotTable TableDestination:="", TableName:="PivotTable1"
oBook.PivotCaches.Create(xlDatabase, oSheet.UsedRange).CreatePivotTable TableDestination:="", tableName:="PivotTable1"
oSheet.PivotTableWizard TableDestination:=oSheet.Cells(1, 5), tableName:="Pivot1"
 
Set pt = oSheet.PivotTables("Pivot1")
Set pf = pt.PivotFields(strChartLabel)
 
'Going to have to figure out how to get copper out
oSheet.PivotTables("Pivot1").AddFields ColumnFields:=Array("equipmentID", "sampleDate")
 
With pf
        .Orientation = xlDataField
        .Function = xlAverage
End With
 
         pt.PivotFields("equipmentID").Orientation = xlColumnField
         pt.PivotFields("sampleDate").Orientation = xlRowField
 
 
         Dim shp As Shape
         Set shp = oSheet.Shapes.AddChart(xlColumnClustered)
         shp.Chart.SetSourceData Source:=oSheet.PivotTables(1).TableRange2, _
                               PlotBy:=xlColumns
 
         With shp.Chart.Axes(xlCategory)
            .TickLabels.Orientation = 80
         End With
 
        Dim rng As String
        rng = (Replace(("A" & Str(rs.RecordCount + 5) & ":V" & Str(rs.RecordCount + 32)), " ", ""))
 
        oSheet.Select
        oSheet.Activate
        oSheet.Range(rng).Select
         With Selection
             shp.Left = .Left
             shp.Top = .Top
             shp.Width = .Width
             shp.Height = .Height
         End With
 
 
 
         With shp.Chart.PivotLayout.PivotTable
             .PivotFields("equipmentID").Orientation = xlColumnField
             .PivotFields("sampleDate").Orientation = xlRowField
             .GetPivotData
         End With
         shp.Chart.ChartType = xlColumnClustered
         shp.Chart.Refresh
         oApp.Visible = True
         shp.Chart.ChartType = xlColumnClustered
         shp.Chart.Refresh
 
'Display
'On Error GoTo closeerror:
oApp.Visible = True
oApp.UserControl = True
 
'close out everything
 
rs.Close
db.Close
 
 
Exit Sub
unexError:
MsgBox Str$(Err.Number)
 
On Error GoTo closeerror:
'close out everything
 
rs.Close
db.Close
closeerror:
Exit Sub
End Sub