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:
Here's the entire sub. I feel like I'm missing something obvious. Any help is appreciated.
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:
VBA Code:
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.
VBA Code:
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