Excel 2016 VBA, How to transfer specific data on to another sheet in same workbook?

[Windy borough]

Hello,

New user here. Not much experience of excel or vba to be honest. Now that its out of the way. I have 2 questions and I'm hoping somebody here can help me achieve the desired result. Your help is appreciated.

I'll try my best to explain and remain short and to the point, your patience is also appreciated.

I have a workbook with 13 sheets. 1 to 12 are months of the year. Last sheet is Profit & Loss sheet. I designed a user form for the workbook/sheet and all works perfectly. A new sheet was added yesterday which is Profit & Loss and what I need is that when user adds data using user form all data is entered into the relevant sheet (month selection by combobox). Once the data is lets say January 2019 sheet, from there some of it should also be transferred into the Profit & Loss sheet.

Months sheets have simple columns. Date, comment, rent, admin, misc, out and balance. The fields on Profit & Loss sheet are different, slightly different. They are Date, comment, admin and out. Question is how do I achieve this? Any formula or VBA code? I have no knowledge and need help doing this. The code bellow is for the CommandButton1 which adds data into the workbook/sheet.

Private Sub CommandButton1_Click()


Dim dcc As Long
Dim abc As Worksheet


Set abc = ThisWorkbook.Worksheets(Me.ComboBox1.Value)


With abc


dcc = .Range("A" & Rows.Count).End(xlUp).Row


.Cells(dcc + 1, 1).Value = Date
.Cells(dcc + 1, 2).Value = Me.TextBox1.Value
.Cells(dcc + 1, 3).Value = Me.TextBox2.Value
.Cells(dcc + 1, 4).Value = Me.TextBox3.Value
.Cells(dcc + 1, 5).Value = Me.TextBox4.Value
.Cells(dcc + 1, 6).Value = Me.TextBox5.Value


End With
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""




End Sub

Code for user form initialization,

Private Sub UserForm_Initialize()


Dim i As Long
For i = 1 To ThisWorkbook.Sheets.Count
Me.ComboBox1.AddItem ThisWorkbook.Sheets(i).Name
Next

End Sub

Many thanks for your patience and I hope some of the expert users can help me with this. I appreciate your help. Thanks in advance.

Cheers.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub CommandButton1_Click()
   Dim NxtRw As Long
      
   With ThisWorkbook.Worksheets(Me.ComboBox1.Value)
      NxtRw = .Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      .Range("A" & NxtRw).Resize(, 6).Value = _
         Array(Date, Me.TextBox1.Value, Me.TextBox2.Value, Me.TextBox3.Value, Me.TextBox4.Value, Me.TextBox5.Value)
   End With
   With ThisWorkbook.Sheets("Profit & Loss")
      NxtRw = .Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      .Range("A" & NxtRw).Resize(, 4).Value = Array(Date, Me.TextBox1.Value, Me.TextBox3.Value, Me.TextBox5.Value)
   End With
   TextBox1.Text = ""
   TextBox2.Text = ""
   TextBox3.Text = ""
   TextBox4.Text = ""
   TextBox5.Text = ""
End Sub

 

[Windy borough]

Many thanks for your help. I appreciate it very much! I have another question, shall I ask in the same thread or start a new one?

Hi & welcome to MrExcel.
How about

Private Sub CommandButton1_Click()
   Dim NxtRw As Long
      
   With ThisWorkbook.Worksheets(Me.ComboBox1.Value)
      NxtRw = .Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      .Range("A" & NxtRw).Resize(, 6).Value = _
         Array(Date, Me.TextBox1.Value, Me.TextBox2.Value, Me.TextBox3.Value, Me.TextBox4.Value, Me.TextBox5.Value)
   End With
   With ThisWorkbook.Sheets("Profit & Loss")
      NxtRw = .Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      .Range("A" & NxtRw).Resize(, 4).Value = Array(Date, Me.TextBox1.Value, Me.TextBox3.Value, Me.TextBox5.Value)
   End With
   TextBox1.Text = ""
   TextBox2.Text = ""
   TextBox3.Text = ""
   TextBox4.Text = ""
   TextBox5.Text = ""
End Sub

 

[Fluff]

If it's similar to this question, ask it here, otherwise start a new thread.