How to ensure updates from userform is saved in the new rows of your Worksheets

Yusuf_ · Dec 28th 2020

Hi everyone.

I have a user-form with a Combo-Box and 6 text fields and 3 buttons( Update, Reset, Exit). My Combo-Box contains all the worksheets( i have 4 of it) as it contents

How do I ensure that after updating my desired worksheets, the updates always falls in the new rows.

My subsequent updates always overrides the previous update and it defeating the idea of updating from the user-form

This is my update button code

Code

Private Sub Cmd_Update_Data_Click()
TargetSheet = Cmb_Application.Value ' Cmb_Application is the name of my Combo-Box
If TargetSheet = "" Then
MsgBox ("Please, Select the Region for the data update")
Exit Sub
End If
Worksheets(TargetSheet).Activate
Dim LastRow As Long
LastRow = ActiveSheet.Range("D999999").End(xlUp).Row + 1
ActiveSheet.Range("E" & LastRow).Value = Txt_Site_ID.Value 'Change accordingly
ActiveSheet.Range("F" & LastRow).Value = Txt_Site_Address.Value
ActiveSheet.Range("G" & LastRow).Value = Txt_CPD.Value
ActiveSheet.Range("M" & LastRow).Value = Txt_DSD.Value
ActiveSheet.Range("H" & LastRow).Value = Txt_SD.Value
ActiveSheet.Range("I" & LastRow).Value = Txt_ED.Value
MsgBox ("The data has been updated to " & TargetSheet & " sheet")
End Sub

Display More

Please help

**royUK** · Dec 28th 2020

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Note: no apostrophe in the tags, just used for demonstration here.

['code]

your code goes between these tags

['/code]

Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

**royUK** · Dec 28th 2020

That's what your code should be doing. Is there any entries below the data?

Yusuf_ · Dec 28th 2020

Quote from royUK

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags
Note: no apostrophe in the tags, just used for demonstration here.
['code]

your code goes between these tags

['/code]

Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

Display More

Okay. Thanks

Yusuf_ · Dec 28th 2020

Quote from royUK

That's what your code should be doing. Is there any entries below the data?

I have no entries below the data.

**royUK** · Dec 28th 2020

You need to attach an example of the file

Yusuf_ · Dec 28th 2020

Thanks, I have worked it out

It happens that my LastRow was wrong

Code

LastRow = ActiveSheet.Range("D999999").End(xlUp).Row + 1

This fixed it

Code

LastRow = ActiveSheet.Range("E999999").End(xlUp).Row + 1

Thanks.

**royUK** · Dec 28th 2020

Pleased you got it working.

Share

Tags