VBA Code Required

[hrayani]

Hello Friends,

I want to copy specific data in specific format from one worksheet to another (both worksheet in the same workbook)

Here is the short sample data

Running Orders New Style.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
2	PO #	REF #	PO DATE	Customer	Supplier	Article	Quality	Dyed or Printed	Fiber Content	Construction	SIZE	QTY	UNIT	PO SHIP DATE	ACTUAL SHIP DATE	REMARKS	SAMPLING	PO # CONCAT	ARTICLE CONCAT	EXTRA COLUMN	VALUE	STATUS
3	377421	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Disperse Print	100% Polyester	75D X 200D 107X66	Normal	143,760	Set(s)	2-May-22			OCT - 2021	377421	Bed Set		$699,576	In Process
4	377422	633	29-Dec-21	TEX 1	Mills	Pillow Pair	Microfiber Satin - 100 Gsm	Disperse Dyed + Dispers Print	100% Polyester	75D X 200D 107X66	Multiple	233,544	Pair(s)	2-May-22			OCT - 2021	377421 - 377422	Bed Set - Pillow Pair		$452,181	In Process
5	377423	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Disperse Print	100% Polyester	75D X 200D 107X66	Over	94,240	Set(s)	2-May-22			OCT - 2021	377421 - 377422 - 377423	Bed Set - Pillow Pair		$673,612	In Process
6	377424	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Disperse Print	100% Polyester	75D X 200D 107X66	King	37,110	Set(s)	2-May-22			OCT - 2021	377421 - 377422 - 377423 - 377424	Bed Set - Pillow Pair		$309,762	In Process
7	377425	633	29-Dec-21	TEX 1	Mills	Side Pillow	Microfiber Satin - 100 Gsm	Disperse Dyed + Dispers Print	100% Polyester	75D X 200D 107X66	40x145	176,532	Pc(s)	2-May-22			OCT - 2021	377421 - 377422 - 377423 - 377424 - 377425	Bed Set - Pillow Pair - Side Pillow		$307,166	In Process
8	980154	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Disperse Print	100% Polyester	75D X 200D 107X66	Normal	59,976	Set(s)	6-Jun-22			OCT - 2021	980154	Bed Set		$249,122	In Process
9	980155	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Disperse Print	100% Polyester	75D X 200D 107X66	King	25,410	Set(s)	6-Jun-22			OCT - 2021	980154 - 980155	Bed Set		$170,777	In Process
10	980156	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Disperse Print	100% Polyester	75D X 200D 107X66	Over	47,976	Set(s)	6-Jun-22			OCT - 2021	980154 - 980155 - 980156	Bed Set		$254,075	In Process
11	980157	634	10-Dec-21	TEX 1	Mills	Pillow Pair	Microfiber Satin - 100 Gsm	Disperse Print	100% Polyester	75D X 200D 107X66	Multiple	51,240	Pair(s)	6-Jun-22			OCT - 2021	980154 - 980155 - 980156 - 980157	Bed Set - Pillow Pair		$ 41,313	In Process
12	123456	635	10-Dec-21	ROS 2	Usman	Fitted Sheet	CTN Jersey - 135 Gsm	Reactive Dyed	100% Cotton	30'S Cotton	100x200	3,920	Pc(s)	13-Feb-22		Best Price & Easy Sleep - Contract # 21176967		123456	Fitted Sheet		$ 14,896	Shipped
13	123457	635	10-Dec-21	ROS 2	Usman	Fitted Sheet	CTN Jersey - 135 Gsm	Reactive Dyed	100% Cotton	30'S Cotton	150x200	2,400	Pc(s)	13-Feb-22		Best Price & Easy Sleep - Contract # 21176967		123456 - 123457	Fitted Sheet		$ 12,960	Shipped
14	229988	636	14-Dec-21	LOS	Fabrics	Sheet Set	CTN Renforce - 100 Gsm	Pigment Printed Rotary	100% Cotton	30X30 / 76X50	Single	1,416	Set(s)	28-Feb-22				229988	Sheet Set		$ 14,168	In Process
15	229900	636	14-Dec-21	LOS	Fabrics	Sheet Set	CTN Renforce - 100 Gsm	Pigment Printed Rotary	100% Cotton	30X30 / 76X50	Single	3,456	Set(s)	28-Feb-22				229988 - 229900	Sheet Set		$ 41,437	In Process
16	403175	637	21-Dec-21	OFF	Towel	Terry Beach Towel	CTN 320 GSM	Reactive Print	100% Cotton		70x150 cm	60,160	Pc(s)	18-Apr-22		Amount in euro increased to 13%		403175	Terry Beach Towel		$205,982	In Process
17	403176	637	21-Dec-21	OFF	Towel	Poncho	CTN 320 GSM	Reactive Print	100% Cotton		60x120 cm	56,092	Pc(s)	18-Apr-22		Amount in euro increased to 13%		403175 - 403176	Terry Beach Towel - Poncho		$205,998	In Process
18	310088	638	7-Jan-22	OFF	Towel	Terry Towel	CTN 385 GSM	Reactive Dyed	100% Cotton		50x100 cm (2 Pc Set)	142,566	Set(s)	10-Jun-22		Young Living ▬ Amount in euro increased to 14%		310088	Terry Towel		$367,307	Shipped
19	310089	638	7-Jan-22	OFF	Towel	Terry Towel	CTN 385 GSM	Reactive Dyed	100% Cotton		70x140 cm	124,382	Pc(s)	10-Jun-22		Young Living ▬ Amount in euro increased to 14%		310088 - 310089	Terry Towel		$314,786	Shipped
20	567891	639	12-Jan-22	ROS 2	Usman	Fitted Sheet	CTN Jersey - 135 Gsm	Reactive Dyed	100% Cotton	30'S Cotton	100x200	4,080	Pc(s)	13-May-22		Best Price & Easy Sleep - Contract # 21176967		567891	Fitted Sheet		$ 15,504	Shipped
21	567892	639	12-Jan-22	ROS 2	Usman	Fitted Sheet	CTN Jersey - 135 Gsm	Reactive Dyed	100% Cotton	30'S Cotton	150x200	2,220	Pc(s)	13-May-22		Best Price & Easy Sleep - Contract # 21176967		567891 - 567892	Fitted Sheet		$ 11,988	Shipped
22	129875	640	12-Jan-22	ROS 2	Usman	Fitted Sheet	CTN Jersey - 135 Gsm	Reactive Dyed	100% Cotton	30'S Cotton	100x200	3,840	Pc(s)	1-Jun-22		Best Price & Easy Sleep - Contract # 21176967		129875	Fitted Sheet		$ 14,592	Shipped
23	129876	640	12-Jan-22	ROS 2	Usman	Fitted Sheet	CTN Jersey - 135 Gsm	Reactive Dyed	100% Cotton	30'S Cotton	150x200	2,400	Pc(s)	1-Jun-22		Best Price & Easy Sleep - Contract # 21176967		129875 - 129876	Fitted Sheet		$ 12,960	Shipped
ORDERS

