Unexplainable Application-defined or object-defined error

[Foo_Man_Chu]

I've written a small Sub that is really frustrating me. Sometimes it works just fine, other times I get the "Application-defined or object-defined error". I don't have any further information to give, sorry. I've thought about this one for quite a few hours now. For the sake of my sanity, please help me out......
Here's my code:

Option Explicit


Public Sub Worksheet_Change(ByVal Target As Excel.Range)


     If Not Intersect(Target, Target.Worksheet.Range("C3")) Is Nothing Then
        
        Dim WS As Worksheet
        Dim LastCell As Range
        Dim LastCellRowNumber As Long
        Dim pc As PivotCache
        Dim pt As PivotTable
        Dim lastRow As String
        Range("C3").Activate
        If ActiveCell.Value <> vbNullString Then
            Set WS = Worksheets("855_Template")
            With WS
                Set LastCell = .Cells(.Rows.Count, "C").End(xlUp)
                LastCellRowNumber = LastCell.Row
            End With
            LastCell.Activate
            lastRow = LastCell.Row
            Dim quick As String
            quick = "B2:H" & lastRow
            Range(ActiveCell.Offset(0, -2), "A3").Select
            Selection.Copy
            Range("B3").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                
            Dim blah As Worksheet
            Set blah = ThisWorkbook.Worksheets("855_Summary")
                ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                SourceData:="855_Template!" & quick, _
                Version:=xlPivotTableVersion14).CreatePivotTable _
                TableDestination:=blah.Range("A1:D1"), TableName:="PTCtable69"
        End If
    End If
End Sub

 

[Jeffrey Mahoney]

When I ran it, it gave me an error on the line:
LastCell.Activate

If you want to select a cell on another sheet, you need to activate that sheet first. You don't really need to select a cell or activate a cell to copy or paste stuff. It's faster if you don't. Copy and paste is slow. Try using Range.value instead. The only requirement is that the destination range must be the same size as the original range. You have to define the range size.

I couldn't fully test the code below.

Jeff

Public Sub Worksheet_Change(ByVal Target As Excel.Range)  
  Dim WS As Worksheet
  Dim LastCell As Range
  Dim LastCellRowNumber As Long
  Dim pc As PivotCache
  Dim pt As PivotTable
  Dim lastRow As String
  Dim R As Range
  Dim quick As String
  Dim blah As Worksheet




  If Not Intersect(Target, Range("C3")) Is Nothing Then
    'Range("C3").Activate
    If Target.Value <> vbNullString Then
        Set WS = Worksheets("855_Template")
        With WS
            Set LastCell = .Cells(.Rows.Count, "C").End(xlUp)
            LastCellRowNumber = LastCell.Row
        End With
        'LastCell.Activate
        Set R = Range(LastCell.Offset(0, -2), WS.Range("A3"))
        Range(Range("B3"), Range("B3").Offset(R.Rows.Count - 1, R.Columns.Count - 1)).Value = R.Value
        
        'Range(ActiveCell.Offset(0, -2), "A3").Select
        'Selection.Copy
        'Range("B3").Select
        'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
        lastRow = LastCell.Row
        quick = "B2:H" & lastRow
        
        Set blah = ThisWorkbook.Worksheets("855_Summary")
            ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:="855_Template!" & quick, _
            Version:=xlPivotTableVersion14).CreatePivotTable _
            TableDestination:=blah.Range("A1:D1"), TableName:="PTCtable69"
    End If
  End If
  
End Sub

 

[Foo_Man_Chu]

Jeffrey,
Thank you so much, more than words can say. I loved how you kept my code commented out and showed the better way to accomplish the desired result. Thanks for taking the time to help someone out that has a little less knowledge than yourself. Thank you!

 

[Jeffrey Mahoney]

You are absolutely welcome.

-- You can wake up as a different person -- I redefined myself twice. Don't blindly accept the path laid out in front of you! ----