VBA code to copy textbox input to specific cell

Hi All:

I'm having a little issue troubleshooting the code below.

This is the Userform:

Userform.png

The desired output is that a new row is added to the active sheet. The "Text" input will appear in the B Column position of the new row. The "Code" input will appear in the A Column of the new row.

The actual output is that a new row is added to the active sheet (Good). The "Text" input appears in the B Column position of the new row (Good). The "Code" input always inputs the number "14" (no matter what the actual input is in this box-not Good). I get a run-time error '1004': AutoFill method of Range class failed.

So, could someone take a look at the below and give some suggestions?

Private Sub CommandButton1_Click()
'MsgBox UserForm1.TextBox1.Value
UserForm1.TextBox1.Value = Worksheets.Count

Dim lab1 As String
Dim lab2 As String
lab1 = UserForm1.TextBox1.Value
lab2 = UserForm1.TextBox2.Value
If lab1 = "" Or lab2 = "" Then
MsgBox "Fill in both boxes!"

Else
Dim ws As Worksheet

Set ws = ActiveSheet

For Each Cell In ws.Range("A10:A100")
If Len(Cell) = 0 Then Cell.Select: Exit For
Next Cell
ActiveCell.EntireRow.Insert
myRow = ActiveCell.Row
myOldRow = myRow - 1
Cells(myRow, "A") = lab1
Cells(myRow, "B") = lab2
Set SourceRange = ws.Range("F" & myOldRow & ":Y" & myOldRow & "")
Set fillRange = ws.Range("F" & myRow & ":Y" & myRow & "")
SourceRange.AutoFill Destination:=fillRange
Dim lastColumn As Long


'lastColexumn = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column
'Columns(lastColumn).Copy Destination:=Columns(lastColumn + 1)

UserForm1.Hide
End If

Can you upload your sheet? its difficult to recreate without seeing the form etc.

@Mike: Thanks for your response! I played with the code, and it is posting the new row at the beginning of my worksheet. @PJ, I had to scrub data before posting, but the worksheet should now be attached. The issue is no longer "14" posting in the "A" column, but
now a "1".Test WS.xlsm

In addition to the original post, is there a way to build in code to copy the formulas of the preceding row into the newly created row? For example, the end of my data of interest (row 18) would have a new row with the user-inputted data and have the formulas
already prefilled from the previous row.

Thank you both for your interest/response.

Mike, thank you for that, I see that it works. However, how would I get the row to insert at the end of data (row 18)? I would like it to be inserted here so that any other data below that is pushed down when the new row is added. I'm playing with the "Row +!1" code but so far, nothing's working out.

I apologize for the late follow-up, there were other "fires" burning and this got pushed back a little.

Nevermind, I *think* I got it working.

Okay, appears to be doing everything I need it to do, *except* the first the code is ran, it adds a new row at the end of the set of data. After that, when it the code is ran, it only replaces the cells of focus (in columns A and B) with whatever is inputted in the userform. No new rows are added.

Please Login or Register  to view this content.