split data from one sheet to multiple workbooks

I

I R

New member
Joined
Apr 28, 2015
Messages
1
Reaction score
0
Points
0
Hi,

I have a data which run in millions and wants to split based on the uniqueness of the data listed in column A. The Column A has unique names like Phase1, phase 2, phase 3 etc., based on this the data has to be copied of each phase to different file and rename it Phase 1 or Phase 2 of the unique identifier.

There are two files one is which contains this data and another one which is the template where data needs to be populated and saved to specific path.

Template path
For Ex: I have the template in C://users/data/template.xlsx

The files after copying and renaming has to save in C://users/data/rawdata

Need help in doing this and appreciate your help in this regard.
 

Attachments

  • example sheet.xlsx
    9.4 KB · Views: 17
Code: 
Public Sub Splitdata()Dim this As Worksheet
Dim ws As Worksheet
Dim firstrow As Long
Dim lastrow As Long
Dim numrows As Long


    Application.ScreenUpdating = False


    Set this = ActiveSheet
    
    With this
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        Do
        
            firstrow = .Columns("A").Find(.Cells(lastrow, "A").Value, after:=.Range("A1")).Row
            Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
            .Rows(firstrow).Resize(lastrow - firstrow + 1).Copy ws.Range("A2")
            .Rows(1).Copy ws.Range("A1")
            ws.Name = .Cells(lastrow, "A").Value
            .Rows(firstrow).Resize(lastrow - firstrow + 1).Delete
            lastrow = firstrow - 1
        Loop Until lastrow < 2
    End With
    
    Application.ScreenUpdating = True
End Sub
 
You must log in or register to reply here.
Back
Top