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

[Winterkid]

Hi All, thank you for taking your time to see this message. I hope it make sense.

i need help in create a VBA/Macro for searching data and compiling data.
i have 2 excel, Excel Test is where all the monthly report and sample consolidation report is 

From Excel Test
SheetName PL_CompanyA, BS_CompanyA, PL_CompanyB, BS_CompanyB, PL_CompanyC, BS_Company C.
Each Sheet contain table as below not limited to the table A.
For PLSheet it contain such information

for each month 2023, every month when the report generated, the New month will be added.
For example, Feb 2023 is the latest month.
For this Excel,in PL_Company A, i will need to search for the latest month follow by Sales (Column A / Row 3) , then i will need to get the amount from (Column C / Row 3).
Next i will need to search for   Cost of sales (Column A / Row 6) , then i will need to get the amount 6.00 from (Column C / Row 6).
There will be alot of search in Column A for getting the different amount in each month and i will need to do for the same for PL_Company B and PL_Company C Sheets.
Some have additional information to search from Column A. i believe i can modify once it.

For BS sheet will contain such information. I believe i can do it once i know how to search and get the data from PL sheet.

2nd Excel sample consolidation report
2023_PL sheet,it contain table like this.

From the data gotten from Excel Test PL sheet, i need to put them into here.
I will need to search for Company A, follow by the month latest month.

For example from the Excel Test the latest month is Feb, so i need to search for 2/28/2023.
then from Sales that is the amount 6.00 from (Column C / Row 6) in in Excel Test PL Sheet, I will need to put 6.00 into this 2023_PL sheet Column C Row 4.
and this goes on for the rest.

In my testing excel, i have done up to this code, which i am planning to get into the consolidation report excel.

Sub Get_Data_From_File()
    Dim FileToOpen As Variant
    Dim OpenBook As Workbook

    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)
    OpenBook.Sheets(1).Range("A5:D55").Copy
    ThisWorkbook.Worksheets("Sheet1").Range("A5").PasteSpecial xlPasteValues
    OpenBook.Close False
    
    End If
    Application.ScreenUpdating = True
    
End Sub

Thank you And many thank for taking your time to read and understand.
please do reach out to me if you need to explaining.

[#2]

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!