Transferring information from a User Form to a table

[cmerrick]

Good morning/afternoon,

I have a some code that I'm using to transfer data from a user form to a table. The code was set up to place the data in the last row of the table (creating more rows). I've used it before so I know that this code works. The problem I have with the new table, is that the last 4 rows are used to total various columns so I need the new rows to be added above the last 4 rows.

The functionality should remain the same, so I want each new row to shift the last 4 rows down by one if that that makes sense?

If anyone can assist it would be great.

This is the code I'm using at the moment.

Private Sub CommandButton2_Click()
Dim emptyRow As Long
 
 Sheet3.Activate
 
 
 emptyRow = Sheets(3).Range("AS" & Rows.Count).End(xlUp).Row + 1
 
 Cells(emptyRow, 4).Value = TextBox1.Value
 
 Cells(emptyRow, 8).Value = ComboBox5.Value
 Cells(emptyRow, 9).Value = ComboBox6.Value
 
 
 Cells(emptyRow, 12).Value = ComboBox7.Value
 Cells(emptyRow, 13).Value = ComboBox8.Value
 
 Cells(emptyRow, 16).Value = ComboBox9.Value
 Cells(emptyRow, 17).Value = ComboBox10.Value
 
 Cells(emptyRow, 20).Value = ComboBox11.Value
 Cells(emptyRow, 21).Value = ComboBox12.Value
 
 Cells(emptyRow, 24).Value = ComboBox13.Value
 Cells(emptyRow, 25).Value = ComboBox14.Value
 
 Cells(emptyRow, 39).Value = ComboBox1.Value
 
 Cells(emptyRow, 42).Value = ComboBox2.Value
 Cells(emptyRow, 43).Value = ComboBox3.Value
 Cells(emptyRow, 44).Value = ComboBox4.Value
 
 
If OptionButton5.Value = True Then
     Cells(emptyRow, 5).Value = "FT"
Else
     Cells(emptyRow, 5).Value = "PT"
     
End If
     
If OptionButton1.Value = True Then
     Cells(emptyRow, 6).Value = "Y"
Else
     Cells(emptyRow, 6).Value = "N"
     
End If
End Sub

 

[Fluff]

Try

emptyrow = Sheets(3).Range("AS" & Rows.Count).End(xlUp).Row - 3
Rows(emptyrow).Insert

 

[cmerrick]

Hiya,

Thanks for the help.

It's working but it keeps bringing up the file open window too for some reason ?

Any ideas ?

 

[Fluff]

Do you have any formulas that reference other workbooks?

 

[cmerrick]

Ahh, yes I do.

There are two columns hat reference an external document.

The one user form adds new employees to the spreadsheet (the one we are coding)

The other calls and allows to edit the data then sends it back (for existing employees)


This causes an issue because there are items of data that a new employee will not have (the data that is being referenced from another workbook).

Is there a way around this ?

Thank you kindly

 

[Fluff]

Is there a way around this ?

Almost certainly yes, but I'm not to familiar with this sort of thing.
However if you can supply a mock-up of both sheets, & show what your formulae are, then hopefully somebody else can step in and help.

 

[cmerrick]

In theory it should be fairly simple. We are just doing the same but ignoring the formula in the first two columns that pulls down when a new row is created.