VBA to Seperate Data and paste into different tabs/files

[phoebeyanan]

I want to create this macro to automate the process of separating the data.

The manual process is filter the data by column A (company code), and paste the filtered data into a new tab. Tab name is the company code.

 

[JoeMo]

Welcome to the forum!

Your post is a bit light on details so I've assumed you have a Co Code header in A1 and the codes are in A2 and down column A with no blank cells between A1 and the last company code in col A.

See if this does what you want.

Sub CompanyCode()
'Assumes company code begins in A2. Run code with the raw data sheet active
Dim DataSht As Worksheet, R As Range, CompanyIDs As Range, ID As Range
Set DataSht = ActiveSheet
Set R = Range("A1").CurrentRegion
Application.ScreenUpdating = False
Set CompanyIDs = Cells(R.Rows.Count + 3, R.Columns.Count + 3)
CompanyIDs.EntireColumn.Insert
R.Columns(1).AdvancedFilter xlFilterCopy, copytorange:=CompanyIDs, unique:=True
Set CompanyIDs = Range(CompanyIDs(2), CompanyIDs.End(xlDown))
For Each ID In CompanyIDs
    R.AutoFilter field:=1, Criteria1:=ID
    If R.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Or R.SpecialCells(xlCellTypeVisible).Areas.Count > 1 Then
        On Error Resume Next
        Application.DisplayAlerts = False
        Sheets(ID.Value).Delete
        Sheets.Add after:=DataSht
        ActiveSheet.Name = ID
        R.Copy Sheets(ID.Value).Range("A1")
        Sheets(ID.Value).Range("A1").CurrentRegion.EntireColumn.AutoFit
    End If
Next ID
CompanyIDs.EntireColumn.Delete
With DataSht
    .AutoFilterMode = False
    .Activate
End With
With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
End With
End Sub

 

[phoebeyanan]

Thank you very much! I was trying to upload a sample excel file, but I didn't find a place that I can upload the file.

In my data sheet, Column A is company code, column B is account number, column C through column M is the data.

I am wondering in the following code, what does the "+3" use for? Thank you!
Set CompanyIDs = Cells(R.Rows.Count + 3, R.Columns.Count + 3)

 

[JoeMo]

Thank you very much! I was trying to upload a sample excel file, but I didn't find a place that I can upload the file.

In my data sheet, Column A is company code, column B is account number, column C through column M is the data.

I am wondering in the following code, what does the "+3" use for? Thank you!
Set CompanyIDs = Cells(R.Rows.Count + 3, R.Columns.Count + 3)

You are welcome.

Since you didn't provide a layout I had to make some assumptions. One of those is that the data are isolated from any other filled cells on the worksheet by at least one empty row and one empty column. If that's true, then the current region property of the data includes all rows & columns holding the data. The +3 ensures that a temporary list of unique company codes is to the right and below the data current region, so filtering of the data has no effect on the list.