I have the below mention code which I need to run from a particular worksheet to a particular worksheet in increment of 1. My worksheet names are 1, 2, 3, 4, 5, 6, etc.
I am using Do Loop Until loop to execute the data entry part
I am getting stuck with line ws = ws + 1. Here ws is defined as Worksheets and I need to increment this by one (1). How do I do this? Currently as per below code I'm trying to add 1 which is number to ws which is Worksheets object.
Sub update_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim kcntr, rng1, rng2, rngk As Integer
rng1 = Worksheets("Data List").Cells(4, 18).Value
rng2 = Worksheets("Data List").Cells(9, 18).Value
rngk = rng2 - rng1
Set ws = Worksheets(CStr(Worksheets("Data List").Cells(i, 18).Value))
Do
'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row
'copy the data to the database
ws.Cells(iRow, 2).Value = Me.txtname.Value
ws.Cells(iRow, 4).Value = Me.txtcode.Value
'clear the data
Me.txtname.Value = ""
Me.txtcode.Value = ""
ws = ws + 1
Loop Until ws = Worksheets(Cstr(rng2))
End Sub
I am using Do Loop Until loop to execute the data entry part
I am getting stuck with line ws = ws + 1. Here ws is defined as Worksheets and I need to increment this by one (1). How do I do this? Currently as per below code I'm trying to add 1 which is number to ws which is Worksheets object.
Sub update_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim kcntr, rng1, rng2, rngk As Integer
rng1 = Worksheets("Data List").Cells(4, 18).Value
rng2 = Worksheets("Data List").Cells(9, 18).Value
rngk = rng2 - rng1
Set ws = Worksheets(CStr(Worksheets("Data List").Cells(i, 18).Value))
Do
'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row
'copy the data to the database
ws.Cells(iRow, 2).Value = Me.txtname.Value
ws.Cells(iRow, 4).Value = Me.txtcode.Value
'clear the data
Me.txtname.Value = ""
Me.txtcode.Value = ""
ws = ws + 1
Loop Until ws = Worksheets(Cstr(rng2))
End Sub