Split a Worksheet into Multiple Worksheets by a name

Darker1973 · Jun 4, 2014

Hi,
I have a rather large company salary review tool cols A - BQ which I have built and needs to be split by Reviewer (col L).
The document has over 4869 lines of data but I want to keep all other functionality ie macro buttons and so on.
Basically it should be a copy worksheet, and then cut by reviewer. Can anyone help me with something, somehow to do this with some code? cheers

-- removed inline image ---

-- removed inline image ---

-- removed inline image ---

-- removed inline image ---

-- removed inline image ---

Total Budget

£ -

Total Spend To Date

£ -

Target Incentives not Actual Award to Compare Opportunity of Incentives Year on Year

Remaining Spend

£ -

ID

HRD

Payroll

Surname

First Name

BU-1

LPS Business

Business Name

Unit

Organization

Supervisor

1st Level
Salary Reviewer

2nd Level
Salary Reviewer

Actual No. Hours worked

Start Date

Job Title

Career Grade Name

Career Level

Job Classification

2013 Performance Rating

2014 Performance Rating

Salary before
Last Change £

Last
Salary Change Date

Salary
% Change

Current Salary
£

INPUT
Promotion wef 1-7-2014 Y/N

PLEASE CHECK
New Grade wef 1-7-2014

TOWERS MATCHING CODE

LQ
Salary

Med
Salary

UQ
Salary

LQ
Salary - 2013 Aged Data

Med
Salary - 2013 Aged Data

UQ
Salary - 2013 Aged Data

LQ
Target Total Comp

Med
Target Total Comp

UQ
Target Total Comp

LQ
Target Total Comp - 2013 Aged Data

Med
Target Total Comp- 2013 Aged Data

UQ
Target Total Comp- 2013 Aged Data

Current Car Allowance
£

New Car Allowance July
Y/N

Car Allowance
1-7-2014

Increase Car Allowance July
£

Current Full time
Annual Salary
£

2013 Salary Market Data Positioning
If Available

INPUT
Salary Increase July 2014
£

INPUT
Salary Increase July 2014
%

New Salary July 2014
£

Salary Inc
%

New Salary Market Data Positioning
If Available

INPUT
Discretionary Bonus July 2014
£

Salary & Discretionary Bonus Spend 2014
£

2013
PPP / Sales Target %

2013
PPP / Sales Target £

2014
PPP Target %

2014
PPP Target
£

2014 Sales Target
%

2014 Sales Target
£

2013 Long Term Incentive
£

2014 Target Long Term Incentive
£

Previous FTE Target Total Comp (sal+car+
bonus+LTI)

New FTE Target Total Comp (sal+car+PPP/Sales+LTI)

Change in Target Total Comp %

o

NEW Total Target Comp Positioning

Reward/HR Comment

Manager / HR Comment

14896

TEST

Surname

First Name

BU-1

LPS Business

Business Name

Unit

Organization

Supervisor

Robert Parker

37.5

04-Jan-2000

Job Title

Career Grade Name

Level 5

Job Classification

3 - Meets expectations

£ 100

01/07/2013

0.00%

£ 100

-

£ 100

Y

£ 1,000

£ 900

£ 100

-

£ 100

0.0%

-

£ -

10.0%

£ 10

10%

£ 10

0.0%

£ -

£ 210

£ 1,110

428.57%

£ 900

-

302615

TEST

Scally

Damian

BU-1

LPS Business

Business Name

Unit

Organization

Supervisor

Robert Parker

37.5

30-Apr-2012

Job Title

Career Grade Name

Level 5

Job Classification

2 - Exceeds expectations

£ 100

01/07/2013

0.00%

£ 100

-

£ 100

N

£ -

-

£ 100

-

£ 100

0.0%

-

£ -

5.0%

£ 5

5%

£ 5

0.0%

£ -

£ 205

£ 105

-48.78%

-£ 100

-

314953

TEST

Auer

Jens

BU-1

LPS Business

Business Name

Unit

Organization

Supervisor

Robert Parker

40.0

01-Oct-2013

Job Title

Career Grade Name

Level 5

Job Classification

-

£ 100

01/10/2013

0.00%

£ 100

-

£ 100

N

£ -

-

£ 100

-

£ 100

0.0%

-

£ -

2.0%

£ 2

2%

£ 2

0.0%

£ -

£ 202

£ 102

-49.50%

-£ 100

-

306766

TEST

Basol

Kenan

BU-1

LPS Business

Business Name

Unit

Organization

Supervisor

Robert Parker

40.0

