Please help: Method 'Default' of object 'Range" failed

R

roninblackman

New Member
Joined
Sep 5, 2014
Messages
3
Hello,

I am a first time poster and I was hoping someone could provide me with a little help. I'm running into an error when I run the macro I've created. Before I post the code, I would like to explain what it is I am attempting. I've got a console that pulls in various extracts from other sources and said data into a few tabs on my worksheet. It then generates a pivot table that puts the data into an organized view. The data itself consists of Project Managers, Resources, Project Names, etc. The macro works like a charm and I'm not having any issue currently with that portion. The pivot table separates the data into projects and then it lists the resources attached to that project. What I'm trying to add is a section of code that filters through the data and finds the project manager associated to that project and then highlights that resource in yellow for a quick visual indicator.


Sounds easy, right? Well.. I'm stuck and every time I try to run the code it locks up excel and for the life of me I cannot figure it out. I've attached the section of code that I've worked on to highlight the Project Manager. Could someone please provide me some assistance in troubleshooting this? I'll be more than happy to give any further information if that would help. Any help regarding this issue would be greatly appreciated. Thank you!

Code: 
'   To assign color to project manager in the pivot by project tab

    Dim DataPMname As String
    Dim isFound As Boolean
    Dim PivotProjectName As String
    Dim Count As Long
    Dim DataSheetLastRow As Long
    
    isFound = False
    PivotProjectName = ""
    Count = 1
    
    LastDataRow = Sheet9.UsedRange.Rows(Sheet9.UsedRange.Rows.Count).Row
    DataSheetLastRow = Sheet8.UsedRange.Rows(Sheet8.UsedRange.Rows.Count).Row
    
    For r = 1 To LastDataRow
        PivotProjectName = Sheet9.Cells(r, "A").Value
        While isFound = False And PivotProjectName <> ""
        
            For x = 1 To DataSheetLastRow
                If x = DataSheetLastRow Then
                    isFound = True
                End If
                If PivotProjectName = Sheet8.Cells(Count, "C").Value Then
                    DataPMname = UCase(Sheet8.Cells(Count, "D").Value)
                    isFound = True
                End If
            Next
            isFound = False
        Wend
    
        If Sheet9.Cells(r, "B").Value = DataPMname Then
           Sheet9.Cells(r, "B").Interior.Color = RGB(255, 255, 0)
        Else
           Sheet9.Cells(r, "B").Interrior.Color = RGB(255, 255, 255)
        End If
       
    Next
    
    Application.ScreenUpdating = True
        

    
End Function

This portion is highlighted when I debug..
If PivotProjectName = Sheet8.Cells(Count, "C").Value Then
 
Last edited by a moderator:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Am No VBA expert but surely you can't use Sheet8 / Sheet9 like that? For example suppose your sheet was called Sheet 8,
this surely wouldn't be correct

Code: 
DataSheetLastRow = Sheet 8.UsedRange.Rows(Sheet8.UsedRange.Rows.Count).Row
Note the space between Sheet and 8

Try Sheets("Sheet8"). and Sheets("Sheet9").
 
