I've multiple (around 400) protected (with the same password) excel files and each have a common worksheet, named "Survey Summary". I want to copy and append the data of that worksheet (from a specific column to the end of data) from all the workbooks to a new excel workbook. I've used the below VBA code. Unfortunately, it's showing run time error-1004 which shows that "Excel can't access 'TSSR Approved'. The document may be read-only or encrypted."
Please help.
Sub LoopAllExcelFilesInFolder()
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them
'SOURCE: www.TheSpreadsheetGuru.com
Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim lRow As Long
Dim ws2 As Worksheet
Dim y As Workbook
'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'Retrieve Target Folder Path From User
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FldrPicker
.Title = "F:\Nokia_BD\BanglaLink Project\TSSR\TSSR_Approved\Approved TSSR Complete"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With
'In Case of Cancel
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings
'Target File Extension (must include wildcard "*")
myExtension = "*.xlsx*"
'Target Path with Ending Extention
myFile = Dir(myPath & myExtension)
Set y = Workbooks.Open("F:\Nokia_BD\BanglaLink Project\TSSR\TSSR_Approved\Approved TSSR Complete")
Set ws2 = y.Sheets("Survey Summary_Total")
'Loop through each Excel file in folder
Do While myFile <> ""
'Set variable equal to opened workbook
Set wb = Workbooks.Open(Filename:=myPath & myFile)
'Copy data on "Survey Summary" sheet to "Survey Summary_Total" Sheet in other workbook
With wb.Sheets("Survey Summary")
lRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A3:Q" & lRow).Copy ws2.Range("A3" & Rows.Count).End(xlUp)(2)
End With
wb.Close SaveChanges:=True
'Get next file name
myFile = Dir
Loop
'Message Box when tasks are completed
MsgBox "Task Complete!"
ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Thanks Sajib