title: How do I reference cells on a sheet without activating it?
body: <p>So I'm running an audit on a set of digital files but I think the way I wrote is pretty convoluted so maybe someone can help me...</p>
  
  <p>What I can't figure out is why activesheet.cells is working fine, but when I try to reference the cells with Workbooks("Master List").worksheets("Master Shipped").cells it gives me an error. I've tried it with the full file location, I've tried it with worksheets.cells, I've tried it with sheets.cells, I've tried making the worksheet a variable, and I can't get it to work.</p>
  
  <p>It does work at the top where I'm naming the variables, but it doesn't work in my if statements which is where I want it work because I don't want to have to activate the sheet 10 times each loop.</p>
  
  <pre><code>Application.ScreenUpdating = False
  LR = Cells(Rows.Count, 2).End(xlUp).Row
  Dim AuditSheet As Worksheet, Mastersheet As Worksheet
  Set AuditSheet = ThisWorkbook.Sheets("Audit")
  Set Mastersheet = ThisWorkbook.Sheets("Master Shipped")
  
  For r = 3 To LR
     FpathBOL = "U:\Warehouse Associate\Shipped Orders 2016\Bill of Lading\"
     fnamebol = Mastersheet.Cells(r, 21).Text
     FNamePOD = Left(Mastersheet.Cells(r, 21).Text, (Len(Mastersheet.Cells(r, 21)) - 8))
     FpathFile = "V:\LVA Files\" &amp; Mastersheet.Cells(r, 4).Value &amp; "\Line " &amp; Mastersheet.Cells(r, 10).Value &amp; "\"
     FnameFile = Mastersheet.Cells(r, 4).Value &amp; "-"
     BOL = FpathBOL &amp; "\" &amp; fnamebol &amp; ".pdf"
     POD = FpathBOL &amp; FNamePOD &amp; "POD.pdf"
     File1 = FpathFile &amp; FnameFile &amp; "PO.pdf"
     File2 = FpathFile &amp; FnameFile &amp; "EIC PO.pdf"
     File3 = FpathFile &amp; FnameFile &amp; "EDI 855.pdf"
     File4 = FpathFile &amp; FnameFile &amp; "EDI 870.pdf"
     File5 = FpathFile &amp; FnameFile &amp; "VENDOR INVOICE.pdf"
     File6 = FpathFile &amp; FnameFile &amp; "EIC INVOICE.pdf"
     File7 = FpathFile &amp; FnameFile &amp; "PCGR.pdf"
     File8 = FpathFile &amp; FnameFile &amp; "PL.pdf"
     File9 = FpathFile &amp; FnameFile &amp; "EDI.pdf"
  If Dir(File1) = "" Then
      Workbooks("Master List.xlsm").Worksheets("Audit").Activate
      ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
      ActiveCell.Value = File1
  End If
  If Dir(File2) = "" Then
      Worksheets("Audit").Activate
      ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
      ActiveCell.Value = File2
  End If
  
  If Dir(File3) = "" And Dir(File9) = "" Then
      Worksheets("Audit").Activate
      ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
      ActiveCell.Value = File3
  End If
  If Dir(File4) = "" And Dir(File9) = "" Then
      Worksheets("Audit").Activate
      ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
      ActiveCell.Value = File4
  End If
  If Dir(File5) = "" Then
      Worksheets("Audit").Activate
      ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
      ActiveCell.Value = File5
  End If
  If Dir(File6) = "" Then
      Worksheets("Audit").Activate
      ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
      ActiveCell.Value = File6
  End If
  If Dir(File7) = "" Then
      Worksheets("Audit").Activate
      ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
      ActiveCell.Value = File7
  End If
  If Dir(File8) = "" Then
      Worksheets("Audit").Activate
      ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
      ActiveCell.Value = File8
  End If
  If Dir(BOL) = "" Then
      Worksheets("Audit").Activate
      ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
      ActiveCell.Value = FnameFile &amp; BOL
  End If
  If Dir(POD) = "" Then
      Worksheets("Audit").Activate
      ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
      ActiveCell.Value = FnameFile &amp; POD
  End If
  Next r
  Application.ScreenUpdating = True
  End Sub
  </code></pre>