how to increment by one using do loop until......

[kxlvba]

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

 

[lozzablake]

If you need to loop through every worksheet you would be better off with a for each loop

otherwise set an index counter i which gets incremented by one each time through the loop and refer as:

thisworkbook.sheets(i)

 

[kxlvba]

This won't work because I have worksheets like...

Index, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, ...till 31

Now, the start date would be the start sheet ,i.e. say 5 and the end date would be the last sheet till which data needs to be updated. i.e. say 8.

Thus, if I use thisworkbook.sheets(i) It will blindly start from xth sheet and go till x+n th sheet.

So, if I want data to be updated from 5 to 8 sheets...thisworkbook.sheets will load data from....4 to 7 because Index sheet is the first sheet.

Kindly advice.

 

[lozzablake]

try

For i= rng1 to rng2  'or rng1+1 to rng2+1 cause of index sheet

   with thisworkbook.sheets(i)
         'code here
   end with

next i

 

[kxlvba]

loop

The below mention code only updated the first worksheet, it doesn't updates the same data in the remaining sheets. Can anyone advice on how to make this work. The loop goes through but the data is not inputed, except for the first sheet.

Sub update_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim kctr, rng1, rng2 As Variant

rng1 = Worksheets("Data List").Cells(4, 18).Value
rng2 = Worksheets("Data List").Cells(9, 18).Value

For kctr = rng1 + 1 To rng2 + 1

With ws

Set ws = ThisWorkbook.Sheets(kctr)

'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

'clear the data
Me.txtname.Value = ""

End With

Next kctr

End Sub

 

[kxlvba]

done

no worries, have figured a way out.

Thanks all.