How do I reference cells on a sheet without activating it?

Question

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...

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.

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.

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\" & Mastersheet.Cells(r, 4).Value & "\Line " & Mastersheet.Cells(r, 10).Value & "\"
   FnameFile = Mastersheet.Cells(r, 4).Value & "-"
   BOL = FpathBOL & "\" & fnamebol & ".pdf"
   POD = FpathBOL & FNamePOD & "POD.pdf"
   File1 = FpathFile & FnameFile & "PO.pdf"
   File2 = FpathFile & FnameFile & "EIC PO.pdf"
   File3 = FpathFile & FnameFile & "EDI 855.pdf"
   File4 = FpathFile & FnameFile & "EDI 870.pdf"
   File5 = FpathFile & FnameFile & "VENDOR INVOICE.pdf"
   File6 = FpathFile & FnameFile & "EIC INVOICE.pdf"
   File7 = FpathFile & FnameFile & "PCGR.pdf"
   File8 = FpathFile & FnameFile & "PL.pdf"
   File9 = FpathFile & FnameFile & "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 & BOL
End If
If Dir(POD) = "" Then
    Worksheets("Audit").Activate
    ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
    ActiveCell.Value = FnameFile & POD
End If
Next r
Application.ScreenUpdating = True
End Sub

Read, learn, and love: [How to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). :D — BruceWayne, Sep 21 '16 at 21:30
The syntax is `Workbooks("Master List.xlsm").Sheets("Audit").Cells(1,1)` to modify Cell `A1`. You can also use `Workbooks("Master List.xlsm").Sheets("Audit").Range("A1")` — user1274820, Sep 21 '16 at 21:32

ThunderFrame · Accepted Answer · 2016-09-21T21:45:36.813

To take your existing code snippet:

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

As a rule of thumb, you can remove the trailing .Activate and the leading ActiveSheet/ActiveCell statements, and merge the lines:

If Dir(File1) = "" Then
    Workbooks("Master List.xlsm").Worksheets("Audit").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = File1
End If

But, it's only a rule of thumb. Notice for example, the use of Rows.Count. That is relative to the active sheet, so it won't work without further editing.

If Dir(File1) = "" Then
    With Workbooks("Master List.xlsm").Worksheets("Audit")
      .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = File1
    End With
End If

ugh that was so simple I hate myself for not getting it lol. Thank you very much! — Victoria, Sep 21 '16 at 21:45

How do I reference cells on a sheet without activating it?

1 Answers1