CODEWITHSUNDEEP
vbaexcel
Ashraful Alam
Ashraful Alam

Reputation: 1

always Save user form data on specific worksheet in excel

i am new in vba, currently i am trying to write a vba code for a user form in excel that will save data automatically on a particular worksheet (fao). i have two more worksheet (data & reports) in this workbook. but no matter which sheet is open, when i try to enter data by user form it must store the data on the third worksheet called (fao). here is my code:

Private Sub CommandButton1_Click()

Dim ws As Worksheet
Dim RowCount As Long
Dim ctl As Control
Set ws = Worksheets("fao")

RowCount = Worksheets("fao").Range("A2").CurrentRegion.Rows.Count

  With Range("A2")
  .Offset(RowCount, 0).Value = Me.TextBox24.Value
  .Offset(RowCount, 1).Value = Me.TextBox23.Value
  .Offset(RowCount, 2).Value = Me.TextBox22.Value
  .Offset(RowCount, 3).Value = Me.TextBox21.Value
  .Offset(RowCount, 4).Value = Me.TextBox20.Value
  .Offset(RowCount, 5).Value = Me.TextBox19.Value
  .Offset(RowCount, 6).Value = Me.TextBox17.Value
  .Offset(RowCount, 8).Value = DateValue(Me.TextBox25.Value)
  .Offset(RowCount, 9).Value = DateValue(Me.TextBox14.Value)
  .Offset(RowCount, 10).Value = Me.TextBox15.Value
  If Me.OptionButton1.Value = True Then
  .Offset(RowCount, 7).Value = "Yds"
  Else
  .Offset(RowCount, 7).Value = "Mtr"
  End If
  End With
  End Sub

Upvotes: 0

Views: 1054

Answers (1)

Abe Gold
Abe Gold

Reputation: 2347

I assume this is the answer to your 'question'.

With ws.Range("A2")

Upvotes: 1

Related Questions