asked on

Create a list of Worksheets based on Cell Value

Dear Experts:

i need an VBA to create list of Worksheets based on cell "A31" from Sheet 120 and cell "A29" from Sheet 121. If "Reporting Population: Non-Charged Off Accounts" exist in worksheet then add to the list in "Sheet3".
Please not that i have 121 sheets, i added two sheet for example.

Help is much appreciated.

Thank you very much in advance.

Regards, Bir NA
Book1.xlsx

Shums Faruk

Hi,

Try below, hope you are referring to only those two cells as above A31 & A29.

Sub GetSheetsNames()

Dim xWb As Workbook
Dim xWs As Integer, DataSh As Worksheet

Set xWb = ThisWorkbook
xWs = xWb.Sheets.Count
Set DataSh = xWb.Sheets("Sheet3")
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With

For i = 1 To xWs
    If Sheets(i).Name <> DataSh.Name Then
        If Sheets(i).Cells(31, 1).Value = "Reporting Population: Non-Charged Off Accounts" Or Sheets(i).Cells(29, 1).Value = "Reporting Population: Non-Charged Off Accounts" Then
            DataSh.Range("A" & i) = Sheets(i).Name
        End If
    End If
Next i
DataSh.Activate
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Open in new window

Bir Na

ASKER

HI, thanks for your answer, the VBA works. Unfortunately i have 123 Sheets, just didn't attached entire file.

can we use cell range instead of this: If Sheets(i).Cells(31, 1).Value = "Reporting Population: Non-Charged Off Accounts"

something like Sheets(i).Cells(A29:A31)

Regards, Bir NA

Shums Faruk

Will that be in Column A?

Shums Faruk

Also send me one more sheet without matching cells

Bir Na

ASKER

@Will that be in Column A? - YES

@Also send me one more sheet without matching cells - FILE attached
Book2.xlsx

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bir Na

ASKER

Hi. the VBA works, Thank you very much

now im trying to do the same for CELL D32 value "Not Applicable" (Book1.xlsx) i updated the VBA but no results.

this is the changes i made

For i = 1 To xWs
    If Sheets(i).Name <> DataSh.Name Then
        For d = 26 To 40
            If Sheets(i).Cells(d, 1).Value = "Not Applicable" Then
                DataSh.Range("[D" & i) = Sheets(i).Name
            End If
        Next d

Open in new window

Shums Faruk

LOL.

a doesn't stands for Col A. That was just variable I used.

You want to include both the condition together? If Col A has string "Reporting Population: Non-Charged Off Accounts" & Col D has string "Not Applicable"?

Shums Faruk

If you are trying different condition, change as below:

For i = 1 To xWs
    If Sheets(i).Name <> DataSh.Name Then
        For a = 20 To 40
            If Sheets(i).Cells(a, 4).Value = "Not Applicable" Then
                DataSh.Range("A" & i) = Sheets(i).Name
            End If
        Next a
    End If
Next i

Open in new window

Bir Na

ASKER

I need two:
1. If Col A has string "Reporting Population: Non-Charged Off Accounts" & Col D has string "Not Applicable"
2. Col D has string "Not Applicable" only

Shums Faruk

Hi Bir,

If provided solution worked for you, I would request you to please close this question and open new question if any new changes required.

Bir Na

ASKER

The VBA which have been provided, solved my problem.

Thanks for help

Shums Faruk

Thanks Bir.

Post your new question link here, I will have a look.

Bir Na

ASKER

new question:
https://www.experts-exchange.com/questions/29025337/Create-a-list-of-Worksheets-based-on-Two-Cells-Value.html