Hi again... Having another problem with the same macro that has been giving me trouble for a week now. Now, the macro will run, but after it finishes, excel stops responding. Is this because the macro continues to run indefinitely? Any ideas?
Sub ParseInputToWorksheets()
Dim memberLists As Workbook
Dim chapterColumn As String
Set memberLists = ActiveWorkbook
chapterColumn = "B"
Dim newMembers As Workbook
Dim newMembersFilePath As String
newMembersFilePath = "C:\Creeds\Database\SAE\NewList.xlsx"
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 memberLists As Workbook
Dim ws As Worksheet
Set memberLists = ActiveWorkbook
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