Combining Several Worksheet into one

[alegria4ever]

I have over 30 excel worksheets that are:
1. Password protected
2. The sheet is also password protected
3. Each worksheet contains only one tab call "All" and this tab is in
the same format and contains the same column in every sheet.
4. It's located in the same folder

I need to write a macro that will open all these workbook in this
folder and combine the data into one new sheet with only one tab called
"All". I am able to write the code to open all the workbook but am
having a difficult time figuring out how to copy only the cells with
data into the new workbook and then do the same with all 30 workbook.

I know I have seen this code floating around before but I am having a
hard time finding the right one that fits my needs. If anyone can
provide some help, I'd appreciate it.

Thank you in advance.

 

[Roger Govier]

Hi

Take a look at Ron de Bruin's site. There is lots of code there that can
be adapted to what you want.

 

[alegria4ever]

Thanks Roger!

I have modified the code but it still is not working right, it only
copies the data on the first workbook it opens and then it doesn't do
anything to the other workbook. Can you tell me why the following code
isn't working?

Sub OpenAllWorkbooksInFolder()
Application.EnableEvents = False
Application.ScreenUpdating = False

Dim i As Integer
Dim MyPath As String
Dim SourceRcount As Long
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim Fnum As Long
Dim mybook As Workbook
Dim basebook As Workbook

Set basebook = ThisWorkbook
MyPath = "C:\Test\"
rnum = 12

With Application.FileSearch
.LookIn = MyPath
'* represents wildcard characters
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then 'Workbook exists
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i),
Password:="password")
Sheets("All").Unprotect Password:="password"
Range("A11:X11").AutoFilter
Columns("B:X").EntireColumn.Hidden = False
Set sourceRange =
mybook.Worksheets("All").Range("B12:X" &
ActiveCell.SpecialCells(xlCellTypeLastCell).Row)
SourceRcount = sourceRange.Rows.Count
Set destrange =
basebook.Worksheets("Combined").Range("B" & rnum)
' This will add the workbook name in column Y if you
want
basebook.Worksheets("Combined").Cells(rnum, "Y").Value
= mybook.Name
With sourceRange
Set destrange =
basebook.Worksheets("Combined").Cells(rnum, "B"). _

Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
rnum = rnum + SourceRcount
mybook.Close savechanges:=False
Next i
Else 'There is NOT a Workbook
MsgBox "The Workbook does not exist"
End If
End With
Application.EnableEvents = True
Application.