[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

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

0
Seamus2626
Asked:
Seamus2626
3 Solutions
 
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
 
StephenJRCommented:
On which line Seamus?
0
 
Seamus2626Author Commented:
Debugging on line

ActiveCell.Offset(1, 0).Select

Thanks
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now