I need some help on a 2 parts macro.
The first part of the macro ask the user to input a number (LSearchValue) where it then search for that number in another worksheet (MasterData) and copies the rows matching that number inputted by the user to another Worksheet (PostMortem). All this is done in the active workbook. This part is already working well and you can find the code below.
I need help on the 2nd part. I would like to keep using the number inputted earlier by the user (LSearchValue) in the first part of the macro. I would like the 2nd part of the macro to search for this number in all excel files found in a folder (no need to look at subfolders) until it find a excel Workbook that contains a Worksheet named the same as the number previously inputted by the user. Once it finds it, It would then copy the cell B4 from the closed workbook and paste that value in the active workbook in cells H4; it would also copy cell B8 from the closed workbook and paste that value in the active workbook in cells H6.
As an example to illustrate the 2nd part of the macro, if the user inputted 12345, the macro would search all workbooks found in the folder until it finds a Workbook that has a worksheet name 12345. It would then copy the cell B4 from the closed workbook (Worksheet 12345) and paste that value in the active workbook in cells H4; it would also copy cell B8 from the closed workbook (Worksheet 12345) and paste that value in the active workbook in cells H6.
I have trying to figure out the 2nd part but without success. I am including the code I got so far; I believe the first part that search for closed workbooks is accurate but the part bolded where it specify a worksheet based on LSearchValue and to copy cells is definitely not accurate.
Thank you for your time.
1st part (Working)
Please Login or Register to view this content.
2nd part (Need help on)
Please Login or Register to view this content.
Bookmarks