Run-Time error 438 - Object doesn't support this property or method

[ConorMoran1]

Hi Everyone

I am very new to VBA to please go easy...

I am at the tail end of a project where I need a command button to copy data from another workbook into my workbook based on a filepath that can be entered in cell D4.

The reason I cannot put the filepath name in the VBA code is due to the fact the files name will change depending on the date.

I have come up with this VBA however I am getting an an error saying "Run-Time error 438 - Object doesn't support this property or method".

Can anyone shed any light as to where I am going wrong?

Thanks in advance.




Sub CommandButton21_Click()
Dim filepath As String
Dim x As Workbook 4
Dim Y As Workbook
filepath = ActiveSheet.Active("D4").Value
Set x = Workbooks.Open(filepath)
Set Y = Workbooks.Open("S:\ICC.Restored.20140103\# IBC #\New Build Team")
x.Sheets("Page1_1").Range("A6:U21000").Copy
Y.Sheets("Monday").Range("A2903").PasteSpecial
'close x:
x.Close
End Sub

 

[ConorMoran1]

Just an update I have worked out the issues however I am now getting a read only error from this. Does anyone have any idea how to get around this?

I am getting an error saying "excel cannot access 'myfolder' document may be read only or encrypted".

Code is below

Sub CommandButton21_Click()

Dim x As Workbook
Dim Y As Workbook
Dim Fpath As String
Fpath = Range("D4")
Set x = Workbooks.Open(Fpath, ReadOnly:=True)
Set Y = Workbooks.Open("S:\ICC.Restored.20140103\# IBC #\New Build Team")
x.Sheets("Page1_1").Range("A6:U21000").Copy
Y.Sheets("Monday").Range("A2903").PasteSpecial
'close x:
x.Close
End Sub