Excel VBA code for consecutive number next to a pasted data

Question

I have a Userform where a commandbutton copies data from sheet1("DATOS GENERALES") range(F4:N22) and paste to sheet2("CARRITO") selecting B10 and looking for next empty row. Until now everything is going fine, but now I need it to place a consecutive number next to each paste on column 1 (A10).

My problem is that it pastes on different rows since data varies in the number of rows that contain data. For example the first paste would start on B10:J15, at this point I should have "01" on A10 and next paste it starts on B16:J20 and should auto-increment A16 with "2" and so on. Like I said the past on next empty cell is working fine, the issue is in the auto numbering. I'll place sample of the code which recalls always from A10.

I really appreciate your help.

Private Sub CommandButton4_Click()
   Worksheets("DATOS GENERALES").Range("F4:N20").Copy
   Sheets("CARRITO").Select
   Range("B1").End(xlDown).Offset(1, -1).Select
   Selection.Value = Range("A10").Value + 1
   Range("B1").End(xlDown).Offset(1, 0).Select
   Selection.PasteSpecial xlPasteFormats
   Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
   False, Transpose:=False

   Sheets("DATOS GENERALES").Select
   Range("F4:N22").Cells.ClearContents
   Application.CutCopyMode = False
   Sheets("DATOS GENERALES").Range("F4:N20").ClearFormats
   Range("F2:G2").Cells.ClearContents
   Range("Q1:Q16").Cells.ClearContents
   Range("H2:I2").Cells.ClearContents



End Sub

Currently it numbers A10 to "1" and A16 to "2" and if next past was on B25 it would put A25 to "2".

The following code has Dim for all abbreviations. The values to send from one sheet to the other vary depending on type of cabinet since but for example some have hinges others have slides and others have both so on target sheet adds quantity of those and adds them, they range from column A to column M and those that dont carry any hardware of a type it would should place "0" on the rest of the rows. Somehow it doesnt work.

The following is the expected code to reduce code size since if I run the code as it is now (I would place below) the working one only in Windows 10.

Dg.Select
If Cells(2, 6) = "" Then
    MsgBox "SELECCIONAR MUEBLE"
    Range("Q1").Cells.ClearContents
    Range("H2:I2").Cells.ClearContents
    Exit Sub
    ElseIf Cells(2, 8) = "" Then
    MsgBox "SELECCIONE CANTIDAD"
    Exit Sub
ElseIf Cells(1, 17) = "" Then
    MsgBox "FAVOR DE INDICAR ANCHO DEL MUEBLE"
    Exit Sub
ElseIf Cells(2, 17) = "" Then
    MsgBox "FAVOR DE INDICAR ALTO DEL MUEBLE"
    Exit Sub
    ElseIf Cells(4, 17) = "" Then
    MsgBox "FAVOR DE INDICAR FONDO DEL MUEBLE"
    Exit Sub
    ElseIf Cells(2, 6) = "BASE TARJA" Then
With Tarja
    Set Target = Dg.Range("F4")
    .Range("A2:I7").Copy

    Target.PasteSpecial xlPasteFormats
    Target.PasteSpecial Paste:=xlValues, Operation:=xlNone,                    SkipBlanks:= 
                           False, Transpose:=False
    'HERRAJE
    With Tarja
    Set Target = Herraje.Range("A2").End(xlDown).Offset(1)
        Range("B24").Copy  'BISAGRA 110
        Target.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Set Target = Herraje.Range("E2").End(xlDown).Offset(1)
        Range("B21").Copy   'PATAS
        Target.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Set Target = Herraje.Range("F2").End(xlDown).Offset(1)
        Range("B20").Copy   'CONFIRMATS
        Target.PasteSpecial Paste:=xlValues, Operation:=xlNone,         SkipBlanks:=False, Transpose:=False
    Set Target = Herraje.Range("G2").End(xlDown).Offset(1)
        Range("B25").Copy   'JALADERAS
        Target.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Set Target = Herraje.Range("H2").End(xlDown).Offset(1)
        Range("B22").Copy   'CHILILLO 5/8
        Target.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Set Target = Herraje.Range("I2").End(xlDown).Offset(1)
        Range("B23").Copy   'CHILILLO BISAGRA
        Target.PasteSpecial Paste:=xlValues, Operation:=xlNone,    SkipBlanks:=False, Transpose:=False
