consolidate and copy data from each sheet in a workbook to different workbook

[kshitij_dch]

Hello Folks,

I have a folder in which i have 10 workbooks name(1 ,2 ,3 ,4......10), Each workbook has 10 sheets name (1 ,2 ,3 ,4..........10).

11th Workbook name "Master" has a command button , as soon a user clicks command button , macro loops in each workbook in folder and copy B2 :B27 from each sheet, go in master workbook and transpose in sheet1.

currently i have below code but it only copies sheet1 and not transpose data , what can be done?

Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Const strPath As String = "C:\Users\1256380\Desktop\Sakshi Data\"
    ChDir strPath
    strExtension = Dir("*.xls*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            LastRow = .Sheets("1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("1").Range("B2:B27" & LastRow).Copy wkbDest.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            
            
             LastRow = .Sheets("2").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("2").Range("B2:B27" & LastRow).Copy wkbDest.Sheets("Sheet1").Cells(Rows.Count, "A", xltranspose:=True).End(xlUp).Offset(1, 0)
            .Close savechanges:=True
            
            
            
            
            .Close savechanges:=True
            
            
            
          End With
          
        strExtension = Dir
   Loop
    Application.ScreenUpdating = False

 

[kshitij_dch]

all the worksheets are in same folder

 

[shivani_05]

I have tried using the above code but giving me compile error.