need help on making a VBA/Macro script for searching and compiling data.

I understand you’re looking to automate the process of extracting data from multiple sheets in an Excel workbook and consolidating it into another workbook.
Here’s a basic example of how you could modify your existing VBA code to achieve this:

Sub Get_Data_From_File()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim ws As Worksheet
Dim searchRange As Range
Dim foundRange As Range

Application.ScreenUpdating = False

FileToOpen = Application.GetOpenFilename(Title:="Browse for excel file", FileFilter:="Excel Files(*.xlsx*),*xlsx*", MultiSelect:=False)
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen, ReadOnly:=True)
For Each ws In OpenBook.Worksheets
If ws.Name Like "PL_*" Then ' Only process worksheets starting with "PL_"
Set searchRange = ws.Range("A:A") ' Define the range to search in
' Find the latest month and sales value
Set foundRange = searchRange.Find(What:="Sales", LookIn:=xlValues, LookAt:=xlWhole)
If Not foundRange Is Nothing Then
ThisWorkbook.Worksheets("Sheet1").Range("C4").Value = foundRange.Offset(0, 2).Value ' Copy the sales value to your consolidation sheet
End If
End If
Next ws
OpenBook.Close False
End If

Application.ScreenUpdating = True
End Sub

This code will open the selected workbook, loop through each worksheet, and if the worksheet name starts with “PL_”, it will find the “Sales” value and copy it to your consolidation sheet.
You can add more searchRange.Find and Offset lines to find and copy more values.

Please note that this is a basic example and might need to be adjusted to fit your specific needs.
Always make sure to back up your data before running any new code123. If you’re new to VBA,
I recommend learning more about the Find method and Offset property, as they’re very useful for tasks like this. Let me know if you have any questions!