End With
ElseIf Cells(2, 6) = "BASE FULL DOOR" Then
With Fd 'IT REPEATS

Current working code but to large for windows 7.

Private Sub CommandButton4_Click()
On Error GoTo errHandler
Application.ScreenUpdating = False

Dim wb1 As Workbook
Dim Dg, Herraje, Complementos, Carrito, Listbox, Tarja, Fd, Pc, Cajonera,       Be9, Ciegofd, Barrafd, Pantryc, Pantryh, Scomun, Sdiagonal, Se9, Shzls, Shzld,     Smicroondas, Scampana, Srefrigerador As Worksheet
Dim modulo, desgloce, cantidad As Range

Set wb1 = Workbooks("LISTA_DE_CORTE_V1_1.xlsm")
Set Dg = Worksheets("DATOS GENERALES")
Set Herraje = Worksheets("HERRAJE")
Set Complementos = Worksheets("COMPLEMENTOS")
Set Carrito = Worksheets("CARRITO")
Set Listbox = Worksheets("LISTBOX")
Set Tarja = Worksheets("TARJA")
Set Fd = Worksheets("FULL_DOOR")
Set Pc = Worksheets("PUERTA_CAJON")
Set Cajonera = Worksheets("CAJONERA")
Set Be9 = Worksheets("ESQUINERO_90")
Set Ciegofd = Worksheets("CIEGO_FULL_DOOR")
Set Barrafd = Worksheets("BARRA_FD")
Set Pantryc = Worksheets("PANTRY_COMUN")
Set Pantryh = Worksheets("PANTRY_HORNO")
Set Scomun = Worksheets("SUPERIOR_COMUN")
Set Sdiagonal = Worksheets("SUPERIOR_DIAGONAL")
Set Se9 = Worksheets("SUP_ESQ_90")
Set Shzls = Worksheets("SUPERIOR_HZL_SENCILLO")
Set Shzld = Worksheets("SUPERIOR_HZL_DOBLE")
Set Smicroondas = Worksheets("SUPERIOR_MICRO")
Set Scampana = Worksheets("SUPERIOR_CAMPANA")
Set Srefrigerador = Worksheets("SUPERIOR_REFRI")


Dg.Range("F2:G2").Value = ComboBox7.Text
Dg.Range("H2:I2").Value = TextBox15.Text
Dg.Range("Q1").Value = TextBox5.Text
Dg.Range("Q2").Value = TextBox6.Text
Dg.Range("Q4").Value = TextBox7.Text
Dg.Range("Q5").Value = TextBox9.Text
Dg.Range("Q6").Value = ComboBox8.Text
Dg.Range("Q7").Value = TextBox8.Text
Dg.Range("B18").Value = TextBox10.Text
Dg.Range("B19").Value = TextBox11.Text
Dg.Range("B20").Value = TextBox12.Text
Dg.Range("B21").Value = TextBox13.Text
Dg.Range("B22").Value = TextBox14.Text


    Dg.Select
Range("F4:N17").Cells.ClearContents
If Cells(2, 6) = "" Then
    MsgBox "SELECCIONAR MUEBLE"
    Range("Q1").Cells.ClearContents
    Range("H2:I2").Cells.ClearContents
    Exit Sub
ElseIf Cells(2, 8) = "" Then
    MsgBox "SELECCIONE CANTIDAD"
    Exit Sub
ElseIf Cells(1, 17) = "" Then
    MsgBox "FAVOR DE INDICAR ANCHO DEL MUEBLE"
    Exit Sub
ElseIf Cells(2, 17) = "" Then
    MsgBox "FAVOR DE INDICAR ALTO DEL MUEBLE"
    Exit Sub
ElseIf Cells(4, 17) = "" Then
    MsgBox "FAVOR DE INDICAR FONDO DEL MUEBLE"
    Exit Sub
