Hi All,

I am having some trouble with a bit of code I have adapted from various sources.
I have designed the code to run from a control sheet in my master workbook. The code is intended to open workbooks in a directory, the address of the directory is defined in a user specified cell in the control worksheet.
The intention of the code is to run through each of the workbooks in the directory, copy information from each of the workbooks successively and paste into a different worksheet ("Dataref") in the master workbook.
Once the information has been copied into "Dataref" I would like to duplicate the worksheet from the open workbook into the master workbook and also call a module which I have designed to transform my data.

Here is the code I have at the moment, however I keep getting a "Runtime 424 Object Required" error, which I can't seem to resolve.
I should mention here that the code is being run through a button ActiveX Control.
Can anyone help?

Public Sub CommandButton1_Click()
'DECLARE AND SET VARIABLES
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Dim Worksheet As Object
Workbooks("batchtrial").Activate
With Sheets("Control")
.Visible = True
.Activate

Path = ActiveSheet.Range("a4") 'CHANGE PATH
Filename = Dir(Path & "*.xlsx")
End With
'.........................................
'OPEN EXCEL FILES

Do While Len(Filename) > 0 'IF NEXT FILE EXISTS THEN
Set wbk = Workbooks.Open(Path & Filename)
With wbk
ActiveSheet.Range("a1:K32").Copy
End With

Workbooks("batchtrial").Activate
Worksheets("DataRef").Activate

With ActiveSheet.Range("A1:K32").PasteSpecial.xlValues
End With

MsgBox Filename & " has been copied" 'OPTIONAL- CAN COMMENT OUT
wbk.Close True
Filename = Dir

Workbooks("batchtrial").Activate
With Sheets("Control")
.Visible = True
.Activate
End With
Loop
End Sub


Thanks in advance.
My apologies for the poor indentation.