I have been using the VBA code for merging multiple worksheets into one sheet given below:

Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A1048576").End(xlUp)(2)
Next
End Sub

Now I need a small help. I have an issue regarding merging worksheets with large data. Let me give you an example. I have four worksheets each having 5 lakh data (rows) so total of 20 lakh data. Now if I am using the given VBA code, its obvious that it will not be going to work out as limit will exceed above 1048576 (.xlsx). So I need to merge all four sheets such as from that four sheets all the data upto 1048576 be utilized by sheet 1 and remaining data get onto sheet 2. So 10 lakh data from sheet 1 and sheet 2 respectively plus 48576 data from sheet 3 all on sheet 1 and remaining data on sheet 2. It will be a great help if you can give me a solution by modifying the given VBA code for that or developing a new one. Oblige.

Thanks