Macro Help--separating data into new tabs

symphonic · Sep 28, 2011

I need some Macro help. And let me state, I’m completely ignorant when it comes to Macros, and my Excel skills are limited.

I used a Macro in the past that an AWESOME person from this list wrote for me and walked me through—saved my life!

Here’s what I’ve got:

A single workbook with 106K rows. There is a column labeled “Type.” For each row, there is one of 43 possible “types” listed in the “Type” column. I would like to automate breaking this list of data into separate tabs (worksheets) based on the “Type” category. In other words, I want 43 tabs, one each for each “type” that includes the rows that fall into that “type.” There are only six total columns, all text.

Can anyone help?
Thanks so much,
Steve

hiker95 · Sep 28, 2011

symphonic,

You will just have to change, in the macro code, what column Type is in.

See:

By Norie
DistributeRows
http://www.mrexcel.com/forum/showthread.php?t=315083

symphonic · Oct 5, 2011

Hi Hiker95:

I'm trying to run the macro, (by using the Macros button on the view ribbobn). When I run the program, I get an error on this line:

wsAll.Range("G1:G" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True

The column in my spreadsheet that includes the categories for creating new tabs is G.

The error message tells me that the command requires at least two rows of source data.

Not sure what I'm doing wrong. Any thoughts?

Thanks for your help!

Steve

hiker95 · Oct 5, 2011

symphonic,

What is the worksheet name where your raw data is located?

Please post your macro code:

At the beginning of your posted code, enter the following without the * character:
[*code]

'Your code goes here.

At the end of your posted code, enter the following without the * character:
[*/code]

symphonic · Oct 5, 2011

Thanks, hiker95.

My worksheet (tab) is titled: All.

The macro code (copied from the post you directed me to) is as follows:

Code:

Option Explicit
Sub DistributeRows()
Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim LastRow As Long
Dim LastRowCrit As Long
Dim I As Long
    
    Set wsAll = Worksheets("All") ' change All to the name of the worksheet the existing data is on
    
    LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row
    
    Set wsCrit = Worksheets.Add
    
    ' column G has the criteria eg project ref
    wsAll.Range("G1:G" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True
    
    LastRowCrit = wsCrit.Range("A" & Rows.Count).End(xlUp).Row
    For I = 2 To LastRowCrit
    
        Set wsNew = Worksheets.Add
        wsNew.Name = wsCrit.Range("A2")
        wsAll.Rows("1:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsCrit.Range("A1:A2"), _
         CopyToRange:=wsNew.Range("A1"), Unique:=False
        wsCrit.Rows(2).Delete
        
    Next I
    
    Application.DisplayAlerts = False
    wsCrit.Delete
    Application.DisplayAlerts = True
    
End Sub

Thanks,
Steve

hiker95 · Oct 5, 2011

symphonic,

Can we see some of the raw data in worksheet All?

You will generally get much more help (and faster) in this forum if you can post your small samples (sensitive data scrubbed/removed/changed) (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

VoG · Oct 5, 2011

look here http://www.mrexcel.com/forum/showthread.php?t=396069

symphonic · Oct 5, 2011

Snippet from my Excel file is below. Column G contains the category that I want to use to create separate tabs. There are 43 possible categories and 106K rows. Thanks!

Excel Workbook

A

B

C

D

E

F

G

1

First Name

Last Name

Account Name

Title

Email

Type

2

Amy

Barron

Spirit of Springfield

Vice President

abarron@spiritofspringfield.org

Association

3

Art

Cantor

Massachusetts Lodging Association

President & CEO

acanter@masslodging.com

Association

4

Anne

Haller

ISHAE

Executive Director

ahaller@ishae.org

Association

5

Annette

Hinkle

HIAYH

Director of Finance

ahinkle@hiayh.org

Association

6

Fred

Werlein

Austin Hotel & Lodging Association

Executive Director

ahla@bizaustin.rr.com

Association

7

Alison

Kelso

Colorado Hotel & lodging Association

Director of Member Services

alison@chla.com

Association

8

Alison

Ippolito

Washington State Hotel & Lodging Association

Member Services Director

alisonnippolito@wshla.com

Association

9

Amy

Winston

Maryland Hotel & Lodging Association

VP of Membership Development

amy@mdassn.com

Association

10

Andrew

Webster-Dunn

International Bar Association

Head of Advertising and Sponsorship

andrew.webster-dunn@int-bar.org

Association

11

Annalisa

Russell

NJ Education Association

Administrative Assistant

arussell@njea.org

Association

12

Richard

Ashton

International Association of Chiefs of Police

Highway Safety

ashton@theiacp.org

Association

13

Patti

Colley

Oklahoma Hotel & Lodging Association

Executive Vice President

askpatti@okrestaurants.com

Association

14

Pamela

Cunningham

Aspen Alps Condominium As

General Manager

aspalps@rof.net

Association

15

Auggie

Cipollini

M.D.D.C.

Director of Development

auggiecipollini@theborgata.com

Association

16

Zach

Goss

San Diego Council - AYH

Business Manager

ayhfinance@aol.com

Association

17

Brad

Aldrich

American Hotel & Lodging Association

Director, Business Development

baldrich@ahla.com

Association

18

Denise

Barnett

Wisconsin Innkeepers Association

VP Marketing & Operations

barnett@wisconsinlodging.info

Association

All

Excel 2010

hiker95 · Oct 5, 2011

symphonic,

Thanks for the screenshot.

The original code assumed that your raw data began in column A.

Sample raw data in worksheet All:

Excel Workbook

A

B

C

D

E

F

G

1

First Name

Last Name

Account Name

Title

Email

Type

2

Amy

Barron

Spirit of Springfield

Vice President

abarron@spiritofspringfield.org

Association

3

Art

Cantor

Massachusetts Lodging Association

President & CEO

acanter@masslodging.com

Association

4

Anne

Haller

ISHAE

Executive Director

ahaller@ishae.org

Association

5

Annette

Hinkle

HIAYH

Director of Finance

ahinkle@hiayh.org

Association

6

Fred

Werlein

Austin Hotel & Lodging Association

Executive Director

ahla@bizaustin.rr.com

Association

7

Alison

Kelso

Colorado Hotel & lodging Association

Director of Member Services

alison@chla.com

Association

8

Alison

Ippolito

Washington State Hotel & Lodging Association

Member Services Director

alisonnippolito@wshla.com

Association

9

Amy

Winston

Maryland Hotel & Lodging Association

VP of Membership Development

amy@mdassn.com

Association

10

Andrew

Webster-Dunn

International Bar Association

Head of Advertising and Sponsorship

andrew.webster-dunn@int-bar.org

Association

11

Annalisa

Russell

NJ Education Association

Administrative Assistant

arussell@njea.org

Association

12

Richard

Ashton

International Association of Chiefs of Police

Highway Safety

ashton@theiacp.org

Association

13

Patti

Colley

Oklahoma Hotel & Lodging Association

Executive Vice President

askpatti@okrestaurants.com

Association

14

Pamela

Cunningham

Aspen Alps Condominium As

General Manager

aspalps@rof.net

Association

15

Auggie

Cipollini

M.D.D.C.

Director of Development

auggiecipollini@theborgata.com

Association

16

Zach

Goss

San Diego Council - AYH

Business Manager

ayhfinance@aol.com

Association

17

Brad

Aldrich

American Hotel & Lodging Association

Director, Business Development

baldrich@ahla.com

Association

18

Denise

Barnett

Wisconsin Innkeepers Association

VP Marketing & Operations

barnett@wisconsinlodging.info

Association

19

All

After the macro in a new worksheet Association:

Excel Workbook

A

B

C

D

E

F

G

1

First Name

Last Name

Account Name

Title

Email

Type

2

Amy

Barron

Spirit of Springfield

Vice President

abarron@spiritofspringfield.org

Association

3

Art

Cantor

Massachusetts Lodging Association

President & CEO

acanter@masslodging.com

Association

4

Anne

Haller

ISHAE

Executive Director

ahaller@ishae.org

Association

5

Annette

Hinkle

HIAYH

Director of Finance

ahinkle@hiayh.org

Association

6

Fred

Werlein

Austin Hotel & Lodging Association

Executive Director

ahla@bizaustin.rr.com

Association

7

Alison

Kelso

Colorado Hotel & lodging Association

Director of Member Services

alison@chla.com

Association

8

Alison

Ippolito

Washington State Hotel & Lodging Association

Member Services Director

alisonnippolito@wshla.com

Association

9

Amy

Winston

Maryland Hotel & Lodging Association

VP of Membership Development

amy@mdassn.com

Association

10

Andrew

Webster-Dunn

International Bar Association

Head of Advertising and Sponsorship

andrew.webster-dunn@int-bar.org

Association

11

Annalisa

Russell

NJ Education Association

Administrative Assistant

arussell@njea.org

Association

12

Richard

Ashton

International Association of Chiefs of Police

Highway Safety

ashton@theiacp.org

Association

13

Patti

Colley

Oklahoma Hotel & Lodging Association

Executive Vice President

askpatti@okrestaurants.com

Association

14

Pamela

Cunningham

Aspen Alps Condominium As

General Manager

aspalps@rof.net

Association

15

Auggie

Cipollini

M.D.D.C.

Director of Development

auggiecipollini@theborgata.com

Association

16

Zach

Goss

San Diego Council - AYH

Business Manager

ayhfinance@aol.com

Association

17

Brad

Aldrich

American Hotel & Lodging Association

Director, Business Development

baldrich@ahla.com

Association

18

Denise

Barnett

Wisconsin Innkeepers Association

VP Marketing & Operations

barnett@wisconsinlodging.info

Association

19

Association

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Option Explicit
Sub DistributeRowsV2()
' hiker95, 10/05/2011
' http://www.mrexcel.com/forum/showthread.php?t=582041
Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim LastRow As Long
Dim LastRowCrit As Long
Dim I As Long
Application.ScreenUpdating = False
Set wsAll = Worksheets("All")
LastRow = wsAll.Range("B" & Rows.Count).End(xlUp).Row
Set wsCrit = Worksheets.Add
wsAll.Range("G1:G" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True
LastRowCrit = wsCrit.Range("A" & Rows.Count).End(xlUp).Row
For I = 2 To LastRowCrit
  Set wsNew = Worksheets.Add(After:=Worksheets(Worksheets.Count))
  wsNew.Name = wsCrit.Range("A2")
  wsAll.Rows("1:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsCrit.Range("A1:A2"), CopyToRange:=wsNew.Range("A1"), Unique:=False
  wsNew.UsedRange.Columns.AutoFit
  wsCrit.Rows(2).Delete
Next I
Application.DisplayAlerts = False
wsCrit.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the DistributeRowsV2 macro.

symphonic · Oct 5, 2011

This is fantastic. It worked perfectly. I can't thank you enough. I would have never figured this out on my own.

You're the best. Really appreciate your help and patience with me.

Steve

Macro Help--separating data into new tabs

symphonic

New Member

Excel Facts

hiker95

Well-known Member

symphonic

New Member

hiker95

Well-known Member

symphonic

New Member

hiker95

Well-known Member

VoG

Legend

symphonic

New Member

hiker95

Well-known Member

symphonic

New Member

Similar threads

Forum statistics

Share this page

Macro Help--separating data into new tabs

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Legend

New Member

Well-known Member

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