In the following program, what I am trying to do is scan the "yes" column of a specific range of sheets in a workbook that a user fills out, and wherever the user puts an "x" within that specific "Yes" column range, it will identify the associated item of the question marked in that row and copy the item code associated with that question (e.g. C3) into a Summary page for logging purposes.
The problem is that the code does not copy the item onto the summary page as intended when the for loop iterates through the desired range of sheets. However, if I comment out the for loop code and write Sheets(6).Select instead of Sheets(i).Select, for example, it will copy the "x" marked items onto the summary page for sheet index #6 as intended. This leads me to believe my copy+paste part of the code works (between the while loop statements), but the for loop fails somehow.
Can somebody please help me identify the source of the error? I understand that this code is not efficient, such as the excessive use of .select and non-dynamic declarations, but if I wanted to keep as much of the code the same as possible, how could I modify it to make it loop through all the sheets as I intended?
Thanks
Sub DSR_Autofill()
' Variable Declarations:
Dim x_count As Long 'keeps track of how many "x"s you have
Dim i As Long 'for loop index
Dim n As Long 'while loop index
Dim item_a As String 'Letter part of Item
Dim item_b As String 'Number part of Item
' Variable Initializations:
x_count = 0 'start x count at zero
' Clear Previous Data:
Sheets(2).Range("A25:A29").ClearContents 'Clear Summary Pages before scanning through
Sheets(3).Range("A18:A200").ClearContents
' Main Data Transfer Code:
For i = 5 To i = 20 'Starts at "Process Control" and ends on "Product Stewardship"
Sheets(i).Select 'Select current indexed worksheet and...
Range("D15").Select '...the first item cell in the "Yes" Column
n = 0 'initialize n to start at top item row every time
Do While ActiveCell.Offset(n, -3) <> Empty 'Scan down "YES" column until Item Column (just "A" Column)...
'...has no characters in it (this includes space (" "))
If (ActiveCell.Offset(n, 0) = "x" _
Or ActiveCell.Offset(n, 0) = "X") Then 'If an "x" or "X" is marked in the "YES" column at descending...
'...cells down the column, at an offset specified by the for loop index n
item_a = ActiveCell.Offset(n, -3).Value ' Store Letter value
item_a = Replace(item_a, "(", "") ' Get rid of "(", ")", and " " (space)
item_a = Replace(item_a, ")", "") ' characters that are grabbed
item_a = Replace(item_a, " ", "")
item_b = ActiveCell.Offset(n, -2).Value ' Store number value
item_b = Replace(item_b, "(", "") ' Get rid of "(", ")", and " " (space)
item_b = Replace(item_b, ")", "") ' characters that are grabbed
item_b = Replace(item_b, " ", "")
x_count = x_count + 1 ' increment the total x count
If (x_count > 5) Then ' If there are more than 5 "x" marks...
Sheets("SUMMARY P.2").Activate ' ...then continue to log in SUMMARY P.2 and...
Range("A18").Select ' ...choose "Item" column, first cell
ActiveCell.Offset((x_count - 6), 0).Value = (item_a & item_b)
'Insert concatenated value of item_a and item_b (for example "A" & "1" = "A1")
'at the cells under the "Item" column, indexed by x_count
Else ' If there are less than 5 "x" marks...
Sheets("SUMMARY P.1").Activate ' ...log in SUMMARY P.1 and...
Range("A25").Select ' ...choose "Item" column, first cell
ActiveCell.Offset((x_count - 1), 0).Value = (item_a & item_b)
End If
End If
n = n + 1
Sheets(i).Select 'Return back to current sheet before running again
Range("D15").Select
Loop 'syntax for continuation of while loop
Next i 'syntax for continuation of for loop
If (x_count > 5) Then 'Bring user back to the Summary Page where the last Item was logged
Sheets("SUMMARY P.2").Select
Else
Sheets("SUMMARY P.1").Select
End If
End Sub
