I have been using the following code to combine sheets from workbook
saved in a folder called "MyData" andit has been working fine.

However today i am being told that "Method 'Copy' of object 'Sheets
failed" and the lines

wkbk.Worksheets.Copy After:=ThisWorkbook. _
Worksheets(ThisWorkbook.Worksheets.Count)

are highlighted as the problem. I can't figure out whats going on an
how to fix it. Can anyone help please (I have included the complet
code below...)


Sub GetSheets()
Dim sPath As String, i As Long
Dim varr As Variant
Dim wkbk As Workbook
sPath = "C:\MyData\"
varr = Array("Data1.xls", "Data2.xls", "Data3.xls")
For i = LBound(varr) To UBound(varr)
Set wkbk = Workbooks.Open(sPath & varr(i))
wkbk.Worksheets.Copy After:=ThisWorkbook. _
Worksheets(ThisWorkbook.Worksheets.Count)
wkbk.Close SaveChanges:=False
Next
End Su

--
Message posted from http://www.ExcelForum.com

If it worked before and you haven't changed anything, then generally, the
code doesn't need tweaking. What has changed? How many sheets are in the
the workbook containing the code (where you are copying/placing the sheets
from Data1, Data2 and Data3?

What version of Excel are you using?

If you place the code in a new workbook and run it, does it work again?

--
Regards,
Tom Ogilvy

ian123 wrote in message
...
I have been using the following code to combine sheets from workbooks
saved in a folder called "MyData" andit has been working fine.

However today i am being told that "Method 'Copy' of object 'Sheets'
failed" and the lines

wkbk.Worksheets.Copy After:=ThisWorkbook. _
Worksheets(ThisWorkbook.Worksheets.Count)

are highlighted as the problem. I can't figure out whats going on and
how to fix it. Can anyone help please (I have included the complete
code below...)


Sub GetSheets()
Dim sPath As String, i As Long
Dim varr As Variant
Dim wkbk As Workbook
sPath = "C:\MyData\"
varr = Array("Data1.xls", "Data2.xls", "Data3.xls")
For i = LBound(varr) To UBound(varr)
Set wkbk = Workbooks.Open(sPath & varr(i))
wkbk.Worksheets.Copy After:=ThisWorkbook. _
Worksheets(ThisWorkbook.Worksheets.Count)
wkbk.Close SaveChanges:=False
Next
End Sub


---
Message posted from http://www.ExcelForum.com/

Tom,

Thanks for the suggestions - i cut and paste the formula into a ne
workbook and new it works fine without any 'tweaking'.

I'm bamboozled as to why it didn't work in the first book but at leas
its fixed now! Many thank

--
Message posted from http://www.ExcelForum.com

Tom,

Still having problems...

If i copy the code, delete the macro from personal.xls, open a ne
workbook, right click on sheet1 name tag and paste the code into th
view code sheet - everything works fine.

If i save the code into personal.xls, and attempt to run from there
am getting the run time error outlined above.

Any idea how i can fix this - i need to save this in personal.xls

Many thanks for your time and consideratio

--
Message posted from http://www.ExcelForum.com

If you put the code in personal.xls, it will try to copy the sheets from the
other workbooks into personal.xls. I doubt that is what you want. Where do
you want the sheets copied?

This will copy the sheet to the activeworkbook at the time the macro is run.

Sub GetSheets()
Dim sPath As String, i As Long
Dim varr As Variant
Dim wkbk As Workbook
Dim wkbk1 as Workbook
set wkbk1 = Activeworkbook
sPath = "C:\MyData\"
varr = Array("Data1.xls", "Data2.xls", "Data3.xls")
For i = LBound(varr) To UBound(varr)
Set wkbk = Workbooks.Open(sPath & varr(i))
wkbk.Worksheets.Copy After:=wkbk1. _
Worksheets(wkbk1.Worksheets.Count)
wkbk.Close SaveChanges:=False
Next
End Sub




--
Regards,
Tom Ogilvy

ian123 wrote in message
...
Tom,

Still having problems...

If i copy the code, delete the macro from personal.xls, open a new
workbook, right click on sheet1 name tag and paste the code into the
view code sheet - everything works fine.

If i save the code into personal.xls, and attempt to run from there i
am getting the run time error outlined above.

Any idea how i can fix this - i need to save this in personal.xls

Many thanks for your time and consideration


---
Message posted from http://www.ExcelForum.com/

Tom,

What you describe in your latest response is exactly what i require -
however on running the macro i am told "Run time error '424': Object
Required"

Again the same two problem lines are highlighted!

I'm figuring that this is easily solved - can you explain how please?

Many thanks


---
Message posted from http://www.ExcelForum.com/