Trying to get my code to apply to all sheets on a workbook

C

ccarrillo54

New Member
Joined
Nov 29, 2018
Messages
2
Hello, I'm trying to get my code to apply to all the sheets, in the workbook I am using. I've tried multiple ways and multiple codes, but nothing I have used worked. So the macro I'm using allows me to check in and out samples, along with the date right beside it. any other code I use to make the code apply to other sheets ends up in error. Please help me, I will greatly appreciate any help or input you can provide. here I the code I am working with.


Option Explicit

Sub Check_In()
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
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




Here is a preview of what my template looks like, set up in table format.



[TABLE="width: 1449"]
<tbody>[TR]
[TD="width: 215"][TABLE="width: 161"]
<colgroup><col width="215" style="width: 161pt;"> <tbody>[TR]
[TD="width: 215"]CHECKED OUT on its way to analytical lab[/TD]
[/TR]
</tbody>[/TABLE]


[/TD]
[TD="width: 215"]

[TABLE="width: 161"]
<colgroup><col width="215" style="width: 161pt;"> <tbody>[TR]
[TD="width: 215"]DATE
[/TD]
[/TR]
</tbody>[/TABLE]


[/TD]
[TD="width: 215"][TABLE="width: 161"]
<colgroup><col width="215" style="width: 161pt;"> <tbody>[TR]
[TD="width: 215"]SAMPLE RECEIVED from analytical lab[/TD]
[/TR]
</tbody>[/TABLE]


[/TD]
[TD="width: 215"]

[TABLE="width: 161"]
<colgroup><col width="215" style="width: 161pt;"> <tbody>[TR]
[TD="width: 215"]DATE2
[/TD]
[/TR]
</tbody>[/TABLE]


[/TD]
[TD="width: 215, bgcolor: transparent, align: left"]

<tbody>
[TD="bgcolor: transparent"][/TD]

[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 508, bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"][/TD]
[TD="bgcolor: transparent, align: left"]
AVVUu2IXqqz AF0hYFYdwM8uAAAAAElFTkSuQmCC


[/TD]

[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: left"]
CvKt6exFm2CloiTy9CS2iJCC0RoSW0RISWiNASWiJCS0RoCS0RoSUitISWiNASEVpCS0RoiQgtoSUitESE1nOj9U3kucihOhR5PvIfqgvMhgtRBSUAAAAASUVORK5CYII=


[/TD]

[TD="bgcolor: transparent"][/TD]

</tbody>

<tbody>
</tbody>
[/TD]
[TD="width: 215, bgcolor: transparent"][/TD]
[TD="width: 215, bgcolor: transparent"][/TD]
[TD="width: 215, bgcolor: transparent"][/TD]
[TD="width: 215, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-1

[/TD]
[TD]4/17/2019 14:20

[/TD]
[TD]4355-007-110

[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-10
[/TD]
[TD]4/17/2019 14:20
[/TD]
[TD]4355-007-117

[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-11

[/TD]
[TD]4/17/2019 14:20

[/TD]
[TD]4355-007-118
[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-12

[/TD]
[TD]4/17/2019 14:20
[/TD]
[TD]4355-007-119

[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-13

[/TD]
[TD]4/17/2019 14:20
[/TD]
[TD]4355-007-121
[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-14
[/TD]
[TD]4/17/2019 14:20

[/TD]
[TD]4355-007-122
[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-15
[/TD]
[TD]4/17/2019 14:20
[/TD]
[TD]4355-007-123
[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-16
[/TD]
[TD]4/17/2019 14:20
[/TD]
[TD]4355-007-124
[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-17
[/TD]
[TD]4/17/2019 14:20
[/TD]
[TD]4355-007-125
[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-18
[/TD]
[TD]4/17/2019 14:20
[/TD]
[TD]4355-007-126
[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-19
[/TD]
[TD]4/17/2019 14:20
[/TD]
[TD]4355-007-127
[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-20
[/TD]
[TD]4/17/2019 14:20
[/TD]
[TD]4355-007-128
[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-6
[/TD]
[TD]4/17/2019 14:20
[/TD]
[TD]4355-007-113
[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-7
[/TD]
[TD]4/17/2019 14:20
[/TD]
[TD]4355-007-114
[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-8
[/TD]
[TD]4/17/2019 14:20
[/TD]
[TD]4355-007-115
[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-9
[/TD]
[TD]4/17/2019 14:20
[/TD]
[TD]4355-007-116
[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-21
[/TD]
[TD]4/18/2019 12:45
[/TD]
[TD]4355-007-129
[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-22
[/TD]
[TD]4/18/2019 12:45
[/TD]
[TD]4355-007-131
[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-23
[/TD]
[TD]4/18/2019 12:45
[/TD]
[TD]4355-007-132
[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-24
[/TD]
[TD]4/18/2019 12:45
[/TD]
[TD]4355-007-133
[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-25
[/TD]
[TD]4/18/2019 12:45
[/TD]
[TD]4355-007-134
[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-26
[/TD]
[TD]4/18/2019 12:45
[/TD]
[TD]4355-007-135
[/TD]
[TD]4/23/2019 11:30
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-27
[/TD]
[TD]4/18/2019 12:45
[/TD]
[TD]4355-007-137
[/TD]
[TD]

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]4655-003-28
[/TD]
[TD]4/18/2019 12:45
[/TD]
[TD]4355-007-138
[/TD]
[TD]

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
Just wrap the body of your code in a loop that goes through all sheets, like this:
Code: 
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Activate
[COLOR=#ff0000]        'YOUR CODE HERE[/COLOR]
    Next ws
 
Upvote 0
You must log in or register to reply here.

Similar threads

ExcelNewbie2020
  • Question Question
Wraprows for Excel 2019 version
Replies
7
Views
495
ExcelNewbie2020
ExcelNewbie2020
H
  • Solved
Capitalize the last character in column “K” conditionally.
Replies
3
Views
233
Fluff
Fluff
H
  • Solved
Copy rows if cells in column "K" end with either "X" or "x".
Replies
3
Views
235
rollis13
rollis13
O
  • Solved
Macro to extract repeating invoice numbers and details
Replies
4
Views
245
Omar M
O
A
  • Solved
Lookup PAAF ID based on Worker ID and date range
Replies
3
Views
128
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top