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.

In general you want to avoid `Activate`. Even though [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) is mainly about avoiding `Select`, give it a read, especially the parts about being *explicit* about the worksheet and workbook. — BigBen, Nov 18 '19 at 21:35
i already tried writing the exact path i need, it's just make the code to crash — Roy Shiff, Nov 18 '19 at 21:51
You shouldn't need to write the path except when you `Open` the workbook. — BigBen, Nov 18 '19 at 21:51
Nothing is ever going to be `Null` in Excel, for one. Finding the last row doesn't need a loop: you want `Line = ActiveWorkbook.Worksheets("Cases").Range("B" & Rows.Count).End(xlUp).Row + 1`. — Mathieu Guindon, Nov 18 '19 at 21:53

score 1 · Accepted Answer · answered Nov 18 '19 at 21:58

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)

that works exactly as i wanted, thank you very much. now i just try to figure out how on the other VBA cod it did work the way i tried here — Roy Shiff, Nov 20 '19 at 21:17

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

1 Answers1