VB Code - Merge 5 sheets create Run-time error 1004

[NeedHelp11]

Dear VB-experts,

I have 5 worksheets that are updated regularly in a folder and then brought into a new summary workbook using vb code. This works all fine.

My problem is when I try to merge the 5 sheets to one summary sheet in the summary workbook using vb code.

Code:
Sub Step_5()
Dim ws As Worksheet
ActiveSheet.UsedRange.Offset(0).Clear
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> ActiveSheet.Name Then
ws.UsedRange.Offset(1).Copy
With Range("A65536").End(xlUp).Offset(1, 0)
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
End If
Next
End Sub

When I run the code I get error message 1004 "application-defined or object-defined error".
The first four sheets are copied fine, but the last one is not copied at all.
The problem showed when debugging is marked in red.

I have searched for this in different threads. One solution could maybe be to copy one sheet, save, close the workbook then copy the second sheet, save, close the workbook etc? I am a beginner in VB and I really do not know how to solve this run-time error.

I would be very grateful for any solutions!

 

[baitmaster]

welcome to the board

Its difficult to tell what is causing your problem from looking at this code

Initial thoughts, which may be well wide of the mark:
is that sheet protected?
how big is the usedrange / what region does it cover? How big is the combination of all usedranges on all sheets?

Do you know how to name your ranges, and use these names in your code? (insert>name>define in xl03, formulas>name manager in xl07)
example VBA

e.g. range("mynamedrange").copy

can this work for you?