ElseIf Cells(2, 6) = "BASE TARJA" Then
Dg.Select
Range("F4:N22").Cells.ClearFormats
Range("F4:N22").Cells.ClearContents
Tarja.Visible = True
Tarja.Select
Range("A2:I7").Select
Selection.Copy
Dg.Select
Range("F4").Select
Selection.PasteSpecial xlPasteFormats
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Dg.Select
'HERRAJE
Herraje.Select
Range("A50").End(xlUp).Offset(1, 0).Select
Selection.Value = Tarja.Range("B24").Value 'BISAGRA 110
Herraje.Select
Range("E50").End(xlUp).Offset(1, 0).Select 'PATAS
Selection.Value = Tarja.Range("B21").Value
Herraje.Select
Range("F50").End(xlUp).Offset(1, 0).Select 'CONFIRMATS
Selection.Value = Tarja.Range("B20").Value
Herraje.Select
Range("G50").End(xlUp).Offset(1, 0).Select 'JALADERAS
Selection.Value = Tarja.Range("B25").Value
Herraje.Select
Range("H50").End(xlUp).Offset(1, 0).Select 'CHILILLO 5/8
Selection.Value = Tarja.Range("B22").Value
Herraje.Select
Range("I50").End(xlUp).Offset(1, 0).Select 'CHILILLO BISAGRA
Selection.Value = Tarja.Range("B23").Value


