Issue with a VBA form on Excel...Pls help

[ryanadam]

Hi gents,
i am new to this forum, I am not savvy in Exce,I use the basic
Recently my boss asked me to prepare a sales activity tracker
I did it is working perfect when i use it. nowhewants all the team to use it
someof them are not familiar with Excel,so I am trying to create a a formwhere they will input their data and do not have to touch the file itself ( I locked all the formulas as well)

My data is organized the following way
and starts at row 27 (data starts going in 27)
<TABLE style="WIDTH: 338pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=450 x:str><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff; WIDTH: 84pt; HEIGHT: 13.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18 width=112>Month</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #99ccff; WIDTH: 35pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=47>Week</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #99ccff; WIDTH: 57pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=76>Day#</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #99ccff; WIDTH: 51pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=68>Date</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #99ccff; WIDTH: 52pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=69>Refer #</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #99ccff; WIDTH: 59pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=78 x:str="Product ">Product </TD></TR></TBODY></TABLE>
wrote this VBA script:

Private Sub CancelButton_Click()
Unload UserForm1
End Sub
Private Sub EducationLevel_Click()
End Sub
Private Sub Label1_Click()
End Sub
Private Sub OKButton_Click()
Sheets("MASTER_SHEET").Activate
NextRow = Application.WorksheetFunction.CountA(Range("B:B")) + 17
NextRow = Application.WorksheetFunction.CountA(Range("B:B")) + 17
Cells(NextRow, 5) = TextName.Text
If Optiongold Then Cells(NextRow, 6) = "GOLD"
If Optionplatinium Then Cells(NextRow, 6) = "platinum"
If Optionsilver Then Cells(NextRow, 6) = "silver"
If OptionACTIV Then Cells(NextRow, 6) = "ACTIV"
If OptionINS100 Then Cells(NextRow, 6) = "INS100"
If OptionINS200 Then Cells(NextRow, 6) = "INS200"
If OptionINS300 Then Cells(NextRow, 6) = "INS300"
If OptionINS400 Then Cells(NextRow, 6) = "INS400"
If OptionINS500 Then Cells(NextRow, 6) = "INS500"
If OptionINS600 Then Cells(NextRow, 6) = "INS600"
If OptionINS700 Then Cells(NextRow, 6) = "INS700"
If OptionINS800 Then Cells(NextRow, 6) = "INS800"

TextName.Text = ""
TextName.SetFocus

End Sub
--------------------------------
Now what happens:
1-The first input goes into the row 27 fine but after a couple of data entered it keeps on replacing of the last cell

I tried someother formulas but stillit does not seem tohelp
Please I need this in my new Job/Position,if anyone can help i wouldhighly appreciate

 

[Norie]

Sorry you've lost me.

What difference does it make that you need this in different modules?

If you are getting data from multiple worksheets you should be calculating it for each worksheet.

 

[MeghaJ]

NO...
Its like i m getting data for Dev project, maintennace project, porting project..etc to generate report.. then whichever tab is clikced first.. for tht project data will start from A13...
but afterwards it is just next row.
User can click on any button to get data for different project in different order.

So, i can not +3 everywhere... am i clear???

 

[Norie]

Sorry it isn't clear, to me anyway.

Do you mean that first time round you want to find the row where the data should start?

Then you need to find where the next lot of data goes, which is probably below that first lot of data.

If that's the case you should still be calculating the next row for each sheet.

For the first sheet add 3, for the rest don't.

Also I think you should start a new thread - the OP's original question wasn't really related to finding the last row of data.

Isn't that what you want to do?

NextRowValue = Range("A" & Rows.Count).End(xlUp).Row + 3 
 
' code to copy/paste first piece of data
 
NextRowValue = Range("A" & Rows.Count).End(xlUp).Row + 1
 
' code to copy/paste next piece of data
 
...etc