Cell Formulas
Range	Formula
R3:R23	R3	=IF(ISBLANK(B3),"",IF(B3<>B2,A3,IF(ISNUMBER(SEARCH(A3,R2)),R2,R2&" - "&A3)))
S3:S23	S3	=IF(ISBLANK(B3),"",IF(B3<>B2,F3,IF(ISNUMBER(SEARCH(F3,S2)),S2,S2&" - "&F3)))

Sheet 1: Copy from Sheet ORDERS
Sheet2: Copy to Sheet RUNNING ORDER STATUS

I want the data to copied to sheet 2 with following conditions

1) Copy all rows showing "In Process" in Column V from sheet 1 to sheet 2 starting from cell B4
Columns to copy would be A,B,C,D,E,F,G,K,L,M,N & P (Total 12 Columns)

2) Sort Data - 1st by Customer in Alphabetical order then by Ascending order PO ship date

3) Insert a Line when a Ref # changes
a) On the inserted Line: Show 1st instance for columns PO #, REF #, PO Date, Customer, Supplier, Article, Quality, PO Shape Date Columns
b) On the inserted Line: Show "Multiple" if more than one entry is found for Size, Unit & Remarks Columns else show 1st Instance
c) On the inserted Line: Show Total for the Qty Column

4) In Column A add value 1 to all the copied rows & value 2 to all the inserted rows (This is because I will be filtering records further)