Herraje.Select
Range("B50").End(xlUp).Offset(1, 0).Select 'BISAGRA 170
Selection.Value = "0"
Herraje.Select
Range("C50").End(xlUp).Offset(1, 0).Select 'BISAGRA UNION
Selection.Value = "0"
Herraje.Select
Range("L50").End(xlUp).Offset(1, 0).Select 'BISAGRA 45
Selection.Value = "0"
Herraje.Select
Range("M50").End(xlUp).Offset(1, 0).Select 'PINS
Selection.Value = "0"
Herraje.Select
Range("D50").End(xlUp).Offset(1, 0).Select 'RIEL
Selection.Value = "0"
Herraje.Select
Range("J50").End(xlUp).Offset(1, 0).Select 'SISTEMA HORIZONTAL
Selection.Value = "0"
Range("K50").End(xlUp).Offset(1, 0).Select 'MINIFIX
Selection.Value = "0"
Tarja.Visible = False
ElseIf Cells(2, 6) = "BASE FULL DOOR" Then
Sheets("DATOS GENERALES").Select
Range("F4:N22").Cells.ClearFormats
Range("F4:N22").Cells.ClearContents
Sheets("FULL_DOOR").Visible = True
Sheets("FULL_DOOR").Select
Range("A2:I8").Select
Selection.Copy
Sheets("DATOS GENERALES").Select
Range("F4").Select
Selection.PasteSpecial xlPasteFormats
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
'HERRAJE
Worksheets("HERRAJE").Select
Range("A50").End(xlUp).Offset(1, 0).Select
Selection.Value = Worksheets("FULL_DOOR").Range("B24").Value 'BISAGRA 110
Worksheets("HERRAJE").Select
Range("B50").End(xlUp).Offset(1, 0).Select 'BISAGRA 170
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("C50").End(xlUp).Offset(1, 0).Select 'BISAGRA UNION
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("L50").End(xlUp).Offset(1, 0).Select 'BISAGRA 45
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("M50").End(xlUp).Offset(1, 0).Select 'PINS
Selection.Value = Worksheets("FULL_DOOR").Range("B28").Value
Worksheets("HERRAJE").Select
Range("D50").End(xlUp).Offset(1, 0).Select 'RIEL
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("E50").End(xlUp).Offset(1, 0).Select
Selection.Value = Worksheets("FULL_DOOR").Range("B21").Value 'PATAS
Worksheets("HERRAJE").Select
Range("F50").End(xlUp).Offset(1, 0).Select
Selection.Value = Worksheets("FULL_DOOR").Range("B20").Value 'CONFIRMATS
Worksheets("HERRAJE").Select
Range("G50").End(xlUp).Offset(1, 0).Select
Selection.Value = Worksheets("FULL_DOOR").Range("B25").Value 'JALADERAS
Worksheets("HERRAJE").Select
Range("H50").End(xlUp).Offset(1, 0).Select
Selection.Value = Worksheets("FULL_DOOR").Range("B22").Value 'CHILILLO 5/8
Worksheets("HERRAJE").Select
Range("I50").End(xlUp).Offset(1, 0).Select
Selection.Value = Worksheets("FULL_DOOR").Range("B23").Value 'CHILILLO     BISAGRA
Worksheets("HERRAJE").Select
Range("J50").End(xlUp).Offset(1, 0).Select 'SISTEMA HORIZONTAL
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("K50").End(xlUp).Offset(1, 0).Select 'MINIFIX
Selection.Value = "0"
Sheets("FULL_DOOR").Visible = False
ElseIf Cells(2, 6) = "BASE PUERTA CAJON" Then
Sheets("DATOS GENERALES").Select
Range("F4:N22").Cells.ClearFormats
Range("F4:N22").Cells.ClearContents
Sheets("PUERTA_CAJON").Visible = True
Sheets("PUERTA_CAJON").Select
Range("A2:I13").Select
Selection.Copy
Sheets("DATOS GENERALES").Select
Range("F4").Select
Selection.PasteSpecial xlPasteFormats
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
'HERRAJE
Worksheets("HERRAJE").Select
Range("A50").End(xlUp).Offset(1, 0).Select
Selection.Value = Worksheets("PUERTA_CAJON").Range("B24").Value 'BISAGRA 110
Worksheets("HERRAJE").Select
Range("B50").End(xlUp).Offset(1, 0).Select 'BISAGRA 170
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("C50").End(xlUp).Offset(1, 0).Select 'BISAGRA UNION
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("L50").End(xlUp).Offset(1, 0).Select 'BISAGRA 45
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("M50").End(xlUp).Offset(1, 0).Select 'PINS
Selection.Value = Worksheets("PUERTA_CAJON").Range("B28").Value
Worksheets("HERRAJE").Select
Range("D50").End(xlUp).Offset(1, 0).Select 'RIEL
Selection.Value = Worksheets("PUERTA_CAJON").Range("B26").Value
Worksheets("HERRAJE").Select
Range("E50").End(xlUp).Offset(1, 0).Select 'PATAS
Selection.Value = Worksheets("PUERTA_CAJON").Range("B21").Value
Worksheets("HERRAJE").Select
Range("F50").End(xlUp).Offset(1, 0).Select 'CONFIRMATS
Selection.Value = Worksheets("PUERTA_CAJON").Range("B20").Value
Worksheets("HERRAJE").Select
Range("G50").End(xlUp).Offset(1, 0).Select 'JALADERAS
Selection.Value = Worksheets("PUERTA_CAJON").Range("B25").Value
Worksheets("HERRAJE").Select
Range("H50").End(xlUp).Offset(1, 0).Select 'CHILILLO 5/8
Selection.Value = Worksheets("PUERTA_CAJON").Range("B22").Value
Worksheets("HERRAJE").Select
Range("I50").End(xlUp).Offset(1, 0).Select 'CHILILLO BISAGRA
Selection.Value = Worksheets("PUERTA_CAJON").Range("B23").Value
Worksheets("HERRAJE").Select
Range("J50").End(xlUp).Offset(1, 0).Select 'SISTEMA HORIZONTAL
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("K50").End(xlUp).Offset(1, 0).Select 'MINIFIX
Selection.Value = Worksheets("PUERTA_CAJON").Range("B27").Value
Sheets("PUERTA_CAJON").Visible = False
ElseIf Cells(2, 6) = "BASE CAJONERA" Then
Sheets("DATOS GENERALES").Select
Range("F4:N22").Cells.ClearFormats
Range("F4:N22").Cells.ClearContents
Sheets("CAJONERA").Visible = True
Sheets("CAJONERA").Select
Range("A2:I12").Select
Selection.Copy
Sheets("DATOS GENERALES").Select
Range("F4").Select
Selection.PasteSpecial xlPasteFormats
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
'HERRAJE
Worksheets("HERRAJE").Select
Range("A50").End(xlUp).Offset(1, 0).Select 'BISAGRA 110
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("B50").End(xlUp).Offset(1, 0).Select 'BISAGRA 170
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("C50").End(xlUp).Offset(1, 0).Select 'BISAGRA UNION
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("L50").End(xlUp).Offset(1, 0).Select 'BISAGRA 45
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("M50").End(xlUp).Offset(1, 0).Select 'PINS
Selection.Value = Worksheets("CAJONERA").Range("B28").Value
Worksheets("HERRAJE").Select
Range("D50").End(xlUp).Offset(1, 0).Select 'RIEL
Selection.Value = Worksheets("CAJONERA").Range("B26").Value
Worksheets("HERRAJE").Select
Range("E50").End(xlUp).Offset(1, 0).Select 'PATAS
Selection.Value = Worksheets("CAJONERA").Range("B21").Value
Worksheets("HERRAJE").Select
Range("F50").End(xlUp).Offset(1, 0).Select 'CONFIRMATS
Selection.Value = Worksheets("CAJONERA").Range("B20").Value
Worksheets("HERRAJE").Select
Range("G50").End(xlUp).Offset(1, 0).Select 'JALADERAS
Selection.Value = Worksheets("CAJONERA").Range("B25").Value
Worksheets("HERRAJE").Select
Range("H50").End(xlUp).Offset(1, 0).Select 'CHILILLO 5/8
Selection.Value = Worksheets("CAJONERA").Range("B22").Value
Worksheets("HERRAJE").Select
Range("I50").End(xlUp).Offset(1, 0).Select 'CHILILLO BISAGRA
Selection.Value = Worksheets("CAJONERA").Range("B23").Value
Worksheets("HERRAJE").Select
Range("J50").End(xlUp).Offset(1, 0).Select 'SISTEMA HORIZONTAL
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("K50").End(xlUp).Offset(1, 0).Select 'MINIFIX
Selection.Value = Worksheets("CAJONERA").Range("B27").Value
Sheets("CAJONERA").Visible = False
ElseIf Cells(2, 6) = "BASE ESQUINERO 90" Then
Sheets("DATOS GENERALES").Select
Range("F4:N22").Cells.ClearFormats
Range("F4:N22").Cells.ClearContents
Sheets("ESQUINERO_90").Visible = True
Sheets("ESQUINERO_90").Select
Range("A2:I12").Select
Selection.Copy
Sheets("DATOS GENERALES").Select
Range("F4").Select
Selection.PasteSpecial xlPasteFormats
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
'HERRAJE
Worksheets("HERRAJE").Select
Range("A50").End(xlUp).Offset(1, 0).Select 'BISAGRA 110
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("B50").End(xlUp).Offset(1, 0).Select 'BISAGRA 170
Selection.Value = Worksheets("ESQUINERO_90").Range("B24").Value
Worksheets("HERRAJE").Select
Range("C50").End(xlUp).Offset(1, 0).Select 'BISAGRA UNION
Selection.Value = Worksheets("ESQUINERO_90").Range("B25").Value
Worksheets("HERRAJE").Select
Range("L50").End(xlUp).Offset(1, 0).Select 'BISAGRA 45
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("M50").End(xlUp).Offset(1, 0).Select 'PINS
Selection.Value = Worksheets("ESQUINERO_90").Range("B28").Value
Worksheets("HERRAJE").Select
Range("D50").End(xlUp).Offset(1, 0).Select 'RIEL
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("E50").End(xlUp).Offset(1, 0).Select 'PATAS
Selection.Value = Worksheets("ESQUINERO_90").Range("B21").Value
Worksheets("HERRAJE").Select
Range("F50").End(xlUp).Offset(1, 0).Select 'CONFIRMATS
Selection.Value = Worksheets("ESQUINERO_90").Range("B20").Value
Worksheets("HERRAJE").Select
Range("G50").End(xlUp).Offset(1, 0).Select 'JALADERAS
Selection.Value = Worksheets("ESQUINERO_90").Range("B26").Value
Worksheets("HERRAJE").Select
Range("H50").End(xlUp).Offset(1, 0).Select 'CHILILLO 5/8
Selection.Value = Worksheets("ESQUINERO_90").Range("B22").Value
Worksheets("HERRAJE").Select
Range("I50").End(xlUp).Offset(1, 0).Select 'CHILILLO BISAGRA
Selection.Value = Worksheets("ESQUINERO_90").Range("B23").Value
Worksheets("HERRAJE").Select
Range("J50").End(xlUp).Offset(1, 0).Select 'SISTEMA HORIZONTAL
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("K50").End(xlUp).Offset(1, 0).Select 'MINIFIX
Selection.Value = "0"
Sheets("ESQUINERO_90").Visible = False
ElseIf Cells(2, 6) = "BASE CIEGO FULL DOOR" Then
Sheets("DATOS GENERALES").Select
Range("F4:N22").Cells.ClearFormats
Range("F4:N22").Cells.ClearContents
Sheets("CIEGO_FULL_DOOR").Visible = True
Sheets("CIEGO_FULL_DOOR").Select
Range("A2:I9").Select
Selection.Copy
Sheets("DATOS GENERALES").Select
Range("F4").Select
Selection.PasteSpecial xlPasteFormats
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
'HERRAJE
Worksheets("HERRAJE").Select
Range("A50").End(xlUp).Offset(1, 0).Select
Selection.Value = Worksheets("CIEGO_FULL_DOOR").Range("B24").Value 'BISAGRA     110
Worksheets("HERRAJE").Select
Range("B50").End(xlUp).Offset(1, 0).Select 'BISAGRA 170
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("C50").End(xlUp).Offset(1, 0).Select 'BISAGRA UNION
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("L50").End(xlUp).Offset(1, 0).Select 'BISAGRA 45
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("M50").End(xlUp).Offset(1, 0).Select 'PINS
Selection.Value = Worksheets("CIEGO_FULL_DOOR").Range("B28").Value
Worksheets("HERRAJE").Select
Range("D50").End(xlUp).Offset(1, 0).Select 'RIEL
Selection.Value = Worksheets("CIEGO_FULL_DOOR").Range("B26").Value
Worksheets("HERRAJE").Select
Range("E50").End(xlUp).Offset(1, 0).Select 'PATAS
Selection.Value = Worksheets("CIEGO_FULL_DOOR").Range("B21").Value
Worksheets("HERRAJE").Select
Range("F50").End(xlUp).Offset(1, 0).Select 'CONFIRMATS
Selection.Value = Worksheets("CIEGO_FULL_DOOR").Range("B20").Value
Worksheets("HERRAJE").Select
Range("G50").End(xlUp).Offset(1, 0).Select 'JALADERAS
Selection.Value = Worksheets("CIEGO_FULL_DOOR").Range("B25").Value
Worksheets("HERRAJE").Select
Range("H50").End(xlUp).Offset(1, 0).Select 'CHILILLO 5/8
Selection.Value = Worksheets("CIEGO_FULL_DOOR").Range("B22").Value
Worksheets("HERRAJE").Select
Range("I50").End(xlUp).Offset(1, 0).Select 'CHILILLO BISAGRA
Selection.Value = Worksheets("CIEGO_FULL_DOOR").Range("B23").Value
Worksheets("HERRAJE").Select
Range("J50").End(xlUp).Offset(1, 0).Select 'SISTEMA HORIZONTAL
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("K50").End(xlUp).Offset(1, 0).Select 'MINIFIX
Selection.Value = "0"
Sheets("CIEGO_FULL_DOOR").Visible = False
ElseIf Cells(2, 6) = "BASE BARRA FULL DOOR" Then
Sheets("DATOS GENERALES").Select
Range("F4:N22").Cells.ClearFormats
Range("F4:N22").Cells.ClearContents
Sheets("BARRA_FD").Visible = True
Sheets("BARRA_FD").Select
Range("A2:I12").Select
Selection.Copy
Sheets("DATOS GENERALES").Select
Range("F4").Select
Selection.PasteSpecial xlPasteFormats
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
'HERRAJE
Worksheets("HERRAJE").Select
Range("A50").End(xlUp).Offset(1, 0).Select
Selection.Value = Worksheets("BARRA_FD").Range("B24").Value 'BISAGRA 110
Worksheets("HERRAJE").Select
Range("B50").End(xlUp).Offset(1, 0).Select 'BISAGRA 170
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("C50").End(xlUp).Offset(1, 0).Select 'BISAGRA UNION
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("L50").End(xlUp).Offset(1, 0).Select 'BISAGRA 45
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("M50").End(xlUp).Offset(1, 0).Select 'PINS
Selection.Value = Worksheets("BARRA_FD").Range("B28").Value
Worksheets("HERRAJE").Select
Range("D50").End(xlUp).Offset(1, 0).Select 'RIEL
Selection.Value = Worksheets("BARRA_FD").Range("B26").Value
Worksheets("HERRAJE").Select
Range("E50").End(xlUp).Offset(1, 0).Select 'PATAS
Selection.Value = Worksheets("BARRA_FD").Range("B21").Value
Worksheets("HERRAJE").Select
Range("F50").End(xlUp).Offset(1, 0).Select 'CONFIRMATS
Selection.Value = Worksheets("BARRA_FD").Range("B20").Value
Worksheets("HERRAJE").Select
Range("G50").End(xlUp).Offset(1, 0).Select 'JALADERAS
Selection.Value = Worksheets("BARRA_FD").Range("B25").Value
Worksheets("HERRAJE").Select
Range("H50").End(xlUp).Offset(1, 0).Select 'CHILILLO 5/8
Selection.Value = Worksheets("BARRA_FD").Range("B22").Value
Worksheets("HERRAJE").Select
Range("I50").End(xlUp).Offset(1, 0).Select 'CHILILLO BISAGRA
Selection.Value = Worksheets("BARRA_FD").Range("B23").Value
Worksheets("HERRAJE").Select
Range("J50").End(xlUp).Offset(1, 0).Select 'SISTEMA HORIZONTAL
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("K50").End(xlUp).Offset(1, 0).Select 'MINIFIX
Selection.Value = "0"
Sheets("BARRA_FD").Visible = False
ElseIf Cells(2, 6) = "PANTRY COMUN" Then
Sheets("DATOS GENERALES").Select
Range("F4:N22").Cells.ClearFormats
Range("F4:N22").Cells.ClearContents
Sheets("PANTRY_COMUN").Visible = True
Sheets("PANTRY_COMUN").Select
Range("A2:I10").Select
Selection.Copy
Sheets("DATOS GENERALES").Select
Range("F4").Select
Selection.PasteSpecial xlPasteFormats
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
'HERRAJE
Worksheets("HERRAJE").Select
Range("A50").End(xlUp).Offset(1, 0).Select
Selection.Value = Worksheets("PANTRY_COMUN").Range("B24").Value 'BISAGRA 110
Worksheets("HERRAJE").Select
Range("B50").End(xlUp).Offset(1, 0).Select 'BISAGRA 170
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("C50").End(xlUp).Offset(1, 0).Select 'BISAGRA UNION
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("L50").End(xlUp).Offset(1, 0).Select 'BISAGRA 45
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("M50").End(xlUp).Offset(1, 0).Select 'PINS
Selection.Value = Worksheets("PANTRY_COMUN").Range("B28").Value
Worksheets("HERRAJE").Select
Range("D50").End(xlUp).Offset(1, 0).Select 'RIEL
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("E50").End(xlUp).Offset(1, 0).Select 'PATAS
Selection.Value = Worksheets("PANTRY_COMUN").Range("B21").Value
Worksheets("HERRAJE").Select
Range("F50").End(xlUp).Offset(1, 0).Select 'CONFIRMATS
Selection.Value = Worksheets("PANTRY_COMUN").Range("B20").Value
Worksheets("HERRAJE").Select
Range("G50").End(xlUp).Offset(1, 0).Select 'JALADERAS
Selection.Value = Worksheets("PANTRY_COMUN").Range("B25").Value
Worksheets("HERRAJE").Select
Range("H50").End(xlUp).Offset(1, 0).Select 'CHILILLO 5/8
Selection.Value = Worksheets("PANTRY_COMUN").Range("B22").Value
Worksheets("HERRAJE").Select
Range("I50").End(xlUp).Offset(1, 0).Select 'CHILILLO BISAGRA
Selection.Value = Worksheets("PANTRY_COMUN").Range("B23").Value
Worksheets("HERRAJE").Select
Range("J50").End(xlUp).Offset(1, 0).Select 'SISTEMA HORIZONTAL
Selection.Value = "0"
Worksheets("HERRAJE").Select
Range("K50").End(xlUp).Offset(1, 0).Select 'MINIFIX
Selection.Value = "0"
Sheets("PANTRY_COMUN").Visible = False

