4

I get this error on the line indicated below. What am I doing wrong?

Run-time error '424' object required

Sub GetSheets()
Path = "C:\Users\vinod\Desktop\dt kte\"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
Sheets("Payout Summary").Select
Sheet.Copy After:=ThisWorkbook.Sheets(1) ' <~~~~ Error occurs here
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
Community
  • 1
  • 1
user1544327
  • 123
  • 1
  • 3
  • 13
  • It will help someone to answer your question if you can give us more information - in what way is it not working? Does it give an error? – BiscuitBaker Dec 18 '14 at 09:54
  • 1
    Run time error '424' object required......here is the error "Sheet.Copy After:=ThisWorkbook.Sheets(1)" – user1544327 Dec 18 '14 at 10:06
  • You appear to be trying to copy worksheet "Payout Summary" from every workbook in folder "C:\Users\vinod\Desktop\dt kte\" to the workbook containing this macro. Am I correct? The result will be worksheets "Payout Summary", "Payout Summary (1)", "Payout Summary (2)" and so on with no indication of the source of each worksheet. I can post some better code if my guess is correct. – Tony Dallimore Dec 18 '14 at 11:04

2 Answers2

3

The error occurs because Sheet is Nothing Empty. You haven't declared any variable Sheet; when you use it for the first time, it defaults to a Variant whose value is Empty.

Change the faulty line to:

ActiveSheet.Copy After:=ThisWorkbook.Sheets(1)

Though really, you should read this: How to avoid using Select in Excel VBA macros

And also use Option Explicit at the top of your modules to force yourself to declare all variables explicitly. Had you done that, you would have been able to find your error much more quickly.

Community
  • 1
  • 1
Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
0

a small improvement on Jean-François Corbett's answer:

replace the lines

Sheets("Payout Summary").Select
Sheet.Copy After:=ThisWorkbook.Sheets(1)

with

Sheets("Payout Summary").Copy After:=ThisWorkbook.Sheets(1)

You do not need to "Select"!!! You, apparently recorded a Macro and are kind of duplicating it. Macros are there to just to look at and improve upon :)

Gene Skuratovsky
  • 591
  • 2
  • 6