Copy certain row to new sheet

[miracleyy]

Dear all,

i would like to copy each new order start from row "PH Order No:" to "0" to new sheet.
for below example, i would like to separate 4 order to 4 sheet.

Please help!!!

 

[miracleyy]

Actually it success in sheet 5 and 6 but fail in sheet 2 3 4 /.\

 

[Michael M]

OK, the image in post#1 and the data you posted in Post #4 are different( no blanks in post #4)...which do you want to use ??
There is a difference in writing the code for each one...

 

[miracleyy]

OK, the image in post#1 and the data you posted in Post #4 are different( no blanks in post #4)...which do you want to use ??
There is a difference in writing the code for each one...

It should be like below, and i would like to cut and copy the whole row not the cell only
Thanks again for you help!

 

[Michael M]

I can't access external images from where I am, hopefully someone else may be able to help.
The macro I provided previously should do the trick, but I don't understand this comment

Actually it success in sheet 5 and 6 but fail in sheet 2 3 4

if it copies data to one sheet, it should work for all

 

[miracleyy]

I can't access external images from where I am, hopefully someone else may be able to help.
The macro I provided previously should do the trick, but I don't understand this comment

if it copies data to one sheet, it should work for all

It did copy data to sheet 5 and 6
but sheet 2 3 4 are blank

 

[hiker95]

miracleyy,

Your reply #13 contains a picture/graphic.

You are posting a picture. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense.


So that we can get it right this next time:

1. can we have another screenshot of the raw data in worksheet Sheet1?

2. can we have screenshots of at least two of the other resulting worksheets, say worksheets 1, and 2?


To post your data, you can download and install one of the following two programs:

Excel Jeanie
Download

MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[hiker95]

miracleyy,

Sample raw data in worksheet Sheet1:

Excel 2007
	A	B	C	D	E	F	G	H
1	Pick No:	6767	Order No:	12	Customer Name:	abc	Store Name:	acb
2	Product Code	Picking Qty	no.	StorCond	InvType	StorCond	Locaion	Qty
3	product code 1	product name 1	1	Ambient	Normal	8X1	404-HLC	4
4	product code 2	product name 2	2	Ambient	Normal	30X1	512-HLC	58
5	product code 3	product name 3	3	Ambient	Normal	24X1	512-HLC	52
6	product code 4	product name 4	4	Ambient	Normal	24X1	5A01-02-040	8
7	product code 5	product name 5	5	Ambient	Normal	24X1	5A01-03-070	2
8	product code 6	product name 6	6	Ambient	Normal	12X10	5A02-05-020	10
9	product code 7	product name 7	7	Ambient	Normal	2X40	5A03-01-031	20
10	product code 8	product name 8	8	Ambient	Normal	12X1	5F02-01-032	3
11	product code 9	product name 9	9	Ambient	Normal	12X1	5F02-01-062	50
12			0					207
13	Pick No:	4545	Order No:	23	Customer Name:	abc	Store Name:	acb
14	Product Code	Picking Qty	no.	StorCond	InvType	StorCond	Locaion	Qty
15	product code 1	product name 1	1	Ambient	Normal	8X1	404-HLC	4
16	product code 2	product name 2	2	Ambient	Normal	30X1	512-HLC	58
17	product code 3	product name 3	3	Ambient	Normal	24X1	512-HLC	52
18			0					114
19	Pick No:	2442	Order No:	1123	Customer Name:	abc	Store Name:	acb
20	Product Code	Picking Qty	no.	StorCond	InvType	StorCond	Locaion	Qty
21	product code 1	product name 1	1	Ambient	Normal	8X1	404-HLC	4
22	product code 2	product name 2	2	Ambient	Normal	30X1	512-HLC	58
23	product code 3	product name 3	3	Ambient	Normal	24X1	512-HLC	52
24	product code 4	product name 4	4	Ambient	Normal	24X1	5A01-02-040	8
25	product code 5	product name 5	5	Ambient	Normal	24X1	5A01-03-070	2
26	product code 6	product name 6	6	Ambient	Normal	12X10	5A02-05-020	10
27			0					134
28	Pick No:	55555	Order No:	233	Customer Name:	abc	Store Name:	acb
29	Product Code	Picking Qty	no.	StorCond	InvType	StorCond	Locaion	Qty
30	product code 1	product name 1	1	Ambient	Normal	8X1	404-HLC	4
31	product code 2	product name 2	2	Ambient	Normal	30X1	512-HLC	58
32			0					62
33
Sheet1

