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