Many WorkBooks to Summary WB

[vinayguj]

Hello to one in this wonderful forum!

I need some help on a Macro.

I have a folder with many Excel WorkBooks in it. All the WBs are encrypted by the same password.

The data in these WBs are in Sheet1, in the Column C5:C10.

The Macro should open each WB in the Folder and copy the data from C5:C10 and paste it in a Summary File in a Transpose manner, starting from Row 2. The data from each and every WB should be below each other, I mean; Row 3, Row 4......

As the Password for opening all the WBs are same, the Macro should open the WBs with the same password and it should not promt for password everytime it opens a file.

Any help would be appreciated.

 

[Tinbendr]

See if this is what you're looking for,

 

[b.downey]

You will need to supply the Passwod and Path in the Two constants at the start of the SUB. Otherwise, this code should work per you requirements

Function Process()
    Const pswd As String = "DDD"
    Const InitPath As String = "C:\Documents and Settings\cpelab\My Documents"
    Dim FilePath As String
    Dim WbSrc As Workbook
    Dim RngSrc As Range
    Dim I As Integer
    
    Dim WsDst As Worksheet
    Dim DstRowNo As Long
    
    Set WsDst = ThisWorkbook.Worksheets(1)
    DstRowNo = 2
    ChDir InitPath
    FilePath = Dir("*.xls")
    
    Do While FilePath <> ""
        Debug.Print FilePath
        Set WbSrc = Workbooks.Open(Filename:=FilePath, ReadOnly:=True, Password:=pswd)
        
        Set RngSrc = WbSrc.Worksheets(1).Range("C5:C10")
        
        For I = 1 To RngSrc.Rows.Count
            WsDst.Cells(DstRowNo, I) = RngSrc.Cells(I, 1)
        Next I
        
        Call WbSrc.Close(SaveChangeS:=False)
        DstRowNo = DstRowNo + 1
        FilePath = Dir
    Loop
End Function

 

[vinayguj]

Thanks for link Tinbendr. It helped me.

 

[vinayguj]

Hello Downey. Your codes work perfectly as desired. Thank you very much for your time and help.

 

[b.downey]

No problem. Thanks for the feedback.