title: Excel VBA fault 1004: Method Range object_Worksheet failed
body: <p>I have a macro which should run no matter what worksheet is active. However it only runs if the worksheet "data" is active. I've been trying to figure out what is wrong but can't find a solution.
  So far I have checked all declarations. I have also tried fully declaring my range as suggested in some solutions.
  Other solutions talk about having the code inside a worksheet as being the problem? But my code is in module 1 of the workbook. As all my macros are, So I doubt that would be the problem. </p>
  
  <p>I think I am somehow not declaring the last range correctly, but I don't see what I'm doing wrong.</p>
  
  <p>The last declaration (rng_nw) is giving me trouble. </p>
  
  <pre><code>Sub test()
  Dim rapport As Worksheet
  Dim data As Worksheet
  Dim wb As Workbook
  Dim cur_rng As Range
  Dim sel_cel_2 As Range
  Dim sel_cel_1 As Range
  Dim rng_nw As Range
  
  Set wb = ThisWorkbook
  Set rapport = wb.Sheets("rapport")
  Set data = wb.Sheets("data")
  Set cur_rng = data.Range("A2").CurrentRegion
  Set sel_cel_2 = Cells(cur_rng.Rows.Count, cur_rng.Columns.Count)
  Set sel_cel_1 = data.Range("k2").End(xlDown).Offset(1, 0)
  Set rng_nw = Sheets("data").Range(sel_cel_2, sel_cel_1)
  'Set rng_nw = data.Range(sel_cel_2, sel_cel_1)
  
  End Sub
  </code></pre>
  
  <p>This macro is in module 1 of my workbook. Of course this is not the full macro, just the declarations that are giving me trouble. And they are just as troublesome in this small test macro ;).</p>
  
  <p>Thanks in advance for your help!</p>
  
  <p>The full macro is below, most of the comments are in dutch:</p>
  
  <pre><code>Sub Data_verversen()
  '
  ' Data_verversen Macro
  ' De macro kopieert alle data uit blad rapport naar blad data en haalt vervolgens de duplicaten weg obv deal nummer'
  
  '
  Dim rapport As Worksheet
  Dim data As Worksheet
  Dim wb As Workbook
  Dim plakpositie As Range
  Dim r_sel1 As Range
  Dim r_sel As Range
  Dim d_sel As Range
  Dim lastcol As Long
  Dim lastrow As Long
  Dim cur_rng As Range
  Dim sel_cel_2 As Range
  Dim sel_cel_1 As Range
  Dim rng_nw As Range
  
  Set wb = ThisWorkbook
  Set rapport = wb.Sheets("rapport")
  Set data = wb.Sheets("data")
  Set plakpositie = data.Range("A1").End(xlDown).Offset(1, 0)
      'De range r_sel wijs ik in 2 stappen toe. Dat doe ik omdat de resize functie ook de range verwijzing nodig heeft en ik anders veel uit moet schrijven.
  Set r_sel1 = rapport.Range("A2").CurrentRegion.Offset(1, 0)
  Set r_sel = r_sel1.Resize(r_sel1.Rows.Count - 1, r_sel1.Columns.Count)
  
      'to import data from Salesforce   (user has to be logged in through the salesforce.com ribbon) (connection cannot be set to background refresh)
      '--------------------------------------------------------------------------------------------
      ThisWorkbook.RefreshAll
  
      'this makes sure all calculations for the updated data are finished before the rest of the code proceeds
      Application.Calculate
  
      If Not Application.CalculationState = xlDone Then
          DoEvents
      End If
  
  
      'to stop the screen from updating for all the steps in this script
      Application.ScreenUpdating = False
  
      'Hier kopieer ik de gegevens uit range r_sel in range plakpositie. (hij plakt waarden en getalnotaties)
      r_sel.Copy
      plakpositie.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
      'Ik wijs de range om duplicaten uit te verwijderen pas toe na de plakactie. Doe je dat eerder, dan bepaal je een range zonder de nieuw geplakte data
  Set d_sel = data.Range("A1").CurrentRegion
      'vervolgens laat ik uit de toegewezen range de duplicaten verwijderen
      d_sel.RemoveDuplicates Columns:=1, Header:=xlYes
  
      data.Columns(4).NumberFormat = "m/d/yyyy"
  
  'onderstaande set verlengd kolom K met een formule om het segment te bepalen, vervolgens worden de uitkomsten weer in waarde omgezet.
      'de formule ziet er zo uit: =DEEL(B366;VIND.ALLES("_";B366;1)+1;((VIND.ALLES("_";B366;8)-1)-VIND.ALLES("_";B366;1)))
  
  Set cur_rng = data.Range("A2").CurrentRegion
  Set sel_cel_2 = Cells(cur_rng.Rows.Count, cur_rng.Columns.Count)
  Set sel_cel_1 = data.Range("k2").End(xlDown).Offset(1, 0)
  'Set rng_nw = ThisWorkbook.Sheets("data").Range(sel_cel_2, sel_cel_1)
  Set rng_nw = data.Range(sel_cel_2, sel_cel_1)
  
  
      rng_nw.FormulaR1C1 = _
          "=MID(RC[-9],FIND(""_"",RC[-9],1)+1,((FIND(""_"",RC[-9],8)-1)-FIND(""_"",RC[-9],1)))"
  
      Application.Calculate
  
      If Not Application.CalculationState = xlDone Then
          DoEvents
      End If
  
      rng_nw.Copy
      rng_nw.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
      Application.ScreenUpdating = True
  
  
  End Sub
  </code></pre>