title: Getting error while running macro on different worksheet but on same workbook
body: <p>I am getting the following error runtime </p>
  
  <blockquote>
    <p>error'1004', select method of range class failed</p>
  </blockquote>
  
  <p>When trying to run the code below on different worksheet but on same workbook.</p>
  
  <pre><code>Sub AdministrationReport()
  
  Dim report               As Workbook
  Dim admiistration        As Workbook
  Dim wbPath               As String
  
  'report is the active workbook where data is being pasted
  Set report = ActiveWorkbook ' (till here it opens new workbook - I have deleted that code to shorten the question)
  
  ' set administration to Open workbook from where data will be copied
  Set administration = Workbooks.Open(wbPath)
  administration.Worksheets("Administration_POWHS Standardis").Range("A2:j100").Copy Destination:=report.Worksheets("administration").Range("A2:j100")
  ActiveWorkbook.Close
  
  'this is to format report data    
  Set report = ActiveWorkbook
  
  **report.Worksheets("administration").Range("a2:d100").Select**- stuck here
  
  With Selection
      .HorizontalAlignment = xlLeft
      .VerticalAlignment = xlCenter
      .WrapText = True
      .Orientation = 90
      .ReadingOrder = xlContext
  End With
  </code></pre>