Object variable or with variable not set for copying

Question

I would like to be able to copy my data to the workbook.

I have built the code from here:

Copying a range from all files within a folder and pasting into master workbook

which currently looks like this:

  Sub CopyData()
  Dim wbSource As Workbook
  Dim datTarget As Worksheet
  Dim datSource As Worksheet
  Dim strFilePath, strfile As String
  Dim strPath As String

  Set datTarget = ThisWorkbook.Sheets("Survey")
  strPath = GetPath

  If Not strPath = vbNullString Then

   strfile = Dir$(strPath & "*.xlsx", vbNormal)

   Do While Not strfile = vbNullString

  Set wbSource = Workbooks.Open(strPath & strfile)
  Set datSource = wbSource.Sheets("Sheet1")

 Call Copy_Data(datSource, datTarget)

 wbSource.Close False
 strfile = Dir$()
  Loop
  End If
  End Sub

   Sub Copy_Data(ByRef datSource As Worksheet, datTarget As Worksheet)

  'QUESTION 1


  Const TM_PM As String = "*PM is required*"

  Dim que1 As Range
  Dim ans1 As Range
  Set que1 = Sheets("Sheet1").Range("A1:A100").Find(What:=TM_PM, _
                                     Lookat:=xlPart, LookIn:=xlValues)
                                     
  If Not que1 Is Nothing Then
  'MsgBox ("The question about PM or TM wasn't found")
  End If

  que1.Copy
  datTarget.Range("E1").PasteSpecial xlPasteValuesAndNumberFormats
  End With

  Dim lrow1 As Long

  lrow1 = datTarget.Range("E" & datTarget.Rows.Count).End(xlUp).Row + 1


   End Sub

I don't know why am I receiving errors:

Object variable or with variable not set

at the line:

     que1.Copy

I tried also something like this:

  With que1
  .Copy
  datTarget.Range("E1").PasteSpecial xlPasteValuesAndNumberFormats
  End With

but the error was the same.

Excel VBA Error: Object variable or With block variable not set

I know that the question is common, but I need to know what is wrong in this issue?

UPDATE:

In the "Locals" window I see, that variable is nothing. My situation is derivative from this query:

Defining just part of the string as a constant

Have I done something wrong in the meantime?

That means nothing was found with `Set que1 = Sheets("Sheet1").Range("A1:A100").Find(What:=TM_PM, Lookat:=xlPart, LookIn:=xlValues)`. Use `If que1 Is Nothing Then MsgBox ("The question about PM or TM wasn't found") : Exit Sub : End If` — CDP1802, Dec 21 '21 at 12:40
`que1.Copy` and the line that paste should be inside between `If Not que1 Is Nothing Then` and `End If`. This way it will only perform copy and paste if the `Find` method has a match i.e. `Not que1 is Nothing` — Raymond Wu, Dec 21 '21 at 12:52
If Not que1 Is Nothing Then MsgBox ("The question about PM or TM wasn't found") Exit Sub End If Just this but the Message box there was quoted I don't need it frankly. — Geographos, Dec 21 '21 at 12:52
There doesn't seem much point in repeating the same question if you're not going to take on board the answers you get. — SJR, Dec 21 '21 at 12:52
I am getting them and trying back and forth for nearly 2 hours mate — Geographos, Dec 21 '21 at 12:53
You didn't need to post this question, you could have continued with your previous one where Rory explained your problem. — SJR, Dec 21 '21 at 12:59
You're getting an error because the text isn't found and that is most like because you're not searching in whatever is assigned to `datSource` but in "Sheet1" of the wordbook with code. — BrakNicku, Dec 21 '21 at 13:32

score 1 · Accepted Answer · answered Dec 21 '21 at 12:56

Always best to test the result of a Find.

Sub Copy_Data(ByRef datSource As Worksheet, datTarget As Worksheet)
    
    'QUESTION 1
    Const TM_PM As String = "*PM is required*"
    
    Dim que1 As Range
    Set que1 = Sheets("Sheet1").Range("A1:A100").Find(What:=TM_PM, _
                            Lookat:=xlPart, LookIn:=xlValues)
                                         
    If que1 Is Nothing Then
        MsgBox "The question about PM or TM wasn't found", vbExclamation
    Else
        que1.Copy
        datTarget.Range("E1").PasteSpecial xlPasteValuesAndNumberFormats
    End If

End Sub

Object variable or with variable not set for copying

1 Answers1

Linked