VBA macro to loop through all workbook > find keywords in cell > copy adjacent cell > paste it in a master file into respective header

[abtg1]

I have several workbook(.xlsx) that have only 1 sheet per workbook. I would like to loop through all the workbooks in the folder one by one and find certain cells with specific keywords and
copy the cell adjacent to it. Next, copy all the cells and paste it in a master file into its respective column header.

the difficulty here is that for every workbook - the specific keywords I want to find are in different cell. But they are in every workbook.

For anomaly purpose - the keyword I want to find in each workbook are:

Objectives
Conditions
Period
Product
Total

 

[abtg1]

VBA to copy data from multiple workbooks into master sheet

Hello Everyone! I have to copy data from 10+ workbooks and paste it into a master workbook. All the workbooks are located in a folder on my desktop: C:\Users\xbv\Desktop\group1 All the workbooks contain a sheet named 'appendix B', I have to open each workbook, go to sheet 'appendix B’, select...

www.mrexcel.com

I have got some success to loop through all the workbook here - but it will only work if I want to copy and paste cells in specific position.

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Const strPath As String = " C:\Users\xbv\Desktop\group1\"
    ChDir strPath
    strExtension = Dir("*.xls*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            LastRow = .Sheets("appendix B").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("appendix B").Range("C6:F" & LastRow).Copy wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[abtg1]

bumping this post