Split the excel data in worksheet to save it in two separate excel files

HI,

hope this help. It is not set up just for 1MIO rows but always split in two files (half and half)

cheers,

Tomas

Sub split()
Dim count_row As Double
Dim count_col As Integer
Dim rng_c As Range
Dim rng_r As Range
Dim split_row As Double
Dim rest_row As Double
Dim thisfile As String
Dim newfile_1 As String
Dim newfile_2 As String

Application.ScreenUpdating = False
thisfile = ActiveWorkbook.Name


Set rng_c = ActiveSheet.Rows(1)
Set rng_r = ActiveSheet.Columns(1)



count_row = Application.WorksheetFunction.CountA(rng_r)
count_col = Application.WorksheetFunction.CountA(rng_c)

split_row = Round(((count_row - 1) / 2), 0)

rest_row = count_row - 1 - split_row


 Range("A1:" & Cells(split_row + 1, count_col).Address(rowabsolute:=False, columnabsolute:=False)).Select
    Selection.Copy
    
Workbooks.Add
    newfile_1 = ActiveWorkbook.Name
  
        ActiveSheet.Paste
         
         Application.CutCopyMode = False
     

  
Workbooks(thisfile).Activate
    Range("A1:" & Cells(1, count_col).Address(rowabsolute:=False, columnabsolute:=False)).Select
    Selection.Copy
    
Workbooks.Add
    newfile_2 = ActiveWorkbook.Name
ActiveSheet.Paste

Workbooks(thisfile).Activate
Range("A" & split_row + 2 & ":" & Cells(split_row + rest_row + 2, count_col).Address(rowabsolute:=False, columnabsolute:=False)).Select
Selection.Copy
Workbooks(newfile_2).Activate
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Application.ScreenUpdating = True

    
       
        


End Sub

Awesome Tomas. I was expecting the same. I can manage by this code... Thanks for your help

Hi Kaper,

Apologies for inconvenience caused, I am new to forums and I am not aware of this. I have posted the same question simultaneously ....

Hi All,

I need some little advanced VBA code for my task.

Query:

There are two excel sheets one with greater than 500,000 rows, 52 columns(consider WB1.xlsx) and less than 500,000 rows, 52 columns(consider WB2.xlsx).

I need one VBA code such a way that all the columns in the workbooks should convert to text, Paste Special all the rows and columns and autofit itself.

But the condition is,
*If we are using WB1.xlsx the macro should do the above task and split into half, save in the some directory which i specify the path with some different names.

*If we are using WB2.xlsx the macro should do the above mentioned task and save in the some directory which I specify the path. No need of splitting the workbook.

Please Help!!

Hi Friends,

any luck on my query.

Originally Posted by Kaper

Hi sat1591,
First, it is now quite different question than the one from title.
Second, you split the main file into two by some macro - natural way to do the same steps will be upgrading this macro.

Having both in mind, my suggestion is that you start a new threas arrach small sample workbook with raw data, and the macro code which you already have. Also give there a link acknowledging source of the macro.

Hope it helps.

Hi Kaper,

Thanks for ur suggestion. I will create a new thread and provide the sample's as requested