Application defined or Object error 1004

Hi,

I am getting the above error message on the below code, can anyone suggest a workaround?

Thanks
Seamus
Sub Combine()

'

Dim NumSheets As Integer

Dim NumRows As Integer

 

' Change the value of NumSheets to equal the number of sheets you wish to combine

 

NumSheets = 10

' Change the value of NumRows to equal the number of rows in each sheet

 

NumRows = 500

Worksheets(12).Select

Sheets.Add

ActiveSheet.Name = "Consolidated"

For X = 1 To NumSheets

Worksheets(X + 1).Select

Rows("2:" & NumRows).Select

Selection.Copy

Worksheets("Consolidated").Select

ActiveSheet.Paste

Selection.End(xlDown).Select

ActiveCell.Offset(1, 0).Select

Worksheets(X + 2).Select

Range("A1").Select

Next X

Worksheets("Consolidated").Select

Range("A1").Select

End Sub

Open in new window

Seamus2626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

etech0Commented:
which line of code is the error on? you can find this out by clicking "debug" on the error box, and seeing which line is highlighted.
Also, if you post the file (without any confidential info) we can probably help you better.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
StephenJRCommented:
On which line Seamus?
0
Seamus2626Author Commented:
Debugging on line

ActiveCell.Offset(1, 0).Select

Thanks
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

etech0Commented:
Which cell is selected when the macro stops running with the error message?
0
chwong67Commented:
Suggest to change For loop as follow:

For X = 1 To NumSheets
    Worksheets(X).Rows("2:" & NumRows).Copy Destination:=Worksheets("Consolidated").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Next X
0
Seamus2626Author Commented:
This question was a favour for a friend who wouldnt provide the data behind it, so im going to close it off

Thanks for all your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.