Shown below is an advanced macro that clears up another spreadsheet. The only problem is that I did not write this macro but I am trying to figure out how to edit it. This macro cleans up another excel spreadsheet and then deletes duplicates and subtotals information. I'm trying to figure out how to edit this macro to delete additional duplicates, because it is not getting all of them in each excel spreadsheet.
The excel spreadsheet that is being edited, looks a little like this before the macro is ran:
After the macro is ran it looks a little like this:
Any ideas how I can edit this macro to complete the job??
The excel spreadsheet that is being edited, looks a little like this before the macro is ran:
Rich (BB code):
No Column
Dealer Number Primary Dba Name Caller Full Name Channel Name NONE
0570 Fritz's Harley-Davidson Fax 1
0570 Fritz's Harley-Davidson Subtotal 1
0570 Fritz's Harley-Davidson Andy (Panda) Cunningham Telephone 7
0570 Fritz's Harley-Davidson Andy (Panda) Cunningham Subtotal 7
0570 Fritz's Harley-Davidson Dan ((vehicle #1)) Height Telephone 2
0570 Fritz's Harley-Davidson Dan ((vehicle #1)) Height Subtotal 2
0570 Fritz's Harley-Davidson Ed Grivelrod Telephone 1
0570 Fritz's Harley-Davidson Ed Grivelrod Subtotal 1
0570 Fritz's Harley-Davidson Fax 2 Fax 1
0570 Fritz's Harley-Davidson Fax 2 Subtotal 1
0570 Fritz's Harley-Davidson George (Drego) Albano Telephone 19
0570 Fritz's Harley-Davidson George (Drego) Albano Subtotal 19
0570 Fritz's Harley-Davidson Jamie Mcgarty Telephone 1
0570 Fritz's Harley-Davidson Jamie Mcgarty Subtotal 1
0570 Fritz's Harley-Davidson John P (Woodchuck) Narciso Telephone 2
0570 Fritz's Harley-Davidson John P (Woodchuck) Narciso Subtotal 2
0570 Fritz's Harley-Davidson Laurie (Foof) Ferriero Telephone 1
0570 Fritz's Harley-Davidson Laurie (Foof) Ferriero Subtotal 1
0570 Fritz's Harley-Davidson Main Email/Fax Fax 3
0570 Fritz's Harley-Davidson Main Email/Fax Subtotal 3
0570 Fritz's Harley-Davidson Malone Ranalli Telephone 1
0570 Fritz's Harley-Davidson Malone Ranalli Subtotal 1
0570 Fritz's Harley-Davidson Subtotal 39
0570 Subtotal 39
1455 Harley-Davidson of New York Fax 5
1455 Harley-Davidson of New York Subtotal 5
1455 Harley-Davidson of New York Adam Alberghine Telephone 30
1455 Harley-Davidson of New York Adam Alberghine Subtotal 30
1455 Harley-Davidson of New York Danielle Melrose Telephone 3
1455 Harley-Davidson of New York Danielle Melrose Subtotal 3
1455 Harley-Davidson of New York David Martin Telephone 1
1455 Harley-Davidson of New York David Martin Subtotal 1
1455 Harley-Davidson of New York Frank Caramico Telephone 1
1455 Harley-Davidson of New York Frank Caramico Subtotal 1
1455 Harley-Davidson of New York James (Jimmy) Holland Telephone 2
1455 Harley-Davidson of New York James (Jimmy) Holland Subtotal 2
1455 Harley-Davidson of New York Subtotal 42
1455 Subtotal 42
1797 Harley-Davidson of Nassau County Fax 11
1797 Harley-Davidson of Nassau County Subtotal 11
1797 Harley-Davidson of Nassau County Bobby C Telephone 3
1797 Harley-Davidson of Nassau County Bobby C Subtotal 3
1797 Harley-Davidson of Nassau County Byron Reich Telephone 8
1797 Harley-Davidson of Nassau County Byron Reich Subtotal 8
1797 Harley-Davidson of Nassau County Dave Kohn Telephone 2
1797 Harley-Davidson of Nassau County Dave Kohn Subtotal 2
1797 Harley-Davidson of Nassau County Elizabeth Bergamini Telephone 1
1797 Harley-Davidson of Nassau County Elizabeth Bergamini Subtotal 1
1797 Harley-Davidson of Nassau County Lisa Valente Telephone 9
1797 Harley-Davidson of Nassau County Lisa Valente Subtotal 9
1797 Harley-Davidson of Nassau County Main Email/Fax Fax 2
1797 Harley-Davidson of Nassau County Main Email/Fax Subtotal 2
1797 Harley-Davidson of Nassau County Michael Nicoletti Telephone 3
1797 Harley-Davidson of Nassau County Michael Nicoletti Subtotal 3
1797 Harley-Davidson of Nassau County Mike Brodsky Telephone 2
1797 Harley-Davidson of Nassau County Mike Brodsky Subtotal 2
1797 Harley-Davidson of Nassau County Nick Palesty Telephone 3
1797 Harley-Davidson of Nassau County Nick Palesty Subtotal 3
1797 Harley-Davidson of Nassau County Phil Melfi Telephone 15
1797 Harley-Davidson of Nassau County Phil Melfi Subtotal 15
1797 Harley-Davidson of Nassau County Tim Magnuson Telephone 1
1797 Harley-Davidson of Nassau County Tim Magnuson Subtotal 1
1797 Harley-Davidson of Nassau County Subtotal 60
1797 Subtotal 60
1972 NewRoc Harley-Davidson Fax 7
1972 NewRoc Harley-Davidson Subtotal 7
1972 NewRoc Harley-Davidson Crissy Scopoletti Telephone 5
1972 NewRoc Harley-Davidson Crissy Scopoletti Subtotal 5
1972 NewRoc Harley-Davidson Dave Delio Telephone 11
1972 NewRoc Harley-Davidson Dave Delio Subtotal 11
1972 NewRoc Harley-Davidson Dee Klein Telephone 1
1972 NewRoc Harley-Davidson Dee Klein Subtotal 1
1972 NewRoc Harley-Davidson Email/Fax Main Fax 3
1972 NewRoc Harley-Davidson Email/Fax Main Subtotal 3
1972 NewRoc Harley-Davidson Heather Webb Telephone 1
1972 NewRoc Harley-Davidson Heather Webb Subtotal 1
1972 NewRoc Harley-Davidson Jack (917-620-0555) Meskunanus Telephone 1
1972 NewRoc Harley-Davidson Jack (917-620-0555) Meskunanus Subtotal 1
1972 NewRoc Harley-Davidson Jim Maguire Telephone 2
1972 NewRoc Harley-Davidson Jim Maguire Subtotal 2
1972 NewRoc Harley-Davidson John Maguire Telephone 1
1972 NewRoc Harley-Davidson John Maguire Subtotal 1
1972 NewRoc Harley-Davidson John Pluchino Telephone 8
1972 NewRoc Harley-Davidson John Pluchino Subtotal 8
1972 NewRoc Harley-Davidson Minos Tzouflas Telephone 1
1972 NewRoc Harley-Davidson Minos Tzouflas Subtotal 1
1972 NewRoc Harley-Davidson Rob (rg@nrhd.com) Gambichler Fax 1
1972 NewRoc Harley-Davidson Rob (rg@nrhd.com) Gambichler Subtotal 1
1972 NewRoc Harley-Davidson Wayne (wayne@nrhd.com) Sforza Telephone 3
1972 NewRoc Harley-Davidson Wayne (wayne@nrhd.com) Sforza Subtotal 3
1972 NewRoc Harley-Davidson Subtotal 45
1972 Subtotal 45
1998 Miracle Mile Harley-Davidson Fax 1
1998 Miracle Mile Harley-Davidson Subtotal 1
1998 Miracle Mile Harley-Davidson Cheryl Reitano Telephone 2
1998 Miracle Mile Harley-Davidson Cheryl Reitano Subtotal 2
1998 Miracle Mile Harley-Davidson doug paloscio Telephone 1
1998 Miracle Mile Harley-Davidson doug paloscio Subtotal 1
1998 Miracle Mile Harley-Davidson Email/Fax Main Fax 5
1998 Miracle Mile Harley-Davidson Email/Fax Main Subtotal 5
1998 Miracle Mile Harley-Davidson Eric Ruther Telephone 4
1998 Miracle Mile Harley-Davidson Eric Ruther Subtotal 4
1998 Miracle Mile Harley-Davidson Len (631-327-8444 cell) Campanelli Telephone 3
1998 Miracle Mile Harley-Davidson Len (631-327-8444 cell) Campanelli Subtotal 3
1998 Miracle Mile Harley-Davidson Luciano (Lou) Jaramillo Telephone 2
1998 Miracle Mile Harley-Davidson Luciano (Lou) Jaramillo Subtotal 2
1998 Miracle Mile Harley-Davidson Phil Lynch Telephone 1
1998 Miracle Mile Harley-Davidson Phil Lynch Subtotal 1
1998 Miracle Mile Harley-Davidson Russ Cox Telephone 9
1998 Miracle Mile Harley-Davidson Russ Cox Subtotal 9
1998 Miracle Mile Harley-Davidson Steve Ruckel Telephone 2
1998 Miracle Mile Harley-Davidson Steve Ruckel Subtotal 2
1998 Miracle Mile Harley-Davidson Subtotal 30
1998 Subtotal 30
2006 Essex County Harley-Davidson Al Molaf Telephone 5
2006 Essex County Harley-Davidson Al Molaf Subtotal 5
2006 Essex County Harley-Davidson Clarence Francis Telephone 3
2006 Essex County Harley-Davidson Clarence Francis Subtotal 3
2006 Essex County Harley-Davidson Ed Archambault Telephone 2
2006 Essex County Harley-Davidson Ed Archambault Subtotal 2
2006 Essex County Harley-Davidson Fax 2 Fax 2
2006 Essex County Harley-Davidson Fax 2 Subtotal 2
2006 Essex County Harley-Davidson Gene Booker Telephone 7
2006 Essex County Harley-Davidson Gene Booker Subtotal 7
2006 Essex County Harley-Davidson Jay Toussaint Telephone 4
2006 Essex County Harley-Davidson Jay Toussaint Subtotal 4
2006 Essex County Harley-Davidson Paul Barthelme Telephone 1
2006 Essex County Harley-Davidson Paul Barthelme Subtotal 1
2006 Essex County Harley-Davidson Robert Rutland Telephone 1
2006 Essex County Harley-Davidson Robert Rutland Subtotal 1
2006 Essex County Harley-Davidson Subtotal 25
2006 Subtotal 25
3027 Kosco Harley-Davidson, Inc. Fax 3
3027 Kosco Harley-Davidson, Inc. Subtotal 3
3027 Kosco Harley-Davidson, Inc. Barry Donow Telephone 3
3027 Kosco Harley-Davidson, Inc. Barry Donow Subtotal 3
3027 Kosco Harley-Davidson, Inc. Denise Kosco Telephone 2
3027 Kosco Harley-Davidson, Inc. Denise Kosco Subtotal 2
3027 Kosco Harley-Davidson, Inc. Eric (parts ext 20) Loeffler Telephone 1
3027 Kosco Harley-Davidson, Inc. Eric (parts ext 20) Loeffler Subtotal 1
3027 Kosco Harley-Davidson, Inc. Jason ((x. 5)) Kosco Telephone 3
3027 Kosco Harley-Davidson, Inc. Jason ((x. 5)) Kosco Subtotal 3
3027 Kosco Harley-Davidson, Inc. Rich Kosco Telephone 2
3027 Kosco Harley-Davidson, Inc. Rich Kosco Subtotal 2
3027 Kosco Harley-Davidson, Inc. Subtotal 14
3027 Subtotal 14
3031 Liberty Harley-Davidson Fax 20
3031 Liberty Harley-Davidson Subtotal 20
3031 Liberty Harley-Davidson Anthony Caligiuri Telephone 1
3031 Liberty Harley-Davidson Anthony Caligiuri Subtotal 1
3031 Liberty Harley-Davidson Chris (Moose) Tonstad Telephone 1
3031 Liberty Harley-Davidson Chris (Moose) Tonstad Subtotal 1
3031 Liberty Harley-Davidson Michelle McTamney Telephone 1
3031 Liberty Harley-Davidson Michelle McTamney Subtotal 1
3031 Liberty Harley-Davidson Paul McGlynn Telephone 2
3031 Liberty Harley-Davidson Paul McGlynn Subtotal 2
3031 Liberty Harley-Davidson Rob Shaw Telephone 4
3031 Liberty Harley-Davidson Rob Shaw Subtotal 4
3031 Liberty Harley-Davidson Tony Bordonaro Telephone 1
3031 Liberty Harley-Davidson Tony Bordonaro Subtotal 1
3031 Liberty Harley-Davidson Vince Meehan Telephone 4
3031 Liberty Harley-Davidson Vince Meehan Subtotal 4
3031 Liberty Harley-Davidson Subtotal 34
3031 Subtotal 34
3036 H-D Of Bergen County Email 1
3036 H-D Of Bergen County Fax 13
3036 H-D Of Bergen County Subtotal 14
3036 H-D Of Bergen County Amanda Sherman Telephone 3
3036 H-D Of Bergen County Amanda Sherman Subtotal 3
3036 H-D Of Bergen County Amy Reyes Telephone 2
3036 H-D Of Bergen County Amy Reyes Subtotal 2
3036 H-D Of Bergen County Dave ((x 131)) Desmarais Telephone 5
3036 H-D Of Bergen County Dave ((x 131)) Desmarais Subtotal 5
3036 H-D Of Bergen County Gina Denaro Telephone 1
3036 H-D Of Bergen County Gina Denaro Subtotal 1
3036 H-D Of Bergen County Joe Bamberger Telephone 5
3036 H-D Of Bergen County Joe Bamberger Subtotal 5
3036 H-D Of Bergen County Joel Diaz Telephone 1
3036 H-D Of Bergen County Joel Diaz Subtotal 1
3036 H-D Of Bergen County Jon (parts ext 158) Kolano Telephone 3
3036 H-D Of Bergen County Jon (parts ext 158) Kolano Subtotal 3
3036 H-D Of Bergen County Ken Muso Telephone 1
3036 H-D Of Bergen County Ken Muso Subtotal 1
3036 H-D Of Bergen County Lori L'Rourke Telephone 4
3036 H-D Of Bergen County Lori L'Rourke Subtotal 4
3036 H-D Of Bergen County Louie (Lou) Manuppelli Telephone 3
3036 H-D Of Bergen County Louie (Lou) Manuppelli Subtotal 3
3036 H-D Of Bergen County Mike Sallemi Telephone 2
3036 H-D Of Bergen County Mike Sallemi Subtotal 2
3036 H-D Of Bergen County Phil (ext 35) DiGennaro Telephone 4
3036 H-D Of Bergen County Phil (ext 35) DiGennaro Subtotal 4
3036 H-D Of Bergen County Scott Muro Telephone 1
3036 H-D Of Bergen County Scott Muro Subtotal 1
3036 H-D Of Bergen County Vinny (ext 126) Panissidi Telephone 8
3036 H-D Of Bergen County Vinny (ext 126) Panissidi Subtotal 8
3036 H-D Of Bergen County Subtotal 57
3036 Subtotal 57
3159 Brooklyn Harley-Davidson Sales Greg Barker Telephone 7
3159 Brooklyn Harley-Davidson Sales Greg Barker Subtotal 7
3159 Brooklyn Harley-Davidson Sales Joe (JB) Burgess Telephone 3
3159 Brooklyn Harley-Davidson Sales Joe (JB) Burgess Subtotal 3
3159 Brooklyn Harley-Davidson Sales Nancy (ace) Melia Telephone 3
3159 Brooklyn Harley-Davidson Sales Nancy (ace) Melia Subtotal 3
3159 Brooklyn Harley-Davidson Sales Subtotal 13
3159 Subtotal 13
3174 Prestige Harley-Davidson, Inc. Bernie Milano Telephone 3
3174 Prestige Harley-Davidson, Inc. Bernie Milano Subtotal 3
3174 Prestige Harley-Davidson, Inc. Bill Westrick Telephone 4
3174 Prestige Harley-Davidson, Inc. Bill Westrick Subtotal 4
3174 Prestige Harley-Davidson, Inc. Carrie Goldberg Telephone 2
3174 Prestige Harley-Davidson, Inc. Carrie Goldberg Subtotal 2
3174 Prestige Harley-Davidson, Inc. Main Email/Fax Fax 5
3174 Prestige Harley-Davidson, Inc. Main Email/Fax Subtotal 5
3174 Prestige Harley-Davidson, Inc. Vince (Vinny) Grimaldi Telephone 2
3174 Prestige Harley-Davidson, Inc. Vince (Vinny) Grimaldi Subtotal 2
3174 Prestige Harley-Davidson, Inc. Subtotal 16
3174 Subtotal 16
3182 Eastern Harley-Davidson Linda Sullivan Telephone 2
3182 Eastern Harley-Davidson Linda Sullivan Subtotal 2
3182 Eastern Harley-Davidson Main Email/Fax Fax 4
3182 Eastern Harley-Davidson Main Email/Fax Subtotal 4
3182 Eastern Harley-Davidson Michael Chornoma Telephone 1
3182 Eastern Harley-Davidson Michael Chornoma Subtotal 1
3182 Eastern Harley-Davidson Todd Stevens Telephone 2
3182 Eastern Harley-Davidson Todd Stevens Subtotal 2
3182 Eastern Harley-Davidson Subtotal 9
3182 Subtotal 9
3235 Lighthouse Harley-Davidson, Inc. Fax 9
3235 Lighthouse Harley-Davidson, Inc. Subtotal 9
3235 Lighthouse Harley-Davidson, Inc. Dan Zagorski Telephone 3
3235 Lighthouse Harley-Davidson, Inc. Dan Zagorski Subtotal 3
3235 Lighthouse Harley-Davidson, Inc. Donna Lofaso Telephone 1
3235 Lighthouse Harley-Davidson, Inc. Donna Lofaso Subtotal 1
3235 Lighthouse Harley-Davidson, Inc. John Mooney Telephone 4
3235 Lighthouse Harley-Davidson, Inc. John Mooney Subtotal 4
3235 Lighthouse Harley-Davidson, Inc. Melissa Blackford Telephone 2
3235 Lighthouse Harley-Davidson, Inc. Melissa Blackford Subtotal 2
3235 Lighthouse Harley-Davidson, Inc. Paul Fardella Telephone 4
3235 Lighthouse Harley-Davidson, Inc. Paul Fardella Subtotal 4
3235 Lighthouse Harley-Davidson, Inc. Rick Sorrentino Telephone 1
3235 Lighthouse Harley-Davidson, Inc. Rick Sorrentino Subtotal 1
3235 Lighthouse Harley-Davidson, Inc. Rick Hawkey Telephone 13
3235 Lighthouse Harley-Davidson, Inc. Rick Hawkey Subtotal 13
3235 Lighthouse Harley-Davidson, Inc. Rob (JR) Schneider Telephone 11
3235 Lighthouse Harley-Davidson, Inc. Rob (JR) Schneider Subtotal 11
3235 Lighthouse Harley-Davidson, Inc. Rubens Deoliveiora Telephone 4
3235 Lighthouse Harley-Davidson, Inc. Rubens Deoliveiora Subtotal 4
3235 Lighthouse Harley-Davidson, Inc. Stephanie Williams Telephone 1
3235 Lighthouse Harley-Davidson, Inc. Stephanie Williams Subtotal 1
3235 Lighthouse Harley-Davidson, Inc. Subtotal 53
3235 Subtotal 53
3417 Staten Island Harley-Davidson Email 2
3417 Staten Island Harley-Davidson Subtotal 2
3417 Staten Island Harley-Davidson Amanda Cenneriello Telephone 4
3417 Staten Island Harley-Davidson Amanda Cenneriello Subtotal 4
3417 Staten Island Harley-Davidson Ed White Telephone 8
3417 Staten Island Harley-Davidson Ed White Subtotal 8
3417 Staten Island Harley-Davidson eileen esposito Telephone 4
3417 Staten Island Harley-Davidson eileen esposito Subtotal 4
3417 Staten Island Harley-Davidson Fax 1 Fax 10
3417 Staten Island Harley-Davidson Fax 1 Subtotal 10
3417 Staten Island Harley-Davidson Gary 1 Telephone 2
3417 Staten Island Harley-Davidson Gary 1 Subtotal 2
3417 Staten Island Harley-Davidson Karen (X) Albrecht Telephone 7
3417 Staten Island Harley-Davidson Karen (X) Albrecht Subtotal 7
3417 Staten Island Harley-Davidson Karen Lombardi Telephone 1
3417 Staten Island Harley-Davidson Karen Lombardi Subtotal 1
3417 Staten Island Harley-Davidson Marisa Giustion Telephone 2
3417 Staten Island Harley-Davidson Marisa Giustion Subtotal 2
3417 Staten Island Harley-Davidson Mark Crescitelli Telephone 4
3417 Staten Island Harley-Davidson Mark Crescitelli Subtotal 4
3417 Staten Island Harley-Davidson Mike Lombardi Telephone 7
3417 Staten Island Harley-Davidson Mike Lombardi Subtotal 7
3417 Staten Island Harley-Davidson Subtotal 51
3417 Subtotal 51
3439 Suffolk County Harley-Davidson Brian Giessen Telephone 17
3439 Suffolk County Harley-Davidson Brian Giessen Subtotal 17
3439 Suffolk County Harley-Davidson David Martin Telephone 8
3439 Suffolk County Harley-Davidson David Martin Subtotal 8
3439 Suffolk County Harley-Davidson Doreen Buscarino Telephone 1
3439 Suffolk County Harley-Davidson Doreen Buscarino Subtotal 1
3439 Suffolk County Harley-Davidson Main Email/Fax Fax 13
3439 Suffolk County Harley-Davidson Main Email/Fax Subtotal 13
3439 Suffolk County Harley-Davidson Mike Biener Telephone 2
3439 Suffolk County Harley-Davidson Mike Biener Subtotal 2
3439 Suffolk County Harley-Davidson Rick (parts) Lefebure Telephone 10
3439 Suffolk County Harley-Davidson Rick (parts) Lefebure Subtotal 10
3439 Suffolk County Harley-Davidson Subtotal 51
3439 Subtotal 51
3442 Reggie Pink, Inc. Fax 3
3442 Reggie Pink, Inc. Subtotal 3
3442 Reggie Pink, Inc. Bob Dellabadia Telephone 2
3442 Reggie Pink, Inc. Bob Dellabadia Subtotal 2
3442 Reggie Pink, Inc. Janie Pink Telephone 4
3442 Reggie Pink, Inc. Janie Pink Subtotal 4
3442 Reggie Pink, Inc. Ray Zerbarini Telephone 4
3442 Reggie Pink, Inc. Ray Zerbarini Subtotal 4
3442 Reggie Pink, Inc. Subtotal 13
3442 Subtotal 13
Total 552After the macro is ran it looks a little like this:
Rich (BB code):
No Column
Dealer Number Primary Dba Name Caller Full Name Channel Name NONE
0570 Fritz's Harley-Davidson Fax 1
Andy (Panda) Cunningham Telephone 7
Dan ((vehicle #1)) Height Telephone 2
Ed Grivelrod Telephone 1
Fax 2 Fax 1
George (Drego) Albano Telephone 19
Jamie Mcgarty Telephone 1
John P (Woodchuck) Narciso Telephone 2
Laurie (Foof) Ferriero Telephone 1
Main Email/Fax Fax 3
Malone Ranalli Telephone 1
0570 Subtotal 39
1455 Harley-Davidson of New York Fax 5
Adam Alberghine Telephone 30
Danielle Melrose Telephone 3
David Martin Telephone 1
Frank Caramico Telephone 1
James (Jimmy) Holland Telephone 2
1455 Subtotal 42
1797 Harley-Davidson of Nassau County Fax 11
Bobby C Telephone 3
Byron Reich Telephone 8
Dave Kohn Telephone 2
Elizabeth Bergamini Telephone 1
Lisa Valente Telephone 9
Main Email/Fax Fax 2
Michael Nicoletti Telephone 3
Mike Brodsky Telephone 2
Nick Palesty Telephone 3
Phil Melfi Telephone 15
Tim Magnuson Telephone 1
1797 Subtotal 60
1972 NewRoc Harley-Davidson Fax 7
1972 NewRoc Harley-Davidson Crissy Scopoletti Telephone 5
Dave Delio Telephone 11
Dee Klein Telephone 1
Email/Fax Main Fax 3
Heather Webb Telephone 1
Jack (917-620-0555) Meskunanus Telephone 1
Jim Maguire Telephone 2
John Maguire Telephone 1
John Pluchino Telephone 8
Minos Tzouflas Telephone 1
Rob (rg@nrhd.com) Gambichler Fax 1
Wayne (wayne@nrhd.com) Sforza Telephone 3
1972 Subtotal 45
1998 Miracle Mile Harley-Davidson Fax 1
Cheryl Reitano Telephone 2
doug paloscio Telephone 1
Email/Fax Main Fax 5
Eric Ruther Telephone 4
Len (631-327-8444 cell) Campanelli Telephone 3
Luciano (Lou) Jaramillo Telephone 2
Phil Lynch Telephone 1
Russ Cox Telephone 9
Steve Ruckel Telephone 2
1998 Subtotal 30
2006 Essex County Harley-Davidson Al Molaf Telephone 5
Clarence Francis Telephone 3
Ed Archambault Telephone 2
Fax 2 Fax 2
Gene Booker Telephone 7
Jay Toussaint Telephone 4
Paul Barthelme Telephone 1
Robert Rutland Telephone 1
2006 Subtotal 25
3027 Kosco Harley-Davidson, Inc. Fax 3
Barry Donow Telephone 3
3027 Kosco Harley-Davidson, Inc. Denise Kosco Telephone 2
Eric (parts ext 20) Loeffler Telephone 1
Jason ((x. 5)) Kosco Telephone 3
Rich Kosco Telephone 2
3027 Subtotal 14
3031 Liberty Harley-Davidson Fax 20
Anthony Caligiuri Telephone 1
Chris (Moose) Tonstad Telephone 1
Michelle McTamney Telephone 1
Paul McGlynn Telephone 2
Rob Shaw Telephone 4
Tony Bordonaro Telephone 1
Vince Meehan Telephone 4
3031 Subtotal 34
3036 H-D Of Bergen County Email 1
Fax 13
3036 H-D Of Bergen County Amanda Sherman Telephone 3
3036 H-D Of Bergen County Amy Reyes Telephone 2
3036 H-D Of Bergen County Dave ((x 131)) Desmarais Telephone 5
3036 H-D Of Bergen County Gina Denaro Telephone 1
3036 H-D Of Bergen County Joe Bamberger Telephone 5
3036 H-D Of Bergen County Joel Diaz Telephone 1
3036 H-D Of Bergen County Jon (parts ext 158) Kolano Telephone 3
3036 H-D Of Bergen County Ken Muso Telephone 1
3036 H-D Of Bergen County Lori L'Rourke Telephone 4
3036 H-D Of Bergen County Louie (Lou) Manuppelli Telephone 3
3036 H-D Of Bergen County Mike Sallemi Telephone 2
3036 H-D Of Bergen County Phil (ext 35) DiGennaro Telephone 4
3036 H-D Of Bergen County Scott Muro Telephone 1
3036 H-D Of Bergen County Vinny (ext 126) Panissidi Telephone 8
3036 Subtotal 57
3159 Brooklyn Harley-Davidson Sales Greg Barker Telephone 7
3159 Brooklyn Harley-Davidson Sales Joe (JB) Burgess Telephone 3
3159 Brooklyn Harley-Davidson Sales Nancy (ace) Melia Telephone 3
3159 Subtotal 13
3174 Prestige Harley-Davidson, Inc. Bernie Milano Telephone 3
3174 Prestige Harley-Davidson, Inc. Bill Westrick Telephone 4
3174 Prestige Harley-Davidson, Inc. Carrie Goldberg Telephone 2
3174 Prestige Harley-Davidson, Inc. Main Email/Fax Fax 5
3174 Prestige Harley-Davidson, Inc. Vince (Vinny) Grimaldi Telephone 2
3174 Subtotal 16
3182 Eastern Harley-Davidson Linda Sullivan Telephone 2
3182 Eastern Harley-Davidson Main Email/Fax Fax 4
3182 Eastern Harley-Davidson Michael Chornoma Telephone 1
3182 Eastern Harley-Davidson Todd Stevens Telephone 2
3182 Subtotal 9
3235 Lighthouse Harley-Davidson, Inc. Fax 9
3235 Lighthouse Harley-Davidson, Inc. Dan Zagorski Telephone 3
3235 Lighthouse Harley-Davidson, Inc. Donna Lofaso Telephone 1
3235 Lighthouse Harley-Davidson, Inc. John Mooney Telephone 4
3235 Lighthouse Harley-Davidson, Inc. Melissa Blackford Telephone 2
3235 Lighthouse Harley-Davidson, Inc. Paul Fardella Telephone 4
3235 Lighthouse Harley-Davidson, Inc. Rick Sorrentino Telephone 1
3235 Lighthouse Harley-Davidson, Inc. Rick Hawkey Telephone 13
3235 Lighthouse Harley-Davidson, Inc. Rob (JR) Schneider Telephone 11
3235 Lighthouse Harley-Davidson, Inc. Rubens Deoliveiora Telephone 4
3235 Lighthouse Harley-Davidson, Inc. Stephanie Williams Telephone 1
3235 Subtotal 53
3417 Staten Island Harley-Davidson Email 2
3417 Staten Island Harley-Davidson Amanda Cenneriello Telephone 4
3417 Staten Island Harley-Davidson Ed White Telephone 8
3417 Staten Island Harley-Davidson eileen esposito Telephone 4
3417 Staten Island Harley-Davidson Fax 1 Fax 10
3417 Staten Island Harley-Davidson Gary 1 Telephone 2
3417 Staten Island Harley-Davidson Karen (X) Albrecht Telephone 7
3417 Staten Island Harley-Davidson Karen Lombardi Telephone 1
3417 Staten Island Harley-Davidson Marisa Giustion Telephone 2
3417 Staten Island Harley-Davidson Mark Crescitelli Telephone 4
3417 Staten Island Harley-Davidson Mike Lombardi Telephone 7
3417 Subtotal 51
3439 Suffolk County Harley-Davidson Brian Giessen Telephone 17
3439 Suffolk County Harley-Davidson David Martin Telephone 8
3439 Suffolk County Harley-Davidson Doreen Buscarino Telephone 1
3439 Suffolk County Harley-Davidson Main Email/Fax Fax 13
3439 Suffolk County Harley-Davidson Mike Biener Telephone 2
3439 Suffolk County Harley-Davidson Rick (parts) Lefebure Telephone 10
3439 Subtotal 51
3442 Reggie Pink, Inc. Fax 3
3442 Reggie Pink, Inc. Bob Dellabadia Telephone 2
3442 Reggie Pink, Inc. Janie Pink Telephone 4
3442 Reggie Pink, Inc. Ray Zerbarini Telephone 4
3442 Subtotal 13
Total 552
Rich (BB code):
'Option Explicit 'Inserted by OfficeConverter 8.0.0 on line 1
Public Sub formatDriver(strReportType As String)
' formatDriver Driver program for formating reports
Dim ColNm1 As String
Dim SearchStr1 As String
Dim ActiveColumns As Long
Dim SubtotalCol As Long
Dim StartRow_ID As Long
Dim StartCol_ID As Long
Dim EndRow_ID As Long
Dim EndCol_ID As Long
Dim HeadingRange As Variant
' Variables required to handle the removal of duplicate cells
' Duplicate cells will exist if we do a grouping in an EICC report
' that results in multiple records per group.
Dim StartResultsRID As Long
Dim StartRCol_ID As Long
Dim SS1 As Long
Dim SS2 As Long
Dim SS2P As Long
Dim ER1(1 To 3, 1 To 50) As Variant
Dim ER2(1 To 3, 1 To 50) As Variant
Dim ER3(1 To 3, 1 To 50) As Variant
Dim StartofMeasureCol As Long
Dim CompareColEnd As Long
Dim CompareColEndP As Long
Dim CompareRowEnd As Long
'Variables for page adjust scale size and adjustment font size
'***Currently only used for pageBreak Report.
Dim scaleSize As Long
Dim fontSize As Long
Dim Match As String
Dim m As Long
Dim Match2 As String
Dim MatchandCLear As String
' Code block to Bold Heading Section
Call BoldHeading
' Code block to Label sheet as being equal to the name of the file
ActiveSheet.Name = Mid(ActiveWorkbook.Name, 1, 30)
' Code block to Select all records above the current row and delete them
' These is default text EICC generates regarding the filters used etc.
' This is not required for the final reporting
' Range(ActiveCell, ActiveCell.End(xlUp)).EntireRow.Select
Range(ActiveCell.End(xlUp), ActiveCell.End(xlUp).End(xlUp)).EntireRow.Select
Selection.Delete Shift:=xlUp
If strReportType = "pageBreak" Then
scaleSize = 55
fontSize = 10 'twk 12-9-03 at Vicki's request changed from 12 to 10
' Call function to set page size to 60%
Call adjustPageFormat(scaleSize)
' Call function to set font size to [fontSize](12 or 10)
Call adjustPageFont(fontSize)
'Delete the total line
Call RemoveTotals
End If
' Code Block to AutoFit and Wrap text on all columns
Cells.Select
Cells.EntireColumn.AutoFit
Cells.VerticalAlignment = xlTop
Selection.WrapText = True
' Code block to handle the removal of sub-totals
' First it calls a function NbrActiveColumns to
' determine the number of active columns
ActiveColumns = NbrActiveColumns
'Call function to autoformat cells in the entire excel spreadsheet
'Call AutoFrmtCol
' Call function to run through all active columns and
'remove records that include the text 'subtotals' from the excel spreadsheet
' This if else statement is used to determine which column to start removing duplicates
' For 2 specific reports (Report 16 - DM.xls, Report 48 - Open Request.xls
' need the subtotal for the first attribute hence the code should start
' removing duplicates from the 3rd column onwards (i.e. SubtotalCol=3)
If strReportType = "subTotals" Or strReportType = "subTotPB" _
Or strReportType = "sort_subTot_PB" Or strReportType = "subTotal_RC" Then
SubtotalCol = 3
SearchStr1 = "Subtotal"
Else:
SearchStr1 = "Subtotal"
SubtotalCol = 2
End If
Do While SubtotalCol < ActiveColumns
If SubtotalCol = 1 Then
ColNm1 = "A"
Call RemoveSubtotal(ColNm1, SearchStr1, ActiveColumns, SubtotalCol)
ElseIf SubtotalCol = 2 Then
ColNm1 = "B"
Call RemoveSubtotal(ColNm1, SearchStr1, ActiveColumns, SubtotalCol)
ElseIf SubtotalCol = 3 Then
ColNm1 = "C"
Call RemoveSubtotal(ColNm1, SearchStr1, ActiveColumns, SubtotalCol)
ElseIf SubtotalCol = 4 Then
ColNm1 = "D"
If strReportType = "pageBreak" Then
Call RemoveSubtotalwPageBreak(ColNm1, SearchStr1, ActiveColumns, SubtotalCol)
Else: Call RemoveSubtotal(ColNm1, SearchStr1, ActiveColumns, SubtotalCol)
End If
ElseIf SubtotalCol = 5 Then
ColNm1 = "E"
Call RemoveSubtotal(ColNm1, SearchStr1, ActiveColumns, SubtotalCol)
ElseIf SubtotalCol = 6 Then
ColNm1 = "F"
Call RemoveSubtotal(ColNm1, SearchStr1, ActiveColumns, SubtotalCol)
ElseIf SubtotalCol = 7 Then
ColNm1 = "G"
Call RemoveSubtotal(ColNm1, SearchStr1, ActiveColumns, SubtotalCol)
ElseIf SubtotalCol = 8 Then
ColNm1 = "H"
Call RemoveSubtotal(ColNm1, SearchStr1, ActiveColumns, SubtotalCol)
End If
SubtotalCol = SubtotalCol + 1
Loop
' Code block does the final formatting of the report.
' Adds border around the table
' Adds Color to the Column Headings
Cells(1, "A").Select
Do While IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(-1, 0).Select
StartRow_ID = ActiveCell.Row
StartCol_ID = ActiveCell.Column
ActiveCell.Offset(1, 0).Select
ActiveCell.End(xlDown).Select
EndRow_ID = ActiveCell.Row
ActiveCell.End(xlToRight).Select
EndCol_ID = ActiveCell.Column
With Range(Cells(StartRow_ID, StartCol_ID), Cells(StartRow_ID + 1, EndCol_ID))
.BorderAround Weight:=xlMedium
.Interior.ColorIndex = 28
End With
Cells(StartRow_ID, StartCol_ID).EntireRow.Select
Range(Selection, Selection.Offset(1, 0)).EntireRow.Select
With ActiveSheet.PageSetup
.PrintTitleRows = Selection.Address ' Set rows for repeating
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.Orientation = xlLandscape ' Default page set up should be landscape
End With
'Block Code to perform sort. Currently sort on first column - If
' we want to sort by another column then we just need to add it here.
If strReportType = "sort_subTot_PB" Then
Call sortAsc(StartCol_ID)
End If
'Block Code to add correct formulas to subtotals and totals
If strReportType = "subTotals" Or strReportType = "subTotPB" _
Or strReportType = "sort_subTot_PB" Then
Call CalcSubtotal(EndCol_ID, strReportType)
ElseIf strReportType = "subTotal_RC" Then
Call CalcSubtotalRC(EndCol_ID, strReportType)
ElseIf strReportType = "regular" Then
Call VerifyTotals(EndCol_ID)
End If
' Block code to remove duplicate records
' The requirements for removing duplicates in an eicc generated report is the following
' Take the first record and place each cell into an array (ER1)
' Take the second record and place each cell into an array (ER2)
' Compare each cell in the first array (ER1) with each cell in the second array (ER2)
' If there is a match, place the value into the 3rd array (ER3)
' Once the comparison has been done, clear each cell identified in the 3rd array
' If there is not a match, move to the next row. This next row because the starting array
' and is placed into ER1. Again this process starts again where ER1 is compared
' with ER2.
' Start by selecting the cell at the start of the report (ie. upper border of the report)
Cells(StartRow_ID, StartCol_ID).Select
' Move down until the first non-bold cell is found
' This indicates the start of the data cells
Do While ActiveCell.Font.Bold = True
ActiveCell.Offset(1, 0).Select
Loop
' set StartResults Cells to be the active row
StartResultsRID = ActiveCell.Row
' Set variables to start search
SS1 = StartResultsRID
SS2 = StartResultsRID + 1
SS2P = SS2
'Code block to determine the start of the measures column
StartofMeasureCol = StartCol_ID
Cells(1, "A").Select
ActiveCell.Offset(StartRow_ID - 1, Start_ColID).Select
' block code to determine the column number at which the measures begin.
' Note: we do not want measures to be included when we analyze duplicates
Do While IsEmpty(ActiveCell)
If ActiveCell(Column) <= ActiveColumns Then
ActiveCell.Offset(0, 1).Select
StartofMeasureCol = StartofMeasureCol + 1
End If
Loop
' start at cell 1,A and move down to the start of the data cells
Cells(1, "A").Select
ActiveCell.Offset(StartResultsRID - 1, Start_ColID).Select
Dim r As Long ' used in for loop for starting row
Dim rr As Long ' used in loop for comparison row
Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long
Dim pageBreak As Boolean
' For Each Cell In Range(StartRow_ID, EndRow_ID)
' CompareRowEnd = 1
For r = 1 To EndRow_ID
CompareColEnd = ActiveColumns
If r > 1 Then
SS1 = CompareRowEnd
SS2 = SS1 + 1
SS2P = SS2 + 1
Cells(SS1, StartCol_ID).Select
Else
Cells(SS1, StartCol_ID).Select ' go to the start of the results section: startresultsId
End If
' place initial value of row, rowid, and colid into array
For i = 1 To StartofMeasureCol
ER1(1, i) = ActiveCell.Value
ER1(2, i) = ActiveCell.Row
ER1(3, i) = ActiveCell.Column
ActiveCell.Offset(0, 1).Select
Next i
For rr = 1 To EndRow_ID
If rr > 1 Then
Cells(SS2P + 1, StartCol_ID).Select
Else
Cells(SS2, StartCol_ID).Select
End If
' place value of row, rowid, and colid into array
For j = 1 To StartofMeasureCol
ER2(1, j) = ActiveCell.Value
ER2(2, j) = ActiveCell.Row
ER2(3, j) = ActiveCell.Column
ActiveCell.Offset(0, 1).Select
Next j
' Clear out array ER3
For m = 1 To StartofMeasureCol
ER3(1, m) = ""
ER3(2, m) = 0
ER3(3, m) = 0
Next m
For k = 1 To StartofMeasureCol
If (ER1(1, k) = ER2(1, k) And ER2(3, k) < CompareColEnd) Then
If ER1(3, k) < StartofMeasureCol Then
ER3(1, k) = ER2(1, k)
ER3(2, k) = ER2(2, k)
ER3(3, k) = ER2(3, k)
Match = "True"
SS2P = ER2(2, k)
End If
Else:
Match = "False"
If rr = 1 Then
If ER2(3, k) > CompareColEndP Then
CompareColEndP = ER2(3, k)
CompareColEnd = ER2(3, k)
CompareRowEnd = ER2(2, k)
Else
CompareColEnd = ER2(3, k)
CompareRowEnd = ER2(2, k)
End If
Else
CompareColEnd = ER2(3, k)
CompareRowEnd = ER2(2, k)
End If
For l = 1 To k - 1
If CompareColEnd = CompareColEndP Then
If l = k - 1 Then
If ER3(1, l + 1) = "" Then
Cells(ER3(2, l), ER3(3, l)).Select
pageBreak = Check_PageBreak
If pageBreak = False Then
Selection.Clear
End If
End If
Else
Cells(ER3(2, l), ER3(3, l)).Select
pageBreak = Check_PageBreak
If pageBreak = False Then
Selection.Clear
End If
End If
Else
l = k - 1
rr = EndRow_ID
End If
Next l
k = ActiveColumns
End If
Next k
If Match = "False" And CompareColEnd = 1 Then
rr = EndRow_ID
End If
Next rr
If CompareRowEnd > EndRow_ID Then
r = EndRow_ID
End If
Next r
' re-border after clearing duplicates
With Range(Cells(StartRow_ID, StartCol_ID), Cells(StartRow_ID + 1, EndCol_ID))
.BorderAround Weight:=xlMedium
.Interior.ColorIndex = 28
End With
' Code Block to AutoFit and Wrap text on all columns
'- Needs to be run twice to fit everything correctly.
Cells.Select
Cells.EntireColumn.AutoFit
Cells.VerticalAlignment = xlTop
Selection.WrapText = True
'Call function to autoformat 'Journal' cells an exact size
Call AutoFrmtCol
If strReportType = "subTotal_RC" Or strReportType = "pageBreak" Then
'Remove number or requests columnm
Call RemoveNbrRequests
End If
Cells(1, "A").Select
End Sub
' Block of code used to autoformat all cells in the spreadsheet
Public Function AutoFrmtCol()
Dim foundText As Range
Cells(1, "A").Select
Do While IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
Do While Not IsEmpty(ActiveCell)
'Initlize variables.
If InStr(1, ActiveCell, "Journal") Then
ActiveCell.EntireColumn.ColumnWidth = 75
End If
'Set to the next active cell
ActiveCell.Offset(0, 1).Select
Loop
End FunctionAny ideas how I can edit this macro to complete the job??
