Error-438: Object doesn't support this property or method

[zraza]

Can someone advise what is the mistake in this code. I am having error on bold line. It says;

Error-438: Object doesn't support this property or method.

Option Explicit


Sub OpenAndCopy()


Dim FolderPath As String
Dim SourceFile As String
Dim TargetWB As String
Dim SourceWB As Workbook
Dim SourceWS As Worksheet


FolderPath = "C:\Users\admin\Downloads\Test"
SourceFile = Dir(FolderPath & "*.xlsx*")
TargetWB = ActiveWorkbook.Name


Set SourceWB = Workbooks.Open(FolderPath & SourceFile)
Set SourceWS = SourceWB.Worksheets("RRSP")


[B]SourceWS.Range("A:P").Copy_[/B]
Workbooks(TargetWB).Sheets("Sheet1").Range ("A1")


End Sub

 

[RoryA]

Welcome to the forum.

Remove the underscore character on the end of Copy.

PS Please don't post the same question more than once, and start your own thread rather than adding on to an unrelated question. I moved your question to a new thread for you this time (and deleted your duplicate post).

 

[zraza]

Hi Rory,

I tried but it didn't work. It gives the same error again.

Btw sorry about posting it to an older thread as I am new to this form and not acquainted to it.

 

[Michael M]

couple of points

This line will need a backslash after the word Test

FolderPath = "C:\Users\admin\Downloads\Test"

This line doesn't need a wildcard after xlsx and without doing a search in the diectory, you will need to specify the filename, not use a wildcard

SourceFile = Dir(FolderPath & "*.xlsx*")

 

[zraza]

couple of points

This line will need a backslash after the word Test

FolderPath = "C:\Users\admin\Downloads\Test"

This line doesn't need a wildcard after xlsx and without doing a search in the diectory, you will need to specify the filename, not use a wildcard

SourceFile = Dir(FolderPath & "*.xlsx*")

Basically, I want to open 10 to 12 files in a folder and copy data from them into my Active Workbook. I will have to use loop, but since I am new to VBA so I am struggling. This is my code. It would be great if you can help me figure it out.

Option ExplicitSub OpenAndCopy()

Dim FolderPath As String
Dim SourceFile As String
Dim TargetWB As String
Dim SourceWB As Workbook
Dim SourceWS As Worksheet

FolderPath = "P:\Macro\Test"
TargetWB = ActiveWorkbook.Name

SourceFile = Dir(FolderPath & "*.xls*")

Do While SourceFile <> ""

Set SourceWB = Workbooks.Open(FolderPath & SourceFile)
Set SourceWS = SourceWB.Worksheets("JE")

SourceWS.Range("A:P").Copy Workbooks(TargetWB).Sheets("Sheet1").Range("A1")

SourceWB.Close SaveChanges:=False
SourceFile = Dir()

Loop
End Sub

 

[zraza]

My objective is to open 10 to 12 files in a folder one-by-one and copy data from them into another workbook. This is the code that I have written but I am having this error on the bold line of code.

Run-time error 1004:
Method 'Open' of object 'Workbooks' failed.

My code also copies data from first file into the Target workbook, and asks me if I want to re-open my Target workbook.

Any help would be appreciated.

CODE:

Sub OpenAndCopy()
Dim FolderPath As String
Dim SourceFile As String
Dim TargetWB As Workbook
Dim SourceWB As Workbook
Dim SourceWS As Worksheet

FolderPath = "P:\Macro\Test"
Set TargetWB = ActiveWorkbook

SourceFile = Dir(FolderPath & "*.xls*")
Do While SourceFile <> ""

Set SourceWB = Workbooks.Open(FolderPath & SourceFile)
Set SourceWS = SourceWB.Worksheets("JE")

SourceWS.Range("A:P").Copy TargetWB.Sheets("Sheet1").Range("A1")

SourceWB.Close SaveChanges:=False
SourceFile = Dir()

Loop
End Sub

 

[Michael M]

Don't have Excel at the moment, so this is UNTESTED

Sub MM1()
Dim Path As String
Dim SourceFile As String
Dim TargetWB As Workbook
Dim SourceWB As Workbook
Dim SourceWS As Worksheet

Path = "P:\Macro\Test"
Set TargetWB = ActiveWorkbook
SourceFile = Dir(Path & "*.xls*")
Do While SourceFile <> ""
    Set SourceWB = Workbooks.Open(FolderPath & SourceFile)
    Set SourceWS = SourceWB.Worksheets("JE")
    SourceWS.Range("A:P").Copy TargetWB.Sheets("Sheet1").Range("A1")
    SourceWB.Close SaveChanges:=False
    SourceFile = Dir()
Loop
End Sub

 

[Norie]

Where do you want the copied data from each workbook to go?

 

[zraza]

Where do you want the copied data from each workbook to go?

In my target book under different sheets. Data from file 1 under sheet 1, file 2 under sheet 2 till all files in folder are finished.

 

[zraza]

It didn't work and I am having the same error;

Run-time error 1004:
Method 'Open' of object 'Workbooks' failed.

Any suggestions to resolve it?