Macro to copy values from multiple sheets in a workbook to a new worksheet in the same workbook

[jmk15315]

Rookie here and hoping someone can help me out.
I have a worksheet in which I want to compile information from multiple sheets into a single sheet and organize them by category. What I have done is create the following code, but it does not work.

Sub CopyValues()
Dim wsRobot As Worksheet
Dim wsPlatform As Worksheet
Dim wsControls As Worksheet
Dim wsJIF_DETAILS As Worksheet
Dim lastRow As Long
Dim i As Long
Dim j As Long

Set wsRobot = ThisWorkbook.Worksheets("Robot")
Set wsPlatform = ThisWorkbook.Worksheets("Platform")
Set wsControls = ThisWorkbook.Worksheets("Controls")
Set wsJIF_DETAILS = ThisWorkbook.Worksheets("JIF_DETAILS")

' Find the last row in column A of each worksheet
lastRow = wsRobot.Cells(wsRobot.Rows.Count, "A").End(xlUp).Row

' Loop through each row in column J of each worksheet
For i = 1 To lastRow
If wsRobot.Cells(i, "J").Value = "FG0100" and wsRobot.cells(i, "B").value = "1" Then
' Find the next available row in column A of wsJIF_DETAILS
j = wsJIF_DETAILS.Cells(wsJIF_DETAILS.Rows.Count, "A").End(xlUp).Row + 1

' Copy the value from column A of wsRobot to wsJIF_DETAILS
wsRobot.Cells(i, "A").Copy wsJIF_DETAILS.Cells(j, "A") 'CODE FAILS AT THIS POINT
End If
Next i

End Sub

 

[MARK858]

wsRobot.Cells(i, "A").Copy wsJIF_DETAILS.Cells(j, "A") 'CODE FAILS AT THIS POINT

In what way does that line fail?

I'd also remove the quotes around the "1"

If wsRobot.Cells(i, "J").Value = "FG0100" and wsRobot.cells(i, "B").value = "1" Then

 

[jmk15315]

Helps if I pay attention to the error. I had a locked worksheet, so between removing the "" from around the 1 and adding an unprotect worksheet line to the code, it resolved the issue. Now all I need to do is figure out how to do this for multiple worksheets and multiple variables "FG0100", "FG0200", etc... without having a million+ lines of code.

 

[MARK858]

Now all I need to do is figure out how to do this for multiple worksheets

Something like

    'Set wsRobot = ThisWorkbook.Worksheets("Robot")

    For Each wsRobot In ActiveWorkbook.Worksheets
        If wsRobot.Name <> wsJIF_DETAILS.Name Then

            For i = 1 To lastRow

                If wsRobot.Cells(i, "J").Value = "FG0100" And wsRobot.Cells(i, "B").Value = "1" Then

                    ' Find the next available row in column A of wsJIF_DETAILS

                    j = wsJIF_DETAILS.Cells(wsJIF_DETAILS.Rows.Count, "A").End(xlUp).Row + 1



                    ' Copy the value from column A of wsRobot to wsJIF_DETAILS

                    wsRobot.Cells(i, "A").Copy wsJIF_DETAILS.Cells(j, "A") 'CODE FAILS AT THIS POINT

                End If

            Next i
        End If

    Next wsRobot

and look into using an array for the multiple variables (I think you mean strings rather than variables btw)