Upvote 0
You can use the codename of the sheet as a variable. (codenames can't contain spaces).
 
Upvote 0
Is this the real code? I ask because this line:
Rich (BB code): 
Sheet9.Cells(r, "B").Interrior.Color = RGB(255, 255, 255)
shouldn't compile.
 
Upvote 0
D'oh! Well I corrected that spelling error. As for the sheet names, I'm not seeing a space anywhere in my coding. The program still locks up when I try to run it. Thank you all for the quick replies!
 
Upvote 0
I'm not sure you posted enough of the code. How/where is Count incremented and what is its value when the error occurs?
 
Upvote 0
Posted below is the entire module (from the macro) that puts all the information into the pivot tables. The section that I am only having difficulty is the very last section of the code. Or at least that is what I am thinking it is. The rest of the code works like a charm.. The section I am working on starts at ' To assign color to project manager in the pivot by project tab. If you think it would help if I post a link with the document itself I will, I'll just have to go in and modify the names for privacy and confidentiality. Thank you for your continued support, I appreciate it.
Code: 
Public Function REFRESH_PIVOT_TABLES()

    Application.ScreenUpdating = False
    
    Dim LastDataRow As Long
    
    LastDataRow = Sheet8.UsedRange.Rows(Sheet8.UsedRange.Rows.Count).Row
    
    If LastDataRow = 1 Then
        MsgBox "Data tab empty. Pivot Tables were NOT successfully refreshed!"
        frmConsole.lblRefreshPivotTablesStatus = "FAIL"
        frmConsole.lblRefreshPivotTablesStatus.ForeColor = RGB(255, 0, 0)
        frmConsole.Repaint
        Exit Function
    End If
    
    Dim MyPivot As PivotTable
    Dim rngSource As Range
    Dim pc As PivotCache
    
    'Get a reference to the source data table
    With ThisWorkbook.Worksheets("Data")
        Set rngSource = .Range(.Cells(1, 1), .Cells(LastDataRow, 20))
    End With
    
    'Get a reference to the Pivot Table
    Set MyPivot = Sheet9.PivotTables("PivotByProject")
    MyPivot.SourceData = "Data!" & rngSource.Address(ReferenceStyle:=xlR1C1)
    
    Set MyPivot = Sheet10.PivotTables("PivotIntResource")
    MyPivot.SourceData = "Data!" & rngSource.Address(ReferenceStyle:=xlR1C1)
    
    Set MyPivot = Sheet11.PivotTables("PivotExtResource")
    MyPivot.SourceData = "Data!" & rngSource.Address(ReferenceStyle:=xlR1C1)
    
    
    Dim pt As PivotTable
    Dim ws As Worksheet
    
    frmConsole.txtStatusLog.Value = frmConsole.txtStatusLog.Value & Chr(13) & "Refreshing Pivot Table Cache..."
    frmConsole.Repaint
    
    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
            pt.RefreshTable
        Next pt
    Next ws
    
    'refresh all the pivot caches
    For Each pc In ActiveWorkbook.PivotCaches
      'On Error Resume Next
      pc.Refresh
    Next pc
    
    frmConsole.txtStatusLog.Value = frmConsole.txtStatusLog.Value &  Chr(13) & "Assigning Avail Man Days to each pivot table..."
    frmConsole.Repaint
    
    'Assign Months to Pivot Tables
    Sheet9.Cells(7, "C").Value = "Sum of " & Sheet8.Cells(1, "G").Value
    Sheet9.Cells(7, "D").Value = "Sum of " & Sheet8.Cells(1, "H").Value
    Sheet9.Cells(7, "E").Value = "Sum of " & Sheet8.Cells(1, "I").Value
    Sheet9.Cells(7, "F").Value = "Sum of " & Sheet8.Cells(1, "J").Value
    Sheet9.Cells(7, "G").Value = "Sum of " & Sheet8.Cells(1, "K").Value
    Sheet9.Cells(7, "H").Value = "Sum of " & Sheet8.Cells(1, "L").Value
    Sheet9.Cells(7, "I").Value = "Sum of " & Sheet8.Cells(1, "M").Value
    Sheet9.Cells(7, "J").Value = "Sum of " & Sheet8.Cells(1, "N").Value
    Sheet9.Cells(7, "K").Value = "Sum of " & Sheet8.Cells(1, "O").Value
    Sheet9.Cells(7, "L").Value = "Sum of " & Sheet8.Cells(1, "P").Value
    Sheet9.Cells(7, "M").Value = "Sum of " & Sheet8.Cells(1, "Q").Value
    Sheet9.Cells(7, "N").Value = "Sum of " & Sheet8.Cells(1, "R").Value
    
    'Assign Months to Pivot Tables
    Sheet10.Cells(7, "D").Value = "Sum of " & Sheet8.Cells(1, "G").Value
    Sheet10.Cells(7, "E").Value = "Sum of " & Sheet8.Cells(1, "H").Value
    Sheet10.Cells(7, "F").Value = "Sum of " & Sheet8.Cells(1, "I").Value
    Sheet10.Cells(7, "G").Value = "Sum of " & Sheet8.Cells(1, "J").Value
    Sheet10.Cells(7, "H").Value = "Sum of " & Sheet8.Cells(1, "K").Value
    Sheet10.Cells(7, "I").Value = "Sum of " & Sheet8.Cells(1, "L").Value
    Sheet10.Cells(7, "J").Value = "Sum of " & Sheet8.Cells(1, "M").Value
    Sheet10.Cells(7, "K").Value = "Sum of " & Sheet8.Cells(1, "N").Value
    Sheet10.Cells(7, "L").Value = "Sum of " & Sheet8.Cells(1, "O").Value
    Sheet10.Cells(7, "M").Value = "Sum of " & Sheet8.Cells(1, "P").Value
    Sheet10.Cells(7, "N").Value = "Sum of " & Sheet8.Cells(1, "Q").Value
    Sheet10.Cells(7, "O").Value = "Sum of " & Sheet8.Cells(1, "R").Value
    
    'Assign Months to Pivot Tables
    Sheet11.Cells(7, "D").Value = "Sum of " & Sheet8.Cells(1, "G").Value
    Sheet11.Cells(7, "E").Value = "Sum of " & Sheet8.Cells(1, "H").Value
    Sheet11.Cells(7, "F").Value = "Sum of " & Sheet8.Cells(1, "I").Value
    Sheet11.Cells(7, "G").Value = "Sum of " & Sheet8.Cells(1, "J").Value
    Sheet11.Cells(7, "H").Value = "Sum of " & Sheet8.Cells(1, "K").Value
    Sheet11.Cells(7, "I").Value = "Sum of " & Sheet8.Cells(1, "L").Value
    Sheet11.Cells(7, "J").Value = "Sum of " & Sheet8.Cells(1, "M").Value
    Sheet11.Cells(7, "K").Value = "Sum of " & Sheet8.Cells(1, "N").Value
    Sheet11.Cells(7, "L").Value = "Sum of " & Sheet8.Cells(1, "O").Value
    Sheet11.Cells(7, "M").Value = "Sum of " & Sheet8.Cells(1, "P").Value
    Sheet11.Cells(7, "N").Value = "Sum of " & Sheet8.Cells(1, "Q").Value
    Sheet11.Cells(7, "O").Value = "Sum of " & Sheet8.Cells(1, "R").Value
    
    'Assign man day values at the top of each month in the internal/external worksheets
    Sheet10.Cells(5, "C").Value = "Avail Man Days"
    Sheet10.Cells(5, "D").Value = Sheet4.Cells(1, "G").Value / 8
    Sheet10.Cells(5, "E").Value = Sheet4.Cells(1, "H").Value / 8
    Sheet10.Cells(5, "F").Value = Sheet4.Cells(1, "I").Value / 8
    Sheet10.Cells(5, "G").Value = Sheet4.Cells(1, "J").Value / 8
    Sheet10.Cells(5, "H").Value = Sheet4.Cells(1, "K").Value / 8
    Sheet10.Cells(5, "I").Value = Sheet4.Cells(1, "L").Value / 8
    Sheet10.Cells(5, "J").Value = Sheet4.Cells(1, "M").Value / 8
    Sheet10.Cells(5, "K").Value = Sheet4.Cells(1, "N").Value / 8
    Sheet10.Cells(5, "L").Value = Sheet4.Cells(1, "O").Value / 8
    Sheet10.Cells(5, "M").Value = Sheet4.Cells(1, "P").Value / 8
    Sheet10.Cells(5, "N").Value = Sheet4.Cells(1, "Q").Value / 8
    Sheet10.Cells(5, "O").Value = Sheet4.Cells(1, "R").Value / 8
    
    Sheet11.Cells(5, "C").Value = "Avail Man Days"
    Sheet11.Cells(5, "D").Value = Sheet4.Cells(1, "G").Value / 8
    Sheet11.Cells(5, "E").Value = Sheet4.Cells(1, "H").Value / 8
    Sheet11.Cells(5, "F").Value = Sheet4.Cells(1, "I").Value / 8
    Sheet11.Cells(5, "G").Value = Sheet4.Cells(1, "J").Value / 8
    Sheet11.Cells(5, "H").Value = Sheet4.Cells(1, "K").Value / 8
    Sheet11.Cells(5, "I").Value = Sheet4.Cells(1, "L").Value / 8
    Sheet11.Cells(5, "J").Value = Sheet4.Cells(1, "M").Value / 8
    Sheet11.Cells(5, "K").Value = Sheet4.Cells(1, "N").Value / 8
    Sheet11.Cells(5, "L").Value = Sheet4.Cells(1, "O").Value / 8
    Sheet11.Cells(5, "M").Value = Sheet4.Cells(1, "P").Value / 8
    Sheet11.Cells(5, "N").Value = Sheet4.Cells(1, "Q").Value / 8
    Sheet11.Cells(5, "O").Value = Sheet4.Cells(1, "R").Value / 8
    
    
    'Refresh conditional formatting on subtotals
    
    frmConsole.txtStatusLog.Value = frmConsole.txtStatusLog.Value & Chr(13) & "Assigning values to monthly variables..."
    frmConsole.Repaint
    
    For c = 4 To 15
        If InStr(1, Sheet10.Cells(7, c).Value, "Jan") Then
            ActiveWorkbook.Names.Add Name:="JanInt", RefersTo:=Sheet10.Cells(5, c)
        ElseIf InStr(1, Sheet10.Cells(7, c).Value, "Feb") Then
            ActiveWorkbook.Names.Add Name:="FebInt", RefersTo:=Sheet10.Cells(5, c)
        ElseIf InStr(1, Sheet10.Cells(7, c).Value, "Feb") Then
            ActiveWorkbook.Names.Add Name:="FebInt", RefersTo:=Sheet10.Cells(5, c)
        ElseIf InStr(1, Sheet10.Cells(7, c).Value, "Mar") Then
            ActiveWorkbook.Names.Add Name:="MarInt", RefersTo:=Sheet10.Cells(5, c)
        ElseIf InStr(1, Sheet10.Cells(7, c).Value, "Apr") Then
            ActiveWorkbook.Names.Add Name:="AprInt", RefersTo:=Sheet10.Cells(5, c)
        ElseIf InStr(1, Sheet10.Cells(7, c).Value, "May") Then
            ActiveWorkbook.Names.Add Name:="MayInt", RefersTo:=Sheet10.Cells(5, c)
        ElseIf InStr(1, Sheet10.Cells(7, c).Value, "Jun") Then
            ActiveWorkbook.Names.Add Name:="JunInt", RefersTo:=Sheet10.Cells(5, c)
        ElseIf InStr(1, Sheet10.Cells(7, c).Value, "Jul") Then
            ActiveWorkbook.Names.Add Name:="JulInt", RefersTo:=Sheet10.Cells(5, c)
        ElseIf InStr(1, Sheet10.Cells(7, c).Value, "Aug") Then
            ActiveWorkbook.Names.Add Name:="AugInt", RefersTo:=Sheet10.Cells(5, c)
        ElseIf InStr(1, Sheet10.Cells(7, c).Value, "Sep") Then
            ActiveWorkbook.Names.Add Name:="SepInt", RefersTo:=Sheet10.Cells(5, c)
        ElseIf InStr(1, Sheet10.Cells(7, c).Value, "Oct") Then
            ActiveWorkbook.Names.Add Name:="OctInt", RefersTo:=Sheet10.Cells(5, c)
        ElseIf InStr(1, Sheet10.Cells(7, c).Value, "Nov") Then
            ActiveWorkbook.Names.Add Name:="NovInt", RefersTo:=Sheet10.Cells(5, c)
        ElseIf InStr(1, Sheet10.Cells(7, c).Value, "Dec") Then
            ActiveWorkbook.Names.Add Name:="DecInt", RefersTo:=Sheet10.Cells(5, c)
        End If
    Next
    
'    Sheet10.PivotTables("PivotIntResource").PivotSelect "", xlDataOnly, True
'    Selection.FormatConditions.Delete
'    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="0"
'    Selection.FormatConditions(1).Interior.ColorIndex = 4
    
    frmConsole.lblRefreshPivotTablesStatus = "SUCC"
    frmConsole.lblRefreshPivotTablesStatus.ForeColor = RGB(0, 255, 0)
    frmConsole.Repaint
    
    frmConsole.txtStatusLog.Value = frmConsole.txtStatusLog.Value & Chr(13) & "Refreshing Pivot Tables Complete"
    frmConsole.Repaint

    
'   To assign color to project manager in the pivot by project tab

    Dim DataPMname As String
    Dim isFound As Boolean
    Dim PivotProjectName As String
    Dim Count As Long
    Dim DataSheetLastRow  As Long
    
    isFound = False
    PivotProjectName = ""
    Count = 1
    
    LastDataRow = Sheet9.UsedRange.Rows(Sheet9.UsedRange.Rows.Count).Row
    DataSheetLastRow = Sheet8.UsedRange.Rows(Sheet8.UsedRange.Rows.Count).Row
    
    For r = 1 To LastDataRow
        PivotProjectName = Sheet9.Cells(r, "A").Value
        While isFound = False And PivotProjectName <> ""
        
            For x = 1 To DataSheetLastRow
                If x = DataSheetLastRow Then
                    isFound = True
                End If
                If PivotProjectName = Sheet8.Cells(Count, "C").Value Then
                    DataPMname = UCase(Sheet8.Cells(Count, "D").Value)
                    isFound = True
                End If
            Next
            isFound = False
        Wend
    
        If Sheet9.Cells(r, "B").Value = DataPMname Then
           Sheet9.Cells(r, "B").Interior.Color = RGB(255, 255, 0)
        Else
           Sheet9.Cells(r, "B").Interrior.Color = RGB(255, 255, 255)
        End If
       
    Next
    
    Application.ScreenUpdating = True
        

    
End Function
 
Last edited by a moderator:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
555
ahaye
A
ajjava
  • Question
Procedure to find MAX VALUE in a SPECIFIC pivot table value field, in the case of MULTIPLE pivot table value fields
Replies
0
Views
831
ajjava
ajjava
R
  • Question
Method range of object _worksheet failed 1004 vba
Replies
4
Views
708
Rfriend
R
C
  • Solved
VBA to Compare two different worksheets and copy missing rows
2
Replies
10
Views
3K
cscotty
C
S
  • Question
VBA code help
Replies
2
Views
276
stromnoexcel
S

Forum statistics

Threads
1,214,932
Messages
6,122,323
Members
449,077
Latest member
jmsotelo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top