Create SQL INSERT stament from Excel

excelCreateInsert.vb

Sub CreateInsert()

Dim rng As Range
Dim insertRng As Range
Dim lastColumn As Integer
Dim lastRow As Integer
Dim tabla As String
Dim insertText As String
Dim wsTest As Variant
Dim my_column As Integer
Dim OldSheet As Variant
Dim my_row As Integer
Dim lastInsertRow As Integer
Dim totalRows As Integer
Dim i As Integer

Application.ScreenUpdating = False

Set rng = Application.Selection
lastColumn = rng.Columns.Count + rng.column - 1
lastRow = rng.Rows.Count + rng.Row - 1
lastInsertRow = (lastRow - rng.Row) * 3
totalRows = rng.Rows.Count

Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets("raulo_insert")
On Error GoTo 0

Set rng = Application.Selection

Set OldSheet = ActiveSheet

If wsTest Is Nothing Then
    Worksheets.Add.Name = "raulo_insert"
End If
Sheets("raulo_insert").Cells.ClearContents
OldSheet.Activate

tabla = UCase(InputBox("Ingrese la libreria/tabla en la que quiere insertar los datos", "Tabla"))
answer = MsgBox("Terminar las sentencias con ;?", vbQuestion + vbYesNo, "Terminar sentencias con ;")

Sheets("raulo_insert").Range("A1").Value = "INSERT INTO " & tabla
Sheets("raulo_insert").Range("A2").Value = "    ("
Sheets("raulo_insert").Cells(2, rng.Columns.Count + 2).Value = ")"

my_row = 2
my_column = 2

On Error GoTo Error
For Each cell In rng
    If cell.column = lastColumn Then
        
        If cell.Row = rng.Row Then
            Sheets("raulo_insert").Cells(my_row, my_column).Value = cell.Value
        Else
            Sheets("raulo_insert").Cells(my_row, my_column).Value = "''" & cell.Value & "'"
        End If
        
        my_row = my_row + 1
        my_column = 1
        Sheets("raulo_insert").Activate
        Set insertRng = Range(Cells(1, 1), Cells(2, rng.Columns.Count + 2))
        OldSheet.Activate
    Else
        If cell.Row = rng.Row Then
            Sheets("raulo_insert").Cells(my_row, my_column).Value = cell.Value & ","
        Else
            Sheets("raulo_insert").Cells(my_row, my_column).Value = "''" & cell.Value & "',"
        End If
    End If
    If cell.Row - rng.Row <> 0 And _
        cell.column = lastColumn Then
        insertRng.Copy Sheets("raulo_insert").Cells(my_row, 1)
        my_row = my_row + 2
    End If
    my_column = my_column + 1
Next cell

For i = 3 To lastInsertRow Step 3
    Sheets("raulo_insert").Cells(i, 1).Value = "VALUES ("
    If answer = vbYes Then
        Sheets("raulo_insert").Cells(i, rng.Columns.Count + 2).Value = ");"
    Else
        Sheets("raulo_insert").Cells(i, rng.Columns.Count + 2).Value = ")"
    End If
Next i

Sheets("raulo_insert").Rows(lastInsertRow + 1).Delete Shift:=xlUp
Sheets("raulo_insert").Rows(lastInsertRow + 1).Delete Shift:=xlUp

Application.ScreenUpdating = True
MsgBox "Cantidad de insert creados: " & totalRows - 1, , "Raulo Insert"

Exit Sub
Error:
    MsgBox "Ha ocurrido un error, verifique la seleccion a procesar", , "Error"
End Sub