CODEWITHSUNDEEP
excelvbafind
Kenny Kirkendall
Kenny Kirkendall

Reputation: 21

Find() returns "object variable or with block variable not set"

This block of code was working fine but I deleted some lines above Find() that broke it. Any ideas?

Sub CopySheet()
      Dim TotalRow As Integer
      
      Set NurselineBook = ThisWorkbook
      TotalRow = Range("$C:$C").Find(What:="Grand Total", LookIn:=xlValues, LookAt:=xlWhole).Row
      Range("A1:L" & TotalRow).Select
      Range("Ah1").Activate
      Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
      
      MsgBox "Dashboard Copied"
End Sub

Upvotes: 2

Views: 138

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

Sub CopyTable()
      
    Const wsName As String = "Sheet1"
    Const ColumnsAddress As String = "A:L"
    Const FirstRow As Long = 1
    Const CriteriaColumn As Long = 3
    Const gtString As String = "Grand Total"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    
    Dim srg As Range
    With ws.Columns(ColumnsAddress)
        Set srg = .Resize(ws.Rows.Count - FirstRow + 1).Offset(FirstRow - 1)
    End With
    
    Dim gtcell As Range: Set gtcell = srg.Columns(CriteriaColumn) _
        .Find(gtString, , xlValues, xlWhole, , xlPrevious)
    If gtcell Is Nothing Then
        MsgBox "Could not find '" & gtString & "'.", vbCritical
        Exit Sub
    End If
    
    Dim drg As Range
    Set drg = srg.Resize(gtcell.Row - FirstRow + 1)
    
    drg.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    
    MsgBox "Dashboard Copied", vbInformation

End Sub

Upvotes: 1

Related Questions