After the macro in worksheets 1, and, 9 (not all sheets are shown):

Excel 2007
	A	B	C	D	E	F	G	H
1	Product Code	Picking Qty	no.	StorCond	InvType	StorCond	Locaion	Qty
2	product code 1	product name 1	1	Ambient	Normal	8X1	404-HLC	4
3	product code 1	product name 1	1	Ambient	Normal	8X1	404-HLC	4
4	product code 1	product name 1	1	Ambient	Normal	8X1	404-HLC	4
5	product code 1	product name 1	1	Ambient	Normal	8X1	404-HLC	4
6
1

Excel 2007
	A	B	C	D	E	F	G	H
1	Product Code	Picking Qty	no.	StorCond	InvType	StorCond	Locaion	Qty
2	product code 9	product name 9	9	Ambient	Normal	12X1	5F02-01-062	50
3
9

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).

Sub DistributeRows()
' hiker95, 06/22/2014, ME785238
Dim w1 As Worksheet, ws As Worksheet, w As String
Dim Area As Range, r As Long, sr As Long, er As Long, nr As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
For Each Area In w1.Range("B1", w1.Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    sr = .Row
    er = sr + .Rows.Count - 1
    For r = sr + 2 To er Step 1
      w = w1.Cells(r, 3).Value
      If Not WorksheetExists(w) Then
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = w
      End If
      Set ws = Sheets(w)
      ws.Cells(1, 1).Resize(, 8).Value = Array("Product Code", "Picking Qty", "no.", "StorCond", "InvType", "StorCond", "Locaion", "Qty")
      nr = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
      ws.Cells(nr, 1).Resize(, 8).Value = w1.Cells(r, 1).Resize(, 8).Value
      ws.Columns.AutoFit
    Next r
  End With
Next Area
w1.Activate
Application.ScreenUpdating = True
End Sub
Function WorksheetExists(w As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(w).Name = w
On Error GoTo 0
End Function

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

Then run the DistributeRows macro.

 

[miracleyy]

miracleyy,

Sample raw data in worksheet Sheet1:

Excel 2007

	A	B	C	D	E	F	G	H
1	Pick No:	6767	Order No:	12	Customer Name:	abc	Store Name:	acb
2	Product Code	Picking Qty	no.	StorCond	InvType	StorCond	Locaion	Qty
3	product code 1	product name 1	1	Ambient	Normal	8X1	404-HLC	4
4	product code 2	product name 2	2	Ambient	Normal	30X1	512-HLC	58
5	product code 3	product name 3	3	Ambient	Normal	24X1	512-HLC	52
6	product code 4	product name 4	4	Ambient	Normal	24X1	5A01-02-040	8
7	product code 5	product name 5	5	Ambient	Normal	24X1	5A01-03-070	2
8	product code 6	product name 6	6	Ambient	Normal	12X10	5A02-05-020	10
9	product code 7	product name 7	7	Ambient	Normal	2X40	5A03-01-031	20
10	product code 8	product name 8	8	Ambient	Normal	12X1	5F02-01-032	3
11	product code 9	product name 9	9	Ambient	Normal	12X1	5F02-01-062	50
12			0					207
13	Pick No:	4545	Order No:	23	Customer Name:	abc	Store Name:	acb
14	Product Code	Picking Qty	no.	StorCond	InvType	StorCond	Locaion	Qty
15	product code 1	product name 1	1	Ambient	Normal	8X1	404-HLC	4
16	product code 2	product name 2	2	Ambient	Normal	30X1	512-HLC	58
17	product code 3	product name 3	3	Ambient	Normal	24X1	512-HLC	52
18			0					114
19	Pick No:	2442	Order No:	1123	Customer Name:	abc	Store Name:	acb
20	Product Code	Picking Qty	no.	StorCond	InvType	StorCond	Locaion	Qty
21	product code 1	product name 1	1	Ambient	Normal	8X1	404-HLC	4
22	product code 2	product name 2	2	Ambient	Normal	30X1	512-HLC	58
23	product code 3	product name 3	3	Ambient	Normal	24X1	512-HLC	52
24	product code 4	product name 4	4	Ambient	Normal	24X1	5A01-02-040	8
25	product code 5	product name 5	5	Ambient	Normal	24X1	5A01-03-070	2
26	product code 6	product name 6	6	Ambient	Normal	12X10	5A02-05-020	10
27			0					134
28	Pick No:	55555	Order No:	233	Customer Name:	abc	Store Name:	acb
29	Product Code	Picking Qty	no.	StorCond	InvType	StorCond	Locaion	Qty
30	product code 1	product name 1	1	Ambient	Normal	8X1	404-HLC	4
31	product code 2	product name 2	2	Ambient	Normal	30X1	512-HLC	58
32			0					62
33

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

After the macro in worksheets 1, and, 9 (not all sheets are shown):

Excel 2007

	A	B	C	D	E	F	G	H
1	Product Code	Picking Qty	no.	StorCond	InvType	StorCond	Locaion	Qty
2	product code 1	product name 1	1	Ambient	Normal	8X1	404-HLC	4
3	product code 1	product name 1	1	Ambient	Normal	8X1	404-HLC	4
4	product code 1	product name 1	1	Ambient	Normal	8X1	404-HLC	4
5	product code 1	product name 1	1	Ambient	Normal	8X1	404-HLC	4
6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

1​

Excel 2007

	A	B	C	D	E	F	G	H
1	Product Code	Picking Qty	no.	StorCond	InvType	StorCond	Locaion	Qty
2	product code 9	product name 9	9	Ambient	Normal	12X1	5F02-01-062	50
3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

9​

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).

Sub DistributeRows()
' hiker95, 06/22/2014, ME785238
Dim w1 As Worksheet, ws As Worksheet, w As String
Dim Area As Range, r As Long, sr As Long, er As Long, nr As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
For Each Area In w1.Range("B1", w1.Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    sr = .Row
    er = sr + .Rows.Count - 1
    For r = sr + 2 To er Step 1
      w = w1.Cells(r, 3).Value
      If Not WorksheetExists(w) Then
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = w
      End If
      Set ws = Sheets(w)
      ws.Cells(1, 1).Resize(, 8).Value = Array("Product Code", "Picking Qty", "no.", "StorCond", "InvType", "StorCond", "Locaion", "Qty")
      nr = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
      ws.Cells(nr, 1).Resize(, 8).Value = w1.Cells(r, 1).Resize(, 8).Value
      ws.Columns.AutoFit
    Next r
  End With
Next Area
w1.Activate
Application.ScreenUpdating = True
End Sub
Function WorksheetExists(w As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(w).Name = w
On Error GoTo 0
End Function

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

Then run the DistributeRows macro.

Thank you!!!!
But wt i want is to distribute by the order not the product code.
Is there any ways to do it??
Thanks again for your help ><

 

[hiker95]

miracleyy,

QUOTES
1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow.


It is always best to display your actual raw data worksheet(s), and, the results that you are looking for. This way we can usually find a solution on the first go.

So that I can get it right this next time, I will have to see your actual workbook.

In your workbook I will need to see:
1. the raw data in its actual worksheet name
2. at least two of the resulting worksheets, with their actual worksheet names, and, manually formatted by you for the results you are looking for.


You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.


If you are not able to provide the above, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[miracleyy]

hiker95,

Thank you, here is the link

https://app.box.com/s/mrb04p8v6vzril34pme9