is there a code that can fix my current code?

Question

I've created a check-in / check-out system. my code works perfectly fine when applied to only one sheet. but when I try to apply this code to multiple sheets, I get error, I've tried three codes and still cant get excel to apply the code to each separate sheet. Any code I use, confuses excel and redirects me to the first sheet.

1st code I used

dim WS as worksheet
for each WS in sheets
(my code)
next WS

2nd code I used

For Each Worksheet In ThisWorkbook.worksheets
sheets("may","June","july").activate
(my code)
next worksheet

ive also tried, (worksheet.activate) but this code redirects me to the first sheet and losses the data on other sheets

Here's the current code I'm trying to get to work

Sub Check_in()
For Each Worksheet In ThisWorkbook.worksheets
worksheets("April", "May", "June").Activate
Dim code As String: code = InputBox("Please scan a barcode", "Scan    procedure")
If code = "" Then MsgBox ("No code scanned"): Exit Sub
Dim NbChIn As Integer: NbChIn = application.CountIf(Range("STORE_RECORDS [CHECKED OUT on its way to analytical lab]"), code)
Dim NbChOut As Integer: NbChOut = application.CountIf(Range("STORE_RECORDS[SAMPLE RECEIVED from analytical lab]"), code)


If NbChIn > NbChOut And NbChIn > 0 Then
MsgBox ("This sample is already Checked-out" & Chr(10) & "Please click  sample received and retry"): Exit Sub
Else
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = code
Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = Now
End If
Next Worksheet
End Sub

Sub Check_Out()
Dim code As String: code = InputBox("Please scan a barcode", "Scan procedure")
If code = "" Then MsgBox ("No code scanned"): Exit Sub
Dim NbChIn As Integer: NbChIn = application.CountIf(Range("STORE_RECORDS[CHECKED OUT on its way to analytical lab]"), code)
Dim NbChOut As Integer: NbChOut = application.CountIf(Range("STORE_RECORDS[SAMPLE RECEIVED from analytical lab]"), code)
If NbChIn = NbChOut And NbChIn > 0 Then
    MsgBox ("This sample has already been received" & Chr(10) & "Please check it out and retry"): Exit Sub
Else
    If Range("STORE_RECORDS[CHECKED OUT on its way to analytical lab]").Find(code, , , xlWhole, , xlPrevious) Is Nothing Then MsgBox ("No match, ask Carlos !"): Exit Sub
    Range("STORE_RECORDS[CHECKED OUT on its way to analytical lab]").Find(code, , , xlWhole, , xlPrevious).Offset(0, 2) = code
    Range("STORE_RECORDS[CHECKED OUT on its way to analytical lab]").Find(code, , , xlWhole, , xlPrevious).Offset(0, 3) = Now
End If
End Sub'

code usually says wrong number of arguments, or object doesn't support this property or method. code only works on sheet 1 or in my case (April) I want this code to apply to each sheet, organized by months.

I would recommend extracting the code (that should be applied to each sheet) out into a separate sub with a worksheet parameter. Inside the sub, make sure all ranges are fully qualified (i.e. `worksheetVar.Range("A1")` instead of `Range("A1")`). Then, when cycling through worksheets, check if the worksheet is one that should have the code "applied" (probably through a name or codename check), then call the custom sub, passing in that worksheet. — Mistella, May 15 '19 at 18:52
Also, I would not recommend using `.Select`. If that's required to make the code functional, there's at least one Range (or other sheet-specific object) reference that isn't fully qualified. — Mistella, May 15 '19 at 18:53

is there a code that can fix my current code?

0 Answers0