5) Content Banding When a Ref # Changes

6) Dark grey color to the Inserted Rows

So, the final look will be like this

Running Orders New Style.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
4		PO #	REF #	PO DATE	Customer	Supplier	Article	Quality	SIZE	QTY	UNIT	PO SHIP DATE	REMARKS
5	1	377421	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Normal	143,760	Set(s)	2-May-22
6	1	377422	633	29-Dec-21	TEX 1	Mills	Pillow Pair	Microfiber Satin - 100 Gsm	Multiple	233,544	Pair(s)	2-May-22
7	1	377423	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Over	94,240	Set(s)	2-May-22
8	1	377424	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	King	37,110	Set(s)	2-May-22
9	1	377425	633	29-Dec-21	TEX 1	Mills	Side Pillow	Microfiber Satin - 100 Gsm	40x145	176,532	Pc(s)	2-May-22
10	2	377421	633	29-Dec-22	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Multiple	685,186	Multiple	2-May-22
11	1	980154	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Normal	59,976	Set(s)	6-Jun-22
12	1	980155	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	King	25,410	Set(s)	6-Jun-22
13	1	980156	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Over	47,976	Set(s)	6-Jun-22
14	1	980157	634	10-Dec-21	TEX 1	Mills	Pillow Pair	Microfiber Satin - 100 Gsm	Multiple	51,240	Pair(s)	6-Jun-22
15	2	980154	634	10-Dec-22	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Multiple	184,602	Multiple	6-Jun-22
16	1	229988	636	14-Dec-21	LOS	Fabrics	Sheet Set	CTN Renforce - 100 Gsm	Single	1,416	Set(s)	28-Feb-22
17	1	229900	636	14-Dec-21	LOS	Fabrics	Sheet Set	CTN Renforce - 100 Gsm	Single	3,456	Set(s)	28-Feb-22
18	2	229988	636	14-Dec-22	LOS	Fabrics	Sheet Set	CTN Renforce - 100 Gsm	Single	4,872	Set(s)	28-Feb-22
19	1	403175	637	21-Dec-21	OFF	Towel	Terry Beach Towel	CTN 320 GSM	70x150 cm	60,160	Pc(s)	18-Apr-22	Amount in euro increased to 13%
20	1	403176	637	21-Dec-21	OFF	Towel	Poncho	CTN 320 GSM	60x120 cm	56,092	Pc(s)	18-Apr-22	Amount in euro increased to 13%
21	2	403175	637	21-Dec-22	OFF	Towel	Terry Beach Towel	CTN 320 GSM	Multiple	116,252	Pc(s)	18-Apr-22
22	1	377421	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Normal	143,760	Set(s)	2-May-22
23	1	377422	633	29-Dec-21	TEX 1	Mills	Pillow Pair	Microfiber Satin - 100 Gsm	Multiple	233,544	Pair(s)	2-May-22
24	1	377423	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Over	94,240	Set(s)	2-May-22
25	1	377424	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	King	37,110	Set(s)	2-May-22
26	1	377425	633	29-Dec-21	TEX 1	Mills	Side Pillow	Microfiber Satin - 100 Gsm	40x145	176,532	Pc(s)	2-May-22
27	2	377421	633	29-Dec-22	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Multiple	685,186	Multiple	2-May-22
28	1	980154	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Normal	59,976	Set(s)	6-Jun-22
29	1	980155	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	King	25,410	Set(s)	6-Jun-22
30	1	980156	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Over	47,976	Set(s)	6-Jun-22
31	1	980157	634	10-Dec-21	TEX 1	Mills	Pillow Pair	Microfiber Satin - 100 Gsm	Multiple	51,240	Pair(s)	6-Jun-22
32	2	980154	634	10-Dec-22	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Multiple	184,602	Multiple	6-Jun-22
RUNNING ORDER STATUS

Any help would be appreciated,

Regards,

