Copy certain row to new sheet

miracleyy

New Member
Joined
Dec 17, 2013
Messages
26
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!!!


21kkobd.jpg
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Can't access the image.
Try using the HTML Maker in my tag..to post a SMALL sample of your data, and what you need done !
 
Upvote 0
Can't access the image.
Try using the HTML Maker in my tag..to post a SMALL sample of your data, and what you need done !


sorry, i cannot download the HTML Maker

my sheet is like that (on 1 column)

Order No:
1
2
3

Order No.
1
2
3
4
5

Order No.
1
2

Order No.
1
2
3
4
5
6

and i would like to cut each order no. to a new sheet.
Thank you!!!
 
Upvote 0
MAybe this
Code:
Sub MM1()
Dim lr As Long, r As Long, x As Long, ns As Worksheet
Set ns = Sheets("Sheet1")
lr = ns.Cells(Rows.Count, "A").End(xlUp).Row
x = 0
For r = 2 To lr
    If ns.Range("A" & r).Value <> "" Then x = x + 1
        If ns.Range("A" & r).Value = "" Then
            Sheets.Add After:=Sheets(Worksheets.Count)
            ns.Rows(r - x & ":" & r).Copy
            ActiveSheet.Paste
            x = 0
        End If
Next r
End Sub
 
Upvote 0
miracleyy,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


So that we can get it right this next time:

How about posting your workbook/worksheets:
1. With the raw data in its actual worksheet name
2. And, the resulting worksheets 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.
 
Last edited:
Upvote 0
MAybe this
Code:
Sub MM1()
Dim lr As Long, r As Long, x As Long, ns As Worksheet
Set ns = Sheets("Sheet1")
lr = ns.Cells(Rows.Count, "A").End(xlUp).Row
x = 0
For r = 2 To lr
    If ns.Range("A" & r).Value <> "" Then x = x + 1
        If ns.Range("A" & r).Value = "" Then
            Sheets.Add After:=Sheets(Worksheets.Count)
            ns.Rows(r - x & ":" & r).Copy
            ActiveSheet.Paste
            x = 0
        End If
Next r
End Sub


Thank you!!
But it did not work. No response.
 
Upvote 0
Are you copying from the sheet named "Sheet1" AND Column "1" ??
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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