Unprotect, unhide sheets in multiple excel files to then append data

Hi all,

I have a weekly task that involves collating 40+ excel files extracting data and appending into a master file. This data is captured in a hidden sheet where I need to first unprotect the workbook before I can unhide the table.

I've tried to record a macro where I open the first file in the folder, unprotect, select and copy out the data. But I get different errors when I run it depending on which excel file I'm in. I think the recorded macro thinks I'm running from the protected workbook.


Sub Test5()
'
' Test5 Macro
'

'
ActiveWorkbook.Unprotect
Sheets("Form").Select
Sheets("DataExtract").Visible = True
Range("A2:I15").Select
Selection.Copy
Windows("Append File.xlsx").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A16").Select
End Sub



I'd like to be able to loop this until all files in the folder have been opened and data extracted.

Any help would be greatly appreciated.


Cheers,

Kal

Maybe something like the below, is the range to be copied always the same size?

Please Login or Register  to view this content.

Hope this helps