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.

 

[BigShanny]

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.

I've never done this in the past and didn't really understand it so I did a deep dive on what to look for. This identified that "FilesLastRow" had a value of 221. I realized that I had some stray values at the bottom of the spreadsheet from prior usage. The code was working correctly the entire time. I feel a little silly but I wanted to reply back to let you know that the Watch window was what helped me find the problem. Thank you. After eliminating the stray values and switching it back to "NextRow" rather than LastRow, my problem is solved.

 

[BigShanny]

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.

Hi Alex, see my other post for the solution. While fumbling around, I had actually changed it to a last row rather than a "next Empty row" type of input. This corrected the problem. Maybe I got a little crazy on the unprotects. Let me see if I can just do it once. Maybe it'll save me time. That "Queue" worksheet has a change routine on it that reapplies protection after every change. I'll try it and see. Thanks for your help.

 

[BigShanny]

Hi Alex, see my other post for the solution. While fumbling around, I had actually changed it to a last row rather than a "next Empty row" type of input. This corrected the problem. Maybe I got a little crazy on the unprotects. Let me see if I can just do it once. Maybe it'll save me time. That "Queue" worksheet has a change routine on it that reapplies protection after every change. I'll try it and see. Thanks for your help.

Just an update: I got the password protection error when removing the unprotect. I remember now that I've intentionally unlocked columns A-E in Queue because they do require further User Input. Anything after that I would like to have protection on in order for formulas to not be written over, etc. So this makes sense. Thanks again for pointing it out though. I appreciate your help.

 

[Micron]

I've never done this in the past and didn't really understand it so I did a deep dive on what to look for.

I really admire that. Drives me crazy when I post something and the OP comes back with (basically) "what's that?". Look it up fer cryin' out loud.
I usually don't mention the watch or locals window for that reason. Even with the watch window you'd want to step through anyway. Thanks for the recognition.

 

[Alex Blakenburg]

Just an update: I got the password protection error when removing the unprotect. I remember now that I've intentionally unlocked columns A-E in Queue because they do require further User Input. Anything after that I would like to have protection on in order for formulas to not be written over, etc. So this makes sense. Thanks again for pointing it out though. I appreciate your help.

I am not sure in what order the code was created, but if you have Worksheet Events running then any lines of code between the False & True below should not run those events which presumably were turning the Protection back on.

Application.EnableEvents = False
...
Application.EnableEvents = True

So assuming you added this after you added the multiple unprotects it should be safe to reduce them to just the first one now.
PS: I am surprised that your first 5 write lines are before you are setting it to False though. Unless you need it to trigger the event 5 times I suggest you move the False line up before that.