User @Lambik helped me a lot in achieving functionality in this workbook. Successfully managed to get the data from userform1 to January 2019 sheet and at the same time transferred on to the profitloss sheet. What I didn't thought about was that in profit and loss all fields can not be copied and pasted.
I tried to add new label and new textbox on the userform and get it to work by studying the code provided by @Lambik but there was another error, not error its more like a repetition, if I keep the edited version of userform1 (I've deleted it btw very non practical) then even if extra fields are "not" filled on the userform1 the rest of the entries still go into profitloss, that means, manually deleting every entry which is not suppose to be in there. So I thought long and hard and only thing I can think of is to add a checkbox on the userform1 and when the checkbox is clicked only then the first two fields from userform1 which are comment and rent go into profitloss along with the respective month selected from the combobox1. I think, I think it's the right logic. If anybody can help with the VBA code that how to achieve this using the checkbox then I'd appreciate it very much. I've attached links to the screenshots of the workbook, column rows of profitloss, old userform1 (works like a charm, thanks to @Lambik) and the new edited userform1 with the extra label and checkbox to have data copied onto the profit and loss sheet along with the current month selected.
The code that have the userform1 working is,
Private Sub ComboBox1_Change()
Dim SheetName As String
Dim ws As Worksheet
Dim LastRow As Long
SheetName = ComboBox1.Value
Set ws = Sheets(SheetName)
LastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row
Label8.Caption = " Balance is: " & ws.Cells(LastRow, 7).Value
Private Sub CommandButton1_Click()
Dim dcc As Long
Dim abc As Worksheet, pfl As Worksheet
Set abc = ThisWorkbook.Worksheets(Me.ComboBox1.Value)
Set pfl = Sheets("ProfitLoss")
With abc
dcc = .Range("A" & Rows.Count).End(xlUp).Row
.Cells(dcc + 1, 1).Value = Date
.Cells(dcc + 1, 2).Value = Me.TextBox1.Value (comment/source)
.Cells(dcc + 1, 3).Value = Me.TextBox2.Value (rent)
.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
With pfl
dcc = .Range("A" & Rows.Count).End(xlUp).Row
.Cells(dcc + 1, 1).Value = Date
.Cells(dcc + 1, 2).Value = Me.TextBox1.Value (comment/source)
.Cells(dcc + 1, 3).Value = Me.TextBox2.Value (In)
.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
The added checkbox on userform1 is checkbox1 and I put (comment/source) (rent) in code above to identify which rows needs to be transferred. If possible then when the checkbox is checked meaning entry will go into january 2019 and also in profitloss then that entry be automatically be highlighted and change colour to lets say green? or any for that matter. just to stand out if userform1 is not in use and user is just looking at the sheet. I appreciate your help in achieving this. Thanks in advance.
Cheers.
P.S. From userform1 only comment and rent needs to be added onto the profitloss sheet when the checkbox is checked and nothing else.
