Hey guys & gals.. I am getting a runtime error 424: Object required on the line "For Each ws In memberLists.Worksheet"
The entire code is below. How can I fix this?
Dim memberLists As Workbook
Dim chapterColumn As String
Sub ParseInputToWorksheets()
Set memberLists = ActiveWorkbook
chapterColumn = "E"
Dim newMembers As Workbook
Dim newMembersFilePath As String
newMembersFilePath = "C:\...\NewMembers.xls"
Set newMembers = GetObject(newMembersFilePath)
Dim r As Range
For Each r In newMembers.Worksheets(1).Rows
Dim chapterName As String
chapterName = r.Cells(1, chapterColumn)
Call CopyToChapter(chapterName, r)
Next
End Sub
Private Sub CopyToChapter(chapterName As String, r As Range)
Dim ws As Worksheet
For Each ws In memberLists.Worksheets
If ws.Name = chapterName Then
Dim iRow As Long
iRow = FirstEmptyRowIndex(ws)
r.Copy (ws.Rows(iRow))
Exit Sub
End If
Next
End Sub
Private Function FirstEmptyRowIndex(ws As Worksheet) As Long
Dim firstCell As Range
Set firstCell = ws.Range("A1")
Dim iRow As Long
iRow = 0
Do
iRow = iRow + 1
If firstCell.Offset(iRow, 0).Value = "" Then Exit Do
Loop
FirstEmptyRowIndex = iRow + 1
End Function
Bookmarks