Run-time error 5 when using a "For" statement

[gusbus]

Hi all,

I am creating a macro that will record the 32 digit ID of each file in a specified folder. When I try to record this into the current excel book I get a run-time error.

Appreciate the help.

Sub MoveKs()

Dim folderName As String
Dim FSOLibrary As Object
Dim FSOFolder As Object
Dim FSOFile As Object
'Workbook declarations
Dim wbBook1 As Workbook
Dim wsSheet1 As Worksheet
Dim wsSheet2 As Worksheet
'Subscription Attributes
Dim subID As String
Dim dtToday As Date


'Set the file name to a variable
folderName = "Filepathhere"




'   Set all the references to the FSO Library
    Set FSOLibrary = CreateObject("Scripting.FileSystemObject")
    Set FSOFolder = FSOLibrary.GetFolder(folderName)
    Set FSOFile = FSOFolder.Files
'   Designate worksheets for file references and location
    Set wbBook1 = ThisWorkbook
    Set wsSheet1 = wbBook1.Worksheets("Macro")
    Set wsSheet2 = wbBook1.Worksheets("Records")
'   Set when the action is occuring
    dtToday = Now()



'Use For Each loop to loop through each file in the folder
For Each FSOFile In FSOFile
    subID = Left(FSOLibrary.GetFileName(FSOFile), 32)
    wsSheet2.Cells("A" & Rows.Count).End(xlUp).Offset(1).Value = subID
    wsSheet2.Cells("B" & Rows.Count).End(xlUp).Offset(1).Value = dtToday
Next


MsgBox Done!



End Sub

 

[Micron]

You're going to make us look up what error 5 is out of thousands of error messages?
Try
wsSheet2.Range("A" & Rows.count).End(xlUp).Offset(1).Value = subID
wsSheet2.Range("B" & Rows.count).End(xlUp).Offset(1).Value = dtToday

 

[Micron]

Forgot to mention that your message box line will raise a run time or compile error because of no quotes around your message string.

 

[gusbus]

You're going to make us look up what error 5 is out of thousands of error messages?
Try
wsSheet2.Range("A" & Rows.count).End(xlUp).Offset(1).Value = subID
wsSheet2.Range("B" & Rows.count).End(xlUp).Offset(1).Value = dtToday

This worked - thank you!

 

[Micron]

You're welcome!