Populate Specific Cells in first Empty Row

[BigShanny]

Hello,

I've written VBA below that loops through an imported file and looks for a specific client name and a "Yes*" value. Anything that meets this criteria is placed in a Worksheet called "Queue" for employees to call out on. All of this works perfectly (if not a little slower than I'd like). Here's where I run into trouble: After my loop to get all my data, I'd like to populate the name of the Imported file in column A and the count of the rows in the file in Column B in another worksheet called "Files." I want it to populate these values in next row. When I run the sub, everything works fine until after the loop. It's as if it's skipping over:

FilesLastRow = Files.Cells(Rows.Count, 1).End(xlUp).Row

Files.Cells(FilesLastRow, 1).Value = ImportFile.Name
Files.Cells(FilesLastRow, 2).Value = QueueLastRow - (QueueNextRow - 1)

Here's the entire sub. I feel like I'm missing something obvious. Any help is appreciated.

Sub GetClient()

Application.CutCopyMode = False

Dim FileToOpen As Variant
Dim ImportFile As Workbook
Dim ImportPath As String
Dim ImportSheet As Worksheet
Dim Outreach As Workbook
Dim Queue As Worksheet
Dim ImportLastRow As Long
Dim QueueFirstRow As Long
Dim QueueLastRow As Long
Dim ImportNextRow As Long
Dim QueueNextRow As Long
Dim Files As Worksheet
Dim FilesLastRow As Long
Dim i As Long
Dim b As Long
Dim c As Long

Set Outreach = ThisWorkbook
Set Queue = Outreach.Worksheets("Queue")
Set Files = Outreach.Worksheets("Files")

FileToOpen = Application.GetOpenFilename(Title:="Browse for your File")

Set ImportFile = Application.Workbooks.Open(FileToOpen)
Set ImportSheet = Application.InputBox(prompt:="Select any cell in the import worksheet:", Type:=8).Worksheet
        
Application.ScreenUpdating = False
        
ImportLastRow = ImportSheet.Cells(Rows.Count, 1).End(xlUp).Row

QueueNextRow = Queue.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
ImportNextRow = ImportSheet.Range("A" & Rows.Count).End(xlUp).Offset(1).Row

'Loops to last row of imported file
For i = 2 To ImportLastRow

    'In the imported file, checking to make sure column 1 is not blank
    If ImportSheet.Cells(i, 1).Value = "Client Name" And ImportSheet.Cells(i, 28) Like "Yes*" Then
        
        Queue.Activate
        
        b = Queue.Cells(Rows.Count, 1).End(xlUp).Row
        
        Queue.Cells(b + 1, 1).Value = ImportSheet.Cells(i, 5)
        Queue.Cells(b + 1, 2).Value = ImportSheet.Cells(i, 1)
        Queue.Cells(b + 1, 3).Value = ImportSheet.Cells(i, 6)
        Queue.Cells(b + 1, 4).Value = ImportFile.Name
        Queue.Cells(b + 1, 5).Value = "Appt Type"
                        
        Application.EnableEvents = False
             
        'Copies down File Age formula
        Queue.Unprotect Password:="Password"
        Queue.Cells(b, 7).Copy
        Queue.Cells(b + 1, 7).PasteSpecial xlPasteFormulas
        
        'Copies down data validation rules for type, user name, and attempt types
        
        Queue.Cells(b, 5).Copy
        Queue.Cells(b + 1, 5).PasteSpecial xlPasteValidation
        
        Queue.Cells(b, 8).Copy
        Queue.Cells(b + 1, 8).PasteSpecial xlPasteValidation
        Queue.Cells(b + 1, 12).PasteSpecial xlPasteValidation
        Queue.Cells(b + 1, 16).PasteSpecial xlPasteValidation
        
        Queue.Cells(b, 9).Copy
        Queue.Cells(b + 1, 9).PasteSpecial xlPasteValidation
        Queue.Cells(b + 1, 13).PasteSpecial xlPasteValidation
        Queue.Cells(b + 1, 17).PasteSpecial xlPasteValidation
        
        'Copies down "Completed" formula
        
        Queue.Unprotect Password:="Password"
        Queue.Cells(b, 20).Copy
        Queue.Cells(b + 1, 20).PasteSpecial xlPasteFormulas
        
        'Copies down "Completed Date" formula
        
        Queue.Unprotect Password:="Password"
        Queue.Cells(b, 21).Copy
        Queue.Cells(b + 1, 21).PasteSpecial xlPasteFormulas
        
        'Copies down "Attempts Remaining" formula
        
        Queue.Unprotect Password:="Password"
        Queue.Cells(b, 23).Copy
        Queue.Cells(b + 1, 23).PasteSpecial xlPasteFormulas
        
        Application.EnableEvents = True
        
    End If

Next

'Everything works perfectly up to here when nothing happens.
'I would like "Files" to be populated with the name of the import file in column A of the first empty row.
'I would like "Files" to be populated with the number of rows between QueueLastRow and (QueuNextRow - 1).
      'This seems to be calculating the correct number as it is appearing in the temporary message box correctly.

QueueLastRow = Queue.Cells(Rows.Count, 1).End(xlUp).Row
FilesLastRow = Files.Cells(Rows.Count, 1).End(xlUp).Row

Files.Cells(FilesLastRow, 1).Value = ImportFile.Name
Files.Cells(FilesLastRow, 2).Value = QueueLastRow - (QueueNextRow - 1)

MsgBox QueueLastRow - (QueueNextRow - 1)
                 
Queue.Protect Password:="Password"

MsgBox ("Import Complete!")
                 
ImportFile.Close
    
Application.ScreenUpdating = True
Application.CutCopyMode = True

End Sub

 

[Micron]

Did you step through the code to see exactly how it executes, and check your variable values as you go?
Without data, can't really test that and I don't see anything obvious.

 

[Alex Blakenburg]

I think it is just that it is overwriting the previous value (in the LastRow) in stead of adding a row. You can either +1 the last row (effectively making it the NextRow) or add one as per the below:

Files.Cells(FilesLastRow + 1, 1).Value = ImportFile.Name
Files.Cells(FilesLastRow + 1, 2).Value = QueueLastRow - (QueueNextRow - 1)

PS: You are unprotecting the same sheet 4 times and strangely the first unprotect appears after already having written 5 cells to the Worksheet.