VBA Macros: Creating New Worksheets and Deleting Usless Data

P

pokerman

New Member
Joined
Aug 15, 2011
Messages
4
To any one smarter than me,

I'm in a bit over my head with this and would really appreciate some help.

Here's a copy of my data.

What needs to happen is that each product (by location) needs to be put on its own separate worksheet, preferably with each worksheet named with the correct location and product. For example "CHI B99588" for the first one.

To complicate things, in the "Ing." Column every row of data after and including the X0002 is useless information and needs to be deleted.

The position of the X0002 varies as well as the number of rows that make up each product.

<table width="384" border="0" cellpadding="0" cellspacing="0"> <col style="width: 48pt;" width="64" span="6"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">Location</td> <td style="width: 48pt;" width="64">Product</td> <td style="width: 48pt;" width="64">Volume</td> <td style="width: 48pt;" width="64">Sequence</td> <td style="width: 48pt;" width="64">Ing.</td> <td style="width: 48pt;" width="64">lbs</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">1</td> <td>B3558</td> <td align="right">900</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">2</td> <td align="right">21067</td> <td align="right">800</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">3</td> <td>D6152</td> <td align="right">295</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">4</td> <td align="right">21097</td> <td align="right">206</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">5</td> <td align="right">22068</td> <td align="right">115</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">6</td> <td align="right">22073</td> <td align="right">63.5</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">7</td> <td align="right">22187</td> <td align="right">63.5</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">8</td> <td>X0002</td> <td align="right">50</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">9</td> <td align="right">23253</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">10</td> <td align="right">24376</td> <td align="right">0</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">11</td> <td align="right">23052</td> <td align="right">0.02</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">12</td> <td align="right">24350</td> <td align="right">0.02</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">13</td> <td align="right">43127</td> <td align="right">1.16</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B97441</td> <td align="right">15000</td> <td align="right">1</td> <td align="right">21067</td> <td align="right">2165</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B97441</td> <td align="right">15000</td> <td align="right">2</td> <td align="right">22068</td> <td align="right">221</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B97441</td> <td align="right">15000</td> <td align="right">3</td> <td align="right">21097</td> <td align="right">100</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B97441</td> <td align="right">15000</td> <td align="right">4</td> <td align="right">22060</td> <td align="right">15</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B97441</td> <td align="right">15000</td> <td align="right">5</td> <td>I5097</td> <td align="right">11</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B97441</td> <td align="right">15000</td> <td align="right">6</td> <td align="right">22061</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B97441</td> <td align="right">15000</td> <td align="right">7</td> <td>X0002</td> <td align="right">50</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B97441</td> <td align="right">15000</td> <td align="right">8</td> <td align="right">23253</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B97441</td> <td align="right">15000</td> <td align="right">9</td> <td align="right">24376</td> <td align="right">0</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B97441</td> <td align="right">15000</td> <td align="right">10</td> <td align="right">23052</td> <td align="right">0.02</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B97441</td> <td align="right">15000</td> <td align="right">11</td> <td align="right">23053</td> <td align="right">0.02</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B97441</td> <td align="right">15000</td> <td align="right">12</td> <td align="right">43127</td> <td align="right">1.1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">MEM</td> <td>B97556</td> <td align="right">30100</td> <td align="right">1</td> <td align="right">21067</td> <td align="right">2080</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">MEM</td> <td>B97556</td> <td align="right">30100</td> <td align="right">2</td> <td align="right">22067</td> <td align="right">80</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">MEM</td> <td>B97556</td> <td align="right">30100</td> <td align="right">3</td> <td align="right">22093</td> <td align="right">50</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">MEM</td> <td>B97556</td> <td align="right">30100</td> <td align="right">4</td> <td align="right">22116</td> <td align="right">50</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">MEM</td> <td>B97556</td> <td align="right">30100</td> <td align="right">5</td> <td align="right">22980</td> <td align="right">5</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">MEM</td> <td>B97556</td> <td align="right">30100</td> <td align="right">6</td> <td align="right">97555</td> <td align="right">248</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">MEM</td> <td>B97556</td> <td align="right">30100</td> <td align="right">7</td> <td>X0002</td> <td align="right">50</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">MEM</td> <td>B97556</td> <td align="right">30100</td> <td align="right">8</td> <td align="right">23253</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">MEM</td> <td>B97556</td> <td align="right">30100</td> <td align="right">9</td> <td align="right">24476</td> <td align="right">0.01</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">MEM</td> <td>B97556</td> <td align="right">30100</td> <td align="right">10</td> <td align="right">23053</td> <td align="right">0.02</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">MEM</td> <td>B97556</td> <td align="right">30100</td> <td align="right">11</td> <td align="right">23053</td> <td align="right">0.02</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">MEM</td> <td>B97556</td> <td align="right">30100</td> <td align="right">12</td> <td align="right">43127</td> <td align="right">1.1
</td> </tr> </tbody> </table>

Thus, the final product in a new worksheet named "CHI B99588" would be:

<table style="width: 384px; height: 163px;" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 15pt;" height="20"><td> <table width="384" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">1</td> <td>B3558</td> <td align="right">900</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">2</td> <td align="right">21067</td> <td align="right">800</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">3</td> <td>D6152</td> <td align="right">295</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">4</td> <td align="right">21097</td> <td align="right">206</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">5</td> <td align="right">22068</td> <td align="right">115</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">6</td> <td align="right">22073</td> <td align="right">63.5</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CHI</td> <td>B99588</td> <td align="right">12600</td> <td align="right">7</td> <td align="right">22187</td> <td align="right">63.5</td></tr></tbody> </table> </td><td align="right">
</td><td align="right">
</td><td>
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td><td>
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td><td>
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr></tbody> </table> Please help. I have over 14,000 rows of data so any help is greatly appreciate it so I don't have to do these individually.

