0

I am a real newbie and have been searching on here for help but cannot find what I'm after.

I have created a user form that populates data into a target worksheet, I need it to sequentially number each row of data and ensure that every time I transfer data it continues the numbering where it left off.

I do not know where to start.

Gass
  • 7,536
  • 3
  • 37
  • 41
Niska
  • 1
  • 1

1 Answers1

0

I hope I understood you correctly. Here is what the macro does.

enter image description here

When you try it remember to make changes to the config area of the code.

Sub NumberSequence()

Dim input_one As String, input_two As String, input_three As String
Dim target As String, main As String, col_start As String
Dim col_start_number As Long, substract As Long
Dim next_row As Long, next_number As Long

'CONFIG HERE
'---------------------------------------
'ranges of form inputs
input_one = "B3"
input_two = "C3"
input_three = "D3"

'first column where data starts in target sheet
col_start = "A"

'from what row do you want to start counting?
substract = 3

'names of sheets
target = "target"
main = "form"
'---------------------------------------

'convert the letter of the column to a number
col_start_number = Range(col_start & 1).Column

'get next empty row in target sheet
next_row = _
Sheets(target).Cells(Rows.Count, col_start_number).End(xlUp).row + 1

'next number in the sequence
next_number = next_row - substract + 1

With Sheets(target)
    
    'transfer the sequencial number
    .Cells(next_row, col_start_number) = next_number
    
    'transfer the inputs of the form
    .Cells(next_row, col_start_number + 1) = _
    Sheets(main).Range(input_one)

    .Cells(next_row, col_start_number + 2) = _
    Sheets(main).Range(input_two)
    
    .Cells(next_row, col_start_number + 3) = _
    Sheets(main).Range(input_three)

End With

End Sub
Gass
  • 7,536
  • 3
  • 37
  • 41