Humayun

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, rng As Range, x As Long, i As Long, fRow As Long, lRow As Long, y As Long: y = 19
    Set srcWS = Sheets("ORDERS")
    Set desWS = Sheets("RUNNING ORDER STATUS")
    With srcWS
        .Range("A1").CurrentRegion.AutoFilter 22, "In Process"
        Intersect(.AutoFilter.Range.Offset(1), .Range("A:G,K:N,P:P")).Copy desWS.Cells(desWS.Rows.Count, "B").End(xlUp).Offset(1)
        .Range("A1").AutoFilter
    End With
    LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With desWS.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("E2:E" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=Range("L2:L" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("B1:M" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    For x = LastRow To 3 Step -1
        With desWS
            If .Cells(x, 3) <> .Cells(x - 1, 3) Then
                .Rows(x).EntireRow.Insert
            End If
        End With
    Next x
    LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With desWS.Range("B2:B" & LastRow).SpecialCells(xlCellTypeConstants)
        For i = 1 To .Areas.Count
            fRow = .Areas(i).Cells(1).Row
            lRow = .Areas(i).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            With desWS
                .Range("B" & lRow + 1 & ":M" & lRow + 1).Value = .Range("B" & fRow & ":M" & fRow).Value
                .Range("J" & lRow + 1).Value = WorksheetFunction.Sum(.Range("J" & fRow & ":J" & lRow))
                .Range("A" & fRow & ":A" & lRow) = 1
                .Range("A" & fRow & ":M" & lRow).Interior.ColorIndex = y
                .Range("A" & lRow + 1) = 2
                .Range("A" & lRow + 1 & ":M" & lRow + 1).Interior.ColorIndex = 15
                Set rng = .Range("I" & fRow & ":I" & lRow)
                If rng.Count = WorksheetFunction.CountIf(rng, rng(1)) Then
                    .Range("I" & lRow + 1) = rng(1)
                Else
                    .Range("I" & lRow + 1) = "Multiple"
                End If
                Set rng = .Range("K" & fRow & ":K" & lRow)
                If rng.Count = WorksheetFunction.CountIf(rng, rng(1)) Then
                    .Range("K" & lRow + 1) = rng(1)
                Else
                    .Range("K" & lRow + 1) = "Multiple"
                End If
                If y = 19 Then
                    y = 20
                Else
                    y = 19
                End If
            End With
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

 

[hrayani]

Hi Mumps,

First of all many thanks for the code. I am almost there

Just few things I noticed

1) It starts pasting from Row B2... I request it to change it to B4
2) When I re-run the code it just overlaps on the existing data. So idea is to erase all the existing data (cells colors also) & start from scratch
3) It does not copy the Headings. Please tell the code to copy the headings also
4) The Remarks column is showing the 1st instance, whereas I want it to write Multiple just like the Size Column else 1st instance if there are multiple entries
5) Strange thing I noticed is, the format of the last row of the table. Pls see below red fonts

Running Orders New Style.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
8	1	377421	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Normal	143,760	Set(s)	2-May-22
9	1	377422	633	29-Dec-21	TEX 1	Mills	Pillow Pair	Microfiber Satin - 100 Gsm	Multiple	233,544	Pair(s)	2-May-22
10	1	377423	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Over	94,240	Set(s)	2-May-22
11	1	377424	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	King	37,110	Set(s)	2-May-22
12	1	377425	633	29-Dec-21	TEX 1	Mills	Side Pillow	Microfiber Satin - 100 Gsm	40x145	176,532	Pc(s)	2-May-22
13	2	377421	633	29-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Multiple	685,186	Multiple	2-May-22
14	1	980154	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Normal	59,976	Set(s)	6-Jun-22
15	1	980155	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	King	25,410	Set(s)	6-Jun-22
16	1	980156	634	10-Dec-21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Over	47,976	Set(s)	6-Jun-22
17	1	980157	634	10-Dec-21	TEX 1	Mills	Pillow Pair	Microfiber Satin - 100 Gsm	Multiple	51,240	Pair(s)	6-Jun-22
18	2	980154	634	10 12 21	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Multiple	184602	Multiple	06 06 22
RUNNING ORDER STATUS

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, rng As Range, x As Long, i As Long, fRow As Long, lRow As Long, y As Long: y = 19
    Set srcWS = Sheets("ORDERS")
    Set desWS = Sheets("RUNNING ORDER STATUS")
    desWS.UsedRange.Rows.Delete
    With srcWS
        .Range("A1").CurrentRegion.AutoFilter 22, "In Process"
        Intersect(.AutoFilter.Range, .Range("A:G,K:N,P:P")).Copy desWS.Range("B3")
        .Range("A1").AutoFilter
    End With
    LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With desWS.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("E3:E" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=Range("L3:L" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("B3:M" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    For x = LastRow To 5 Step -1
        With desWS
            If .Cells(x, 3) <> .Cells(x - 1, 3) Then
                .Rows(x).EntireRow.Insert
            End If
        End With
    Next x
    LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With desWS.Range("B4:B" & LastRow).SpecialCells(xlCellTypeConstants)
        For i = 1 To .Areas.Count
            fRow = .Areas(i).Cells(1).Row
            lRow = .Areas(i).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            With desWS
                .Range("B" & lRow + 1 & ":M" & lRow + 1).Value = .Range("B" & fRow & ":M" & fRow).Value
                .Range("J" & lRow + 1).Value = WorksheetFunction.Sum(.Range("J" & fRow & ":J" & lRow))
                .Range("A" & fRow & ":A" & lRow) = 1
                .Range("A" & fRow & ":M" & lRow).Interior.ColorIndex = y
                .Range("A" & lRow + 1) = 2
                .Range("A" & lRow + 1 & ":M" & lRow + 1).Interior.ColorIndex = 15
                Set rng = .Range("I" & fRow & ":I" & lRow)
                If rng.Count = WorksheetFunction.CountIf(rng, rng(1)) Then
                    .Range("I" & lRow + 1) = rng(1)
                Else
                    .Range("I" & lRow + 1) = "Multiple"
                End If
                Set rng = .Range("K" & fRow & ":K" & lRow)
                If rng.Count = WorksheetFunction.CountIf(rng, rng(1)) Then
                    .Range("K" & lRow + 1) = rng(1)
                Else
                    .Range("K" & lRow + 1) = "Multiple"
                End If
                Set rng = .Range("M" & fRow & ":M" & lRow)
                If rng.Count = WorksheetFunction.CountIf(rng, rng(1)) Then
                    .Range("M" & lRow + 1) = rng(1)
                ElseIf rng.Count = WorksheetFunction.CountIf(rng, "") Then
                    .Range("M" & lRow + 1) = ""
                Else
                    .Range("M" & lRow + 1) = "Multiple"
                End If
                If y = 19 Then
                    y = 20
                Else
                    y = 19
                End If
            End With
        Next i
        With desWS
            .Range("J4:J" & LastRow + 1).NumberFormat = "#,##0"
            .Range("L4:L" & LastRow + 1).NumberFormat = "[$-1009]d/mmm/yy;@"
        End With
    End With
    Application.ScreenUpdating = True
End Sub

 

[hrayani]

1) It starts pasting from Row B2... I request it to change it to B4
2) When I re-run the code it just overlaps on the existing data. So idea is to erase all the existing data (cells colors also) & start from scratch
3) It does not copy the Headings. Please tell the code to copy the headings also
4) The Remarks column is showing the 1st instance, whereas I want it to write Multiple just like the Size Column else 1st instance if there are multiple entries
5) Strange thing I noticed is, the format of the last row of the table. Pls see below red fonts

Thanks Again! for the wonderful code

Point # 1,2 & 4 SOLVED

Point # 5: I tried experiment a bit with your code & came up with this

Changing this line of the code
From This

 LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

To This

 LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

Also removing the below part because it was not required anymore

' With desWS
        '    .Range("J4:J" & LastRow + 1).NumberFormat = "#,##0"
        '    .Range("L4:L" & LastRow + 1).NumberFormat = "[$-1009]d-mmm-yy;@"
      '  End With

Point # 3: It still does not copy the headings. But if the code starts from row 5 instead of 4 then I could type in the headings for the first time coz the headings will & should always remain there & also I can set the format of the headings the way I want once & for all. I think this would be better

Moreover, I would like the code to clear everything from row # 5 at the beginning of the code to overcome the overlapping issue & also I would be having a lot of other formulas on top rows. I hope this makes sense

 

[mumps]

This is what I get when I run the macro on the file you uploaded. You asked that the data be pasted starting in cell B4. That's what the macro does. The headers are also visible in row 3.

hrayani.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
3		PO #	REF #	PO DATE	Customer	Supplier	Article	Quality	SIZE	QTY	UNIT	PO SHIP DATE	REMARKS
4	1	229988	636	44544	LOS	Fabrics	Sheet Set	CTN Renforce - 100 Gsm	Single	1,416	Set(s)	28-Feb-22
5	1	229900	636	44544	LOS	Fabrics	Sheet Set	CTN Renforce - 100 Gsm	Single	3,456	Set(s)	28-Feb-22
6	2	229988	636	44544	LOS	Fabrics	Sheet Set	CTN Renforce - 100 Gsm	Single	4,872	Set(s)	28-Feb-22
7	1	403175	637	44551	OFF	Towel	Terry Beach Towel	CTN 320 GSM	70x150 cm	60,160	Pc(s)	18-Apr-22	Amount in euro increased to 13%
8	1	403176	637	44551	OFF	Towel	Poncho	CTN 320 GSM	60x120 cm	56,092	Pc(s)	18-Apr-22	Amount in euro increased to 13%
9	2	403175	637	44551	OFF	Towel	Terry Beach Towel	CTN 320 GSM	Multiple	116,252	Pc(s)	18-Apr-22	Amount in euro increased to 13%
10	1	377421	633	44559	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Normal	143,760	Set(s)	2-May-22
11	1	377422	633	44559	TEX 1	Mills	Pillow Pair	Microfiber Satin - 100 Gsm	Multiple	233,544	Pair(s)	2-May-22
12	1	377423	633	44559	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Over	94,240	Set(s)	2-May-22
13	1	377424	633	44559	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	King	37,110	Set(s)	2-May-22
14	1	377425	633	44559	TEX 1	Mills	Side Pillow	Microfiber Satin - 100 Gsm	40x145	176,532	Pc(s)	2-May-22
15	2	377421	633	44559	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Multiple	685,186	Multiple	2-May-22
16	1	980154	634	44540	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Normal	59,976	Set(s)	6-Jun-22
17	1	980155	634	44540	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	King	25,410	Set(s)	6-Jun-22
18	1	980156	634	44540	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Over	47,976	Set(s)	6-Jun-22
19	1	980157	634	44540	TEX 1	Mills	Pillow Pair	Microfiber Satin - 100 Gsm	Multiple	51,240	Pair(s)	6-Jun-22
20	2	980154	634	44540	TEX 1	Mills	Bed Set	Microfiber Satin - 100 Gsm	Multiple	184,602	Multiple	6-Jun-22
RUNNING ORDER STATUS

If this is not what you want, please upload a file that shows your desired result.

 

[hrayani]

Hi Mumps,

I appreciate you are spending your time to help me

I figured out what must have gone wrong
My Initial post states that the heading starts from Row # 2 but you must have copied it to row # 1 that is why you are getting the headings & I am not

I have adjusted the data to Row # 1 (that's fine)
Honestly speaking, my actual data is also supposed to be starting from row # 1

Now in said case, is it possible to copy the data only starting from row # 4 & not headings
Sorry, for this but I just realized that I want to format the headings once & for all as per my choice of colors Coz headings will always be there at Row # 3

And also at the start of the code, clear everything from row # 4 till the last row. Don't clear/erase/delete etc anything above row # 4

 

[mumps]

Please upload an updated file showing exactly how your data is organized and include a sheet that shows your desired results.

 

[hrayani]

Please upload an updated file showing exactly how your data is organized and include a sheet that shows your desired results.

Can you please guide me, where to upload it

 

[mumps]

You can use the XL2BB button in the menu. If you use this add-on, select to show a mini sheet in the options. You could use Box.com or Dropbox.com or any other free upload site.