15-Sep-2012

Job Title

Career Grade Name

Level 5

Job Classification

3 - Meets expectations

£ 100

01/07/2013

0.00%

£ 100

-

£ 100

N

£ -

-

£ 100

-

£ 100

0.0%

-

£ -

2.0%

£ 2

2%

£ 2

0.0%

£ -

£ 202

£ 102

-49.50%

-£ 100

-

MrsPen · Jun 4, 2014

Hi Darker,
Please be sure to run this on a copy of your file as I have NOT tested this macro. This is a variation of something that I use all the time.

Code:

Sub XL_Split_by_Reviewer()


ActiveSheet.Name = "Complete"
LastRow = Cells(Rows.Count, 1).End(xlUp).Row


'   ***** Robert Parker *****


Rows("1:1").AutoFilter
Range("L:L").AutoFilter Field:=12, Criteria1:=Array("Robert Parker"), Operator:=xlFilterValues
 
Range("A1:AV" & LastRow).SpecialCells(xlCellTypeVisible).Select
Application.Application.CutCopyMode = False
Selection.Copy


With ActiveWorkbook.Sheets
    .Add After:=Sheets(Sheets.Count)
End With
ActNm = ActiveSheet.Name
ActiveSheet.Name = "Robert Parker"
Sheets("Parker").Paste


Sheets("Complete").Select


'   ***** John Doe *****


ActiveSheet.ShowAllData
Range("L:L").AutoFilter Field:=12, Criteria1:=Array("John Doe"), Operator:=xlFilterValues
 
Range("A1:AV" & LastRow).SpecialCells(xlCellTypeVisible).Select
Application.Application.CutCopyMode = False
Selection.Copy


With ActiveWorkbook.Sheets
    .Add After:=Sheets(Sheets.Count)
End With
ActNm = ActiveSheet.Name
ActiveSheet.Name = "John Doe"
Sheets("John Doe").Paste


 
End Sub

What this does is filters based on the name in Column L, copies visible cells only and pastes into a new worksheet. I hope this works for you.
Cheers!

Darker1973 · Jun 5, 2014

MrsPen said:

Hi Darker,
Please be sure to run this on a copy of your file as I have NOT tested this macro. This is a variation of something that I use all the time.

Code:

Sub XL_Split_by_Reviewer()


ActiveSheet.Name = "Complete"
LastRow = Cells(Rows.Count, 1).End(xlUp).Row


'   ***** Robert Parker *****


Rows("1:1").AutoFilter
Range("L:L").AutoFilter Field:=12, Criteria1:=Array("Robert Parker"), Operator:=xlFilterValues
 
Range("A1:AV" & LastRow).SpecialCells(xlCellTypeVisible).Select
Application.Application.CutCopyMode = False
Selection.Copy


With ActiveWorkbook.Sheets
    .Add After:=Sheets(Sheets.Count)
End With
ActNm = ActiveSheet.Name
ActiveSheet.Name = "Robert Parker"
Sheets("Parker").Paste


Sheets("Complete").Select


'   ***** John Doe *****


ActiveSheet.ShowAllData
Range("L:L").AutoFilter Field:=12, Criteria1:=Array("John Doe"), Operator:=xlFilterValues
 
Range("A1:AV" & LastRow).SpecialCells(xlCellTypeVisible).Select
Application.Application.CutCopyMode = False
Selection.Copy


With ActiveWorkbook.Sheets
    .Add After:=Sheets(Sheets.Count)
End With
ActNm = ActiveSheet.Name
ActiveSheet.Name = "John Doe"
Sheets("John Doe").Paste


 
End Sub

What this does is filters based on the name in Column L, copies visible cells only and pastes into a new worksheet. I hope this works for you.
Cheers!

/
Thanks for this - is there a way to take in multiple names - ie 100 and loop through rather than a named name - ?

Darker1973 · Jun 5, 2014

Hi,

It certainly works for the filtering and sets up a new worksheet named Robert Parker but then I receive "Subscript out of Range",(my columns go from A:BQ - which I have amended in the code)

I also need this to split by whatever the name that is in L -it may be any name - is this possible?

Thanks again

Darker

Split a Worksheet into Multiple Worksheets by a name

Darker1973

New Member

Excel Facts

MrsPen

New Member

Darker1973

New Member

Darker1973

New Member

Similar threads

Forum statistics

Share this page

Split a Worksheet into Multiple Worksheets by a name

Darker1973

New Member

Excel Facts

MrsPen

New Member

Darker1973

New Member

Darker1973

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock