VBA sheets.activate will not change the sheet it takes information from

Question

I am trying to change worksheet during run-time so I wrote this code in VBA:

 Dim Item As Variant
 Dim Info As Variant
 Dim Stat As Integer
 Stat = 2
 Dim Line As Integer
 Line = 1
 Item = "Cases"
 Sheets(Item).Activate
 Do
        Line = Line + 1
 Loop While Cells(Line, "B") <> Null
 Do
     Info = Cells(1, Stat)
     Info = InputBox("what is the item " & Info & "?")
     Cells(Line, Stat) = Info
 Loop While Cells(1, Stat) <> Null

the activate function does open the worksheet named "Cases" just like I wanted, but when this part runs "Info = Cells(1, Stat)" it still takes the information from the last worksheet even though I see the "Cases" worksheet.

I looked in another vba code I wrote which I used the same method, it worked there but I couldn't find anything different I did there that could make it work there and was missing here.

Answer 1

Pull the sheet into its own local variable, you don't need to Activate anything (if you have a Workbook object, use it in place of ActiveWorkbook to qualify the Worksheets call).

Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Cases")

Now get the last/next row without looping:

Dim line As Long
line = ws.Range("B" & ws.Rows.Count).End(xlUp).Row + 1

Now, your loop condition isn't going to work, because nothing in Excel is ever going to be Null. Rather, look for Empty cells, and use the ws variable to qualify every single Cells member call:

Do
    Info = ws.Cells(1, Stat).Value
    Info = InputBox("what is the item " & Info & "?")
    ws.Cells(Line, Stat).Value = Info
Loop While Not IsEmpty(ws.Cells(1, Stat).Value)