I need some help in my excel i want to be dyanmic read since my sheet now is dynamic where i wouldnt know the name as this will change everytime i wan to find in excel this column name or row name call Cost Price or Project ID then copy the value below it into a new excel output but i meet a difficulty where in my 2nd sheet this cost centre is not join together
how am i able to do it ? Any help is approciated

The instruction is like this
1)Use Excel application Scope
2)Get Workbook sheet
3)For each loop through each sheet on the workbook sheet
4)For each loop loop through column a and b and loot through first row to last non empty row to look for Cost Price if not found move on the next sheet
5)start from cell contain Cost Price check for next column to see if is Project ID

I have attached an example test price excel Test Price.xlsx (10.4 KB)

Hi @xxGoRpa ,
you want to find index in sheet 2nd, that’s right
We can get sheet and index of columns by index too
alphabet can convert o number…
we can read all then use for each then find index of it
regards,

can give example of the xaml so i can take a look like how to do
my final output is it write range to an new excel with the sheet name but it will display the Cost Price and Project ID if there is one

For Each sheet In sheetNames
Read Range from sheet → Output: dtCurrentSheet
For Each row In dtCurrentSheet.Rows
If row("A").ToString.Contains("Cost Price") OrElse row("B").ToString.Contains("Project ID") Then
' Perform actions to store/copy the values you need
' Use 'dtCurrentSheet.Rows.IndexOf(row) + 1' to get the next row index
' Retrieve values using dtCurrentSheet.Rows(nextRowIndex)("ColumnNameOrIndex").ToString
' Store these values in variables or a collection
' Exit the loop if necessary, using 'Exit For'
End If
Next row
' Use conditional logic here to check if you have found the values
' If found, exit the outer loop to stop processing further sheets
Next sheet

’ Outside of the loops
’ Write the stored values to a new Excel file using Write Range or Write Cell