Else
Sheets("DATOS GENERALES").Select
Range("F4:N22").Cells.ClearFormats
Range("F4:N22").Cells.ClearContents
End If

Application.CutCopyMode = False
ActiveWorkbook.Save

'Copiar y pegar en carrito

Dim Target As Range

With Worksheets("DATOS GENERALES")
    Set Target = Sheets("CARRITO").Range("B1").End(xlDown).Offset(1)
    .Range("F4:N20").Copy

    Target.Offset(0, -1).Value =   WorksheetFunction.Max(Sheets("CARRITO").Columns(1)) + 1

    Target.PasteSpecial xlPasteFormats
    Target.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                           False, Transpose:=False

    .Range("F4:N22").Cells.ClearContents
    .Range("F4:N20").ClearFormats
    .Range("F4:N22,F2:G2,Q1:Q16,H2:I2").ClearContents
End with

'////////////////

'Copiar y pegar de carrito a "LISTBOX"
Sheets("CARRITO").Visible = True
Sheets("CARRITO").Select
Range("A1000").End(xlUp).Select
ActiveCell.Resize(1, 2).Copy
Sheets("LISTBOX").Visible = True
Sheets("LISTBOX").Select
lMaxRows = Cells(Rows.Count, "D").End(xlUp).Row
Range("D" & lMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Sheets("LISTBOX").Visible = False
Sheets("CARRITO").Visible = False
'///////////////
Sheets("DATOS GENERALES").Select
Range("F4:N22").Cells.ClearContents
Application.CutCopyMode = False
Sheets("DATOS GENERALES").Range("F4:N20").ClearFormats
Range("F2:G2").Cells.ClearContents
Range("Q1:Q16").Cells.ClearContents
Range("H2:I2").Cells.ClearContents

'Listbox1 Agregar Articulo

ListBox1.ListIndex = ListBox1.ListIndex
ListBox2.ListIndex = ListBox2.ListIndex


'//////////////
Worksheets("HERRAJE").Visible = False



Application.ScreenUpdating = True
errHandler:
Application.ScreenUpdating = True


End Sub

You're always just adding 1 to the value of A10, in this line: Selection.Value = Range("A10").Value + 1. — David Zemens
– David Zemens, Commented Jul 26, 2016 at 21:04

user3598756 · Accepted Answer · 2016-07-26 21:31:38Z

1

Replace

Selection.Value = Range("A10").Value + 1

With

Selection.Value = Selection.End(xlUp) + 1

answered Jul 26, 2016 at 21:31

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3598756 Over a year ago

@SalA.: did you get through it?

Sal A. Over a year ago

Hi, yes I have, I appreciate you guys being so helpful, I'm trying to make this little app, I have lots to go but I think is very advanced now maybe like 80% :)

user3598756 Over a year ago

So good coding! Then if I fulfilled your question please mark my answer as accepted. Thank you

user6432984 · Accepted Answer · 2016-07-27 05:57:33Z

0

I refactored your code using removing unnecessary cell selection, combing range addresses and using With Worksheets("DATOS GENERALES") to simplify referencing. I also took YowE3K suggestion and use the WorksheetFunction.Max to determine the next increment.

Private Sub CommandButton4_Click()
    Dim Target As Range

    With Worksheets("DATOS GENERALES")
        Set Target = Sheets("CARRITO").Range("B1").End(xlDown).Offset(1)
        .Range("F4:N20").Copy

        Target.Offset(0, -1).Value = WorksheetFunction.Max(Sheets("CARRITO").Columns(1)) + 1

        Target.PasteSpecial xlPasteFormats
        Target.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                               False, Transpose:=False

        .Range("F4:N22").Cells.ClearContents
        .Range("F4:N20").ClearFormats
        .Range("F4:N22,F2:G2,Q1:Q16,H2:I2").ClearContents

    End With

End Sub

answered Jul 27, 2016 at 5:57

user6432984

4 Comments

Sal A. Over a year ago

Hi Thomas, Some how I managed to get it working with, Worksheets("DATOS GENERALES").Range("F4:N20").Copy Sheets("CARRITO").Visible = True Sheets("CARRITO").Select Range("B1").End(xlDown).Offset(1, -1).Select Selection.Value = Range("A1000").End(xlUp).Value + 1 Range("B1").End(xlDown).Offset(1, 0).Select Selection.PasteSpecial xlPasteFormats Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone. SkipBlanks:= _ False, Transpose:=False

Sal A. Over a year ago

Sorry I'm new in forums and vba, 1 month experience hehe,

Sal A. Over a year ago

Thanks Thomas, I ended up using your suggested code and its great, I'm trying to do same thing with other part of coding in same userform, but cant figure out how to repeat this code from sheet "1" to sheet "2", I would post the code how I'm trying to implement yours.

user6432984 Over a year ago

I'll look over it tomorrow night when I get home from work.

YowE3K · Accepted Answer · 2016-07-26 21:28:20Z

0

Replace your line which says

Selection.Value = Range("A10").Value + 1

with

Selection.Value = Application.WorksheetFunction.Max(Range("A:A")) + 1

answered Jul 26, 2016 at 21:28

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Collectives™ on Stack Overflow

Excel VBA code for consecutive number next to a pasted data

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related