Split excel data into multiple sheets based on Employee ID

**Preeti1309** · 04-19-2014, 12:30 AM

Hello Every One!!!

My requirement is to split my excel data into multiple sheets based on Employee ID cell using macros. I have attached excel sheet with test data for more clarification. The new sheets generated should be named as Employee ID - 111011, Employee ID - 11102 and so on and should have all the data for that employee in it.

Thanks in advance

**mahaveersomani** · 04-19-2014, 01:13 AM

message deleted due to some reason.

**HaHoBe** · 04-19-2014, 01:18 AM

Hi, Preeti1309,

did you try to search for this action? You should have found lots of examples for this kind of request.

https://sites.google.com/a/madrocket...eet1-to-sheets

Ciao,
Holger

**Preeti1309** · 04-19-2014, 01:50 AM

Hi Hahobe!!

I browsed many threads to track such scenario. I found that the requirements were to split a master sheet into multiple sheets on the basis of particular column. In my case Column A contains Employee ID, Employee Name, Address. I need to split the excel sheet on the basis of employee ID. And every sub sheet should contain all the values for that employee ID i.e Employee ID, Employee Name, Employee Grade, Address and base Location. Also I need that every tab should be renamed as Employee ID - 111011, Employee ID - 111002 and so on... I hope I am able to clarify my requirement.

Regards,
Preeti1309

**kvsrinivasamurthy** · 04-19-2014, 05:32 AM

Pl see attached file with macro.

**Preeti1309** · 04-19-2014, 07:32 AM

Hi Sinivas!!!

Thanks a ton!! The script works.. The only issue is..Is there any way to retain the original formatting....?

Regards,
Preeti

**mahaveersomani** · 04-19-2014, 07:45 AM

hey preeti..!!!

use the following code and enjoy.

HTML Code:

Thanks & Regards
CA Mahaveer Somani

**mahaveersomani** · 04-19-2014, 07:48 AM

or u can check the attached file.

**HaHoBe** · 04-19-2014, 08:02 AM

Hi, mahaveersomani,

your code should fail in the sample file at Row1 as the entry there is Emplyee ID 111011 - your code assumes Employee ID 111011.

Sub SplitData_Mod()

Dim lngRC As Long

lngRC = 1

With Sheets("Sheet1")
  Do While .Cells(lngRC, "A").Value <> ""
    If Not Evaluate("ISREF('" & .Cells(lngRC, "A").Value & "'!A1)") Then
      Sheets.Add after:=Worksheets(Sheets.Count)
      ActiveSheet.Name = .Cells(lngRC, "A").Value
      .Cells(lngRC, "A").CurrentRegion.Copy ActiveSheet.Range("A1")
      ActiveSheet.Cells.EntireColumn.AutoFit
    End If
    lngRC = .Cells(lngRC, "A").End(xlDown).Row + 2
  Loop
End With

End Sub

Ciao,
Holger

**Preeti1309** · 04-19-2014, 08:44 AM

Hi Mahaveer!!

Thanks a lot for helping me out here.. Macro works on my test data...

Regards,
Preeti

**mahaveersomani** · 04-21-2014, 02:33 AM

hey hahobe,

I assumed that the word "Emplyee ID 111011" is written by mistaken...and i think i m right.

Regards
Veer

Split excel data into multiple sheets based on Employee ID

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Split excel data into multiple sheets based on Employee ID

Re: Split excel data into multiple sheets based on Employee ID

Re: Split excel data into multiple sheets based on Employee ID

Re: Split excel data into multiple sheets based on Employee ID

Re: Split excel data into multiple sheets based on Employee ID

Re: Split excel data into multiple sheets based on Employee ID

Re: Split excel data into multiple sheets based on Employee ID

Re: Split excel data into multiple sheets based on Employee ID

Re: Split excel data into multiple sheets based on Employee ID

Re: Split excel data into multiple sheets based on Employee ID

Re: Split excel data into multiple sheets based on Employee ID

Thread Information

Users Browsing this Thread

Similar Threads

Split into Multiple sheets based on beginning string in a set column - Code help

[SOLVED] Split Data from one sheet into multiple sheets

[SOLVED] Split data in one worksheets into multiple sheets?

Split Data to multiple Sheets

[SOLVED] Excel 2002 - How do I split a .xls file into multiple files based on data in a column

Bookmarks

Bookmarks

Posting Permissions