Thanks
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Any ideas? Was this not clear? I'm just not sure how to delete the useless data since the length is varying...
 
Upvote 0
i did not understand your problem? do you want to find unique values for some row criterions or what, making some sums.
if you want to delete unwanted content use this link, or if you want to sum something you can use pivot table

wish you the best solution
 
Upvote 0
Welcome to the MrExcel board!

Try this in a copy of your workbook.

Notes:
1. You will need to change the sheet name to match the name that your main data is on.

2. The code assumes Excel 2007+. If you are using an earlier version then change the 'Const f as String' line near the top of the code to the one that is currently commented out.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Create_Sheets()<br>    <SPAN style="color:#00007F">Dim</SPAN> Cols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range, Loc <SPAN style="color:#00007F">As</SPAN> Range, Crit <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> Nm <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#007F00">'Use this line for Excel 2007+</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> f <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = _<br>        "=AND(A2=#,B2=^,COUNTIFS(A$1:A2,#,B$1:B2,^,E$1:E2,""X0002"")=0)"<br>    <br>    <SPAN style="color:#007F00">'Use this line for Excel 2003-</SPAN><br><SPAN style="color:#007F00">'    Const f As String = _<br>'        "=AND(A2=#,B2=^,SUMPRODUCT(--(A$1:A2=#),--(B$1:B2=^),--(E$1:E2=""X0002""))=0)"</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Data")           <SPAN style="color:#007F00">'<-- Change this to your main data sheet name</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> .Range("A1").CurrentRegion<br>            Cols = .Columns.Count<br>                .Resize(, 2).AdvancedFilter Action:=xlFilterCopy, _<br>                    CopyToRange:=.Cells(1, 1).Offset(, Cols + 3).Resize(, 2), _<br>                    Unique:=<SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> .Parent<br>                <SPAN style="color:#00007F">Set</SPAN> Loc = .Range(.Cells(2, Cols + 4), _<br>                    .Cells(.Rows.Count, Cols + 4).End(xlUp))<br>                <SPAN style="color:#00007F">Set</SPAN> Crit = .Cells(1, Cols + 2).Resize(2)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Loc<br>                Crit.Cells(2, 1).Formula = _<br>                    Replace(Replace(f, "#", c.Address), "^", c.Offset(, 1).Address)<br>                Nm = c.Value & " " & c.Offset(, 1).Value<br>                <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>                Sheets(Nm).Delete<br>                <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>                Sheets.Add(After:=Sheets(Sheets.Count)).Name = Nm<br>                .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Crit, _<br>                    CopyToRange:=Sheets(Nm).Range("A1"), Unique:=<SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> c<br>            Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Crit.ClearContents<br>    Loc.CurrentRegion.ClearContents<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Last edited:
Upvote 0
Brilliant!

Thank you so much.

I wish I could learn more about how you actually did that! I'm surprised you used an advanced filter action. I'm still not sure how that allowed you to copy and paste each product and location... I would have thought you would have had to used a loop or something.

Seriously awesome though. Do you mind me asking how you learned? I'm really interested in learning more myself and wouldn't mind some helpful tips... if you have any.

But Thanks again. Will save me so much time.
 
Upvote 0
pokerman said:
I'm surprised you used an advanced filter action. I'm still not sure how that allowed you to copy and paste each product and location... I would have thought you would have had to used a loop or something.
Click to expand...
The code uses Advanced Filter in 2 different ways.

First, it filters columns A:B for unique (combinations of) values and puts the results in columns J:K (assuming the original data is in columns A:F). For your sample data that produces the following:

Excel Workbook
JK
1LocationProduct
2CHIB99588
3CHIB97441
4MEMB97556
5
Data




Then there is in fact a loop in my code (For each c in Loc) that filters each set of data off to its own sheet using the formula base in the "Const f as String = ..." line with each combination from the above unique combination list. With the first combination for your sample data, the Advanced Filter criteria range of H1:H2 looks like this:

Excel Workbook
H
1
2TRUE
Data




I suggest that you put your cursor somehere in the code and use thye F8 key to step through a line at a time and keep looking at the relevant sheets to see what the step has done. That may help you understand what is happening.


pokerman said:
Do you mind me asking how you learned? I'm really interested in learning more myself and wouldn't mind some helpful tips... if you have any.
Click to expand...
I have done most of my learning from this forum and trial & error. However, member hiker95 has compiled quite a comprehensive list of resources that may be some use to you:
http://www.mrexcel.com/forum/showpost.php?p=2830517&postcount=2
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
M
  • Question Question
Modified code not working, as expected
2
Replies
15
Views
2K
Mr2017
M
K
  • Question Question
Need VBA help Cut and paste in to another locations if data and condition matches.
Replies
1
Views
789
Kishan
K
A
  • Question Question
Worksheet Display Not Updating Through Loop Process
Replies
3
Views
778
Ark68
A

Forum statistics

Threads
1,224,594
Messages
6,179,794
Members
452,943
Latest member
Newbie4296

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top