VBA if statement condition met to rely on multiple sheets such as sheet 1 and sheet 2 and so on?

[vbanewbie68]

Hello

Would it be possible to run a macro using an if statement condition met to rely on multiple sheets such as sheet 1 and sheet 2 and even multiple sheets if necessary?

Below this coding section will rely on default on sheet 1 but I need multiple sheets please?

Thank you

Regards

V


Macro
For R = 1 To Cells(10000, 1).End(xlUp).Row
If Left(Cells(R, RefB), 14) = "Company name" Then
If Cells(R, InRE) = "" Then
'Check if date matches to label multiple payment references
If Cells(R, 1).Value = PrevDate Then
ReDim Preserve BankRefs(0 To JGCount)

BankRefs(JGCount) = "Company name" & PrevCount
PrevCount = PrevCount + 1
Else
ReDim Preserve BankRefs(0 To JGCount)
BankRefs(JGCount) = "Company"
PrevDate = "Company"
PrevCount = 2
End If

'Add a string with all required bank statement details for comparison
ReDim Preserve BankRow(0 To JGCount)
BankRow(JGCount) = R & "| " & Cells(R, 1) & " £" & Cells(R, credit)
'Add the corresponding date to its array
ReDim Preserve BankDate(0 To JGCount)
BankDate(JGCount) = Cells(R, 1).Value
'Change position in the arrays
JGCount = JGCount + 1
End If
End If
Next R

 

[gifariz]

Assuming you want to iterate in all sheets that have same cells location,
try adding these lines before and after your current code:

         Dim WS_Count As Integer
         Dim i As Integer

         'Set WS_Count equal to the number of worksheets in the active workbook.
         WS_Count = ActiveWorkbook.Worksheets.Count

         ' Begin the loop.
         For i = 1 To WS_Count

            ActiveWorkbook.Worksheets(i).Activate

            'Insert your code here

         Next i

Source

Macro to Loop Through All Worksheets in a Workbook

support.microsoft.com

 

[vbanewbie68]

Hello,

Thanks for looking into this.

This won't let me to search for "company name"? this usually in sheet 2 and sometime in sheet 3.

Regards

 

[vbanewbie68]

WS_Count = ActiveWorkbook.Worksheets.Count

is this line means it goes to the last worksheet? I need macro to check on sheet 2 and sheet 3 as well please?

 

[gifariz]

In that case, you can write list of desired sheets like this:

         Dim SheetList
         Dim i As Integer

         'Write list of sheets here:
         SheetList = Array("SheetName1","SheetName2")

         ' Begin the loop.
         For i = 0 To UBound(SheetList)

            ActiveWorkbook.Worksheets(SheetList(i)).Activate

            'Insert your code here

         Next i

 

[vbanewbie68]

SheetList = Array(Sheet(1),"SheetName2",SheetName3")

I add this Sheet(1) because it will change again month. For example October 2022 and then next month will be November 2022 and so on. Will this line work?

 

[gifariz]

You can try like this, because how SheetList item is called in the loop

SheetList = Array(1,"SheetName2",SheetName3")

Note that sheet number is not same with sheet order in the spreadsheet window You can see the sheet number in VBA Project window -> Microsoft Excel Objects folder

 

[vbanewbie68]

Thanks for this and can see the sheet number in VBA Project window.

The trouble is that "Sheet2" seems to be a default only rather than to check a value for each tab.

 

[gifariz]

Thanks for this and can see the sheet number in VBA Project window.

The trouble is that "Sheet2" seems to be a default only rather than to check a value for each tab.

Sorry I dont get what you mean in second sentence?

 

[vbanewbie68]

Sorry I dont get what you mean in second sentence?

Hello
I need to check ‘company name’ that could be either in sheet 1, maybe in sheet 2 or maybe in sheet 3. Sheet 1 as you called it a loop is for calendar month. I hope it makes sense?