Run Time Error 1004

[Trevor G]

I have code which outputs data into Excel on to separate sheets, I have also been asked now to give a combined sheet and every now and again I come up against Run Time Error 1004, I believe this is due to copy and pasting to many times, but when I am running the code it is only being run 4 or 5 times at the moment.

I am not sure what to add in to prevent the error from happening.

The extract of code is shown below.

Dim l As Long
'add new sheet at beginning of book of book
xlapp.Worksheets.Add.Name = "Combined"
xlapp.Worksheets("Combined").Move Before:=Worksheets(1)
xlapp.Range("a1").Select
'step through each sheet except Combined
For l = 2 To Worksheets.Count
'Copy heading block
xlapp.Worksheets(l).Select
xlapp.Range("a2:e5").Copy
'paste to Combined sheet (No Formulas in original sheets so can use Pasteall)
xlapp.Worksheets("combined").Select
xlapp.ActiveCell.PasteSpecial xlPasteValues
xlapp.ActiveCell.PasteSpecial xlPasteFormats
xlapp.ActiveCell.End(xlDown).Select
xlapp.ActiveCell.Offset(2, 0).Select
'copy data block
xlapp.Worksheets(l).Select
xlapp.Range("a6").Select
xlapp.Selection.CurrentRegion.Copy
'Paste data block and move activecell ready for next paste
xlapp.Worksheets("combined").Select
xlapp.ActiveCell.PasteSpecial xlPasteValues
xlapp.ActiveCell.PasteSpecial xlPasteFormats
xlapp.ActiveCell.End(xlDown).Select
xlapp.ActiveCell.Offset(2, 0).Select
Next l

 

[DCrake]

Try inserting a DoEvents prior to the Next I to render the task completion before moving to the next one. Maybe it is trying to fly up its own arse.

 

[Trevor G]

Thanks David,

I seem to have missed a couple of xlapp, I have taken a break come back and added them and it seems to work correctly.

Like the comment though.