Help separating data based on one column

[davidmichael99]

I am having issues separating data from one worksheet to multiple worksheets.
DATA: Entering product code onto a master sheet in column A with test results in column B, C, & D.

Example: Master sheet[TABLE="class: grid, width: 100"]
<TBODY>[TR]
[TD]Product[/TD]
[TD]Test1[/TD]
[TD]Test2[/TD]
[TD]Test3[/TD]
[/TR]
[TR]
[TD]1540[/TD]
[TD]50[/TD]
[TD]55[/TD]
[TD]58[/TD]
[/TR]
[TR]
[TD]2020[/TD]
[TD]19[/TD]
[TD]25[/TD]
[TD]74[/TD]
[/TR]
[TR]
[TD]1540[/TD]
[TD]51[/TD]
[TD]54[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]2021[/TD]
[TD]85[/TD]
[TD]45[/TD]
[TD]17[/TD]
[/TR]
</TBODY>[/TABLE]


Result: After new product code is entered for that day onto master sheet I would like to press a button that would update the sheet and separate the data into multiple worksheets based on the product code. A sheet for 1540, 2040, and 2021.

Results: Sheet 1540[TABLE="class: grid, width: 100"]
<TBODY>[TR]
[TD]Product[/TD]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]Test[/TD]
[/TR]
[TR]
[TD]1540[/TD]
[TD]50[/TD]
[TD]55[/TD]
[TD]58[/TD]
[/TR]
[TR]
[TD]1540[/TD]
[TD]51[/TD]
[TD]54[/TD]
[TD]55[/TD]
[/TR]
</TBODY>[/TABLE]


Is this possible to accomplish with excel functions, macro, or would I need VBA? Which would be the best way to approach this issue and where should I start? I have searched to net for examples but Im not really coming up with any results. Maybe Im just not searching for the right keywords. </SPAN>

 

[hiker95]

davidmichael99,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?


Check out the below link, reply #3:

By Norie
DistributeRows
Copy row content to another sheet

 

[wigi]

Hi, welcome to MrExcel.

Maybe Im just not searching for the right keywords.

I think so, yes, because there are plenty of topics on that. Or using Google:
https://sites.google.com/a/madrocke...celassistant/parse-functions/sheet1-to-sheets

Is this possible to accomplish with excel functions, macro, or would I need <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>?

What is the difference between the last 2?

 

[davidmichael99]

windows 7
excel 2010

 

[hiker95]

davidmichael99,

Sample raw data in worksheet Master, before, and, after the macro:

Excel 2007
	A	B	C	D
1	Product	Test1	Test2	Test3
2	1540	50	55	58
3	2020	19	25	74
4	1540	51	54	55
5	2021	85	45	17
6
Master

After the macro, three worksheets are created: 1540, 2020, and, 2021:

Excel 2007
	A	B	C	D
1	Product	Test1	Test2	Test3
2	1540	50	55	58
3	1540	51	54	55
4
1540

Excel 2007
	A	B	C	D
1	Product	Test1	Test2	Test3
2	2020	19	25	74
3
2020

Excel 2007
	A	B	C	D
1	Product	Test1	Test2	Test3
2	2021	85	45	17
3
2021

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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub DistributeRows()
' hiker95, 11/01/2013
' http://www.mrexcel.com/forum/excel-questions/736582-help-separating-data-based-one-column.html
' The below macro code has been modified to reflect davidmichael99's thread request.
' Original macro code by Norie, 04/19/2008
' http://www.mrexcel.com/forum/excel-questions/315083-copy-row-content-another-sheet.html
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("Master")
LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row
Set wsCrit = Worksheets.Add
wsAll.Range("A1:A" & 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
wsAll.Activate
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 DistributeRows macro.

 

[davidmichael99]

Thank you so much! This is what I needed!!!

 

[hiker95]

davidmichael99,

You are very welcome. Glad I could help.

Thanks for the feedback.

And, come back anytime.