I am trying to copy cells that contain a certain criteria to a new worksheet.

For example, in my worksheet “LOCATIONS”, a CONCATENATE formula cell contains a string that would be used in a SQL script to be able to bulk create them.

Reference Table

I’m needing to take the blue pieces of code in cells C8, E8, G8… ( which all contain “dbo.Location”, which I’m using in my InStr() ) and paste them in a column as values on worksheet “SQL” but this will then expand to be quite a lot of locations and we dont want to manually copying them and rearranging them.

I found the following VBA code:

Sub Test()
Dim Cell As Range

With Sheets("LOCATIONS") 'Sheet with data to check for value
    ' loop column A untill last cell with value (not entire column)
    For Each Cell In .Range("A1:R13" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        pos = InStr(Cell.Value, "Location")
        If pos > 0 Then
            NextFreeRow = Sheets("SQL").Cells(Sheets("SQL").Rows.Count, "A").End(xlUp).Row + 1
             'get the next empty row to paste data to
            .Range("I" & Cell.Row).Copy Destination:=Sheets("SQL").Range("A" & NextFreeRow)
        End If
    Next Cell
End With
End Sub

which creates this in worksheet “SQL”

For example the first one should pull through as "INSERT INTO dbo.Locations VALUES ('L-1', 'ROLLER TRACK 1', 'Production', 'd6cf5587-46da-4484-9795-beb0a192cf18', 1, 1, SYSDATETIME(), NULL)"