VBA Code required - copy data from one sheet to another

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,475
Office Version
  1. 2016
Platform
  1. Windows
Hello Friends,

I need a VBA code to copy specific data from worksheet DATABASE to another worksheet PO Wise

Here is the DATABASE sheet. All values are stored as values. No formulas, validation, conditional formats etc... nothing. Just simple values

mr excel query.xlsx
ABCDEFGHIJKLMNOPQRSTUV
3P.O #REFP.O.DATECustomerSuppliersArticleQualityDyed or PrintedFiber ContentConstruction SizeQuantityUNITP.O. SHIPMENT DATEActual Ship DATERemarksSAMPLINGHELPER 2HELPER 3HELPER 4ValuesSTATUS
495822123dateCustomer 3Gul AhmedBed SetCVC Renforce - 90 Gsmsome textsome textsome textsome text676,830datedatesome textsome textsome textsome textsome text3,393,385Shipped
595825123dateCustomer 3Gul AhmedBed SetCVC Renforce - 90 Gsmsome textsome textsome textsome text201,520datedatesome textsome textsome textsome textsome text1,338,316Shipped
695828123dateCustomer 3Gul AhmedBed SetCVC Renforce - 90 Gsmsome textsome textsome textsome text15,420datedatesome textsome textsome textsome textsome text134,154Shipped
795830123dateCustomer 3Gul AhmedSide PillowCVC Renforce - 90 Gsmsome textsome textsome textsome text179,712datedatesome textsome textsome textsome textsome text242,611Shipped
895831123dateCustomer 3Gul AhmedPillow PairCVC Renforce - 90 Gsmsome textsome textsome textsome text436,032datedatesome textsome textsome textsome textsome text639,784Shipped
940041541127dateCustomer 4EasternTerry TowelCTN 400 Gsm PUNCHsome textsome textsome textsome text58,000datedatesome textsome textsome textsome textsome text72,325Shipped
1040043227127dateCustomer 4EasternTerry TowelCTN 400 Gsm PUNCHsome textsome textsome textsome text1,887datedatesome textsome textsome textsome textsome text953Shipped
1140042045129dateCustomer 4Union FabricsBed SetCTN Renforce - 115 Gsmsome textsome textsome textsome text2,400datedatesome textsome textsome textsome textsome text12,840Shipped
1212943122 A130dateCustomer 4Union FabricsPillow PcCTN Renforce - 115 Gsmsome textsome textsome textsome text19,400datedatesome textsome textsome textsome textsome text45,142Shipped
1312943122 B130dateCustomer 4Union FabricsDuvet CoverCTN Renforce - 115 Gsmsome textsome textsome textsome text6,000datedatesome textsome textsome textsome textsome text57,600Shipped
14672571131dateCustomer 5Usman & SonsFitted SheetMicrofiber Interlock - 135 Gsmsome textsome textsome textsome text2,500datedatesome textsome textsome textsome textsome text9,604Shipped
1578118132dateCustomer 3Gul AhmedBed SetCVC Renforce - 90 Gsmsome textsome textsome textsome text372,472datedatesome textsome textsome textsome textsome text1,896,309Shipped
1678186132dateCustomer 3Gul AhmedBed SetCVC Renforce - 90 Gsmsome textsome textsome textsome text176,976datedatesome textsome textsome textsome textsome text1,259,929Shipped
1778581132dateCustomer 3Gul AhmedBed SetCVC Renforce - 90 Gsmsome textsome textsome textsome text26,088datedatesome textsome textsome textsome textsome text254,796Shipped
1876424133dateCustomer 3Gul AhmedBed SetCTN Renforce - 115 Gsmsome textsome textsome textsome text524,320datedatesome textsome textsome textsome textsome text3,514,886Shipped
1976535133dateCustomer 3Gul AhmedBed SetCTN Renforce - 115 Gsmsome textsome textsome textsome text192,840datedatesome textsome textsome textsome textsome text1,775,107Shipped
2076546133dateCustomer 3Gul AhmedBed SetCTN Renforce - 115 Gsmsome textsome textsome textsome text48,360datedatesome textsome textsome textsome textsome text573,377Shipped
2176627133dateCustomer 3Gul AhmedPillow PairCTN Renforce - 115 Gsmsome textsome textsome textsome text372,552datedatesome textsome textsome textsome textsome text755,222Shipped
2276634133dateCustomer 3Gul AhmedSide PillowCTN Renforce - 115 Gsmsome textsome textsome textsome text224,364datedatesome textsome textsome textsome textsome text406,099Shipped
23675098134dateCustomer 5Usman & SonsFitted SheetCombed Jersey 135 GSMsome textsome textsome textsome text500datedatesome textsome textsome textsome textsome text1,689Shipped
2440043141135dateCustomer 4Usman & SonsFitted SheetCTN Jersey - 140 Gsmsome textsome textsome textsome text9,250datedatesome textsome textsome textsome textsome text69,005Shipped
2540043137136dateCustomer 4Usman & SonsFitted SheetCTN Jersey - 140 Gsmsome textsome textsome textsome text14,400datedatesome textsome textsome textsome textsome text55,248Shipped
2640043145137dateCustomer 4Union FabricsBed SetCTN Renforce - 115 Gsmsome textsome textsome textsome text1,000datedatesome textsome textsome textsome textsome text6,100Shipped
2740043138138dateCustomer 4Usman & SonsFitted SheetCTN Jersey - 140 Gsmsome textsome textsome textsome text14,400datedatesome textsome textsome textsome textsome text55,248Shipped
2840043139139dateCustomer 4Usman & SonsFitted SheetCTN Jersey - 140 Gsmsome textsome textsome textsome text14,400datedatesome textsome textsome textsome textsome text55,248Shipped
2940043150140dateCustomer 4Usman & SonsFitted SheetCTN Jersey - 140 Gsmsome textsome textsome textsome text14,400datedatesome textsome textsome textsome textsome text55,248Shipped
3040043241 A141dateCustomer 4Union FabricsBed SetCTN Renforce - 115 Gsmsome textsome textsome textsome text3,900datedatesome textsome textsome textsome textsome text51,870Shipped
3140043241 B141dateCustomer 4Union FabricsBed SetCTN Renforce - 115 Gsmsome textsome textsome textsome text1,000datedatesome textsome textsome textsome textsome text8,850Shipped
3240043241 C141dateCustomer 4Union FabricsPillow PcCTN Satin - T200some textsome textsome textsome text1,400datedatesome textsome textsome textsome textsome text4,047Shipped
3340043241 D141dateCustomer 4Union FabricsDuvet CoverCTN Satin - T200some textsome textsome textsome text1,200datedatesome textsome textsome textsome textsome text15,680Shipped
3440043241 E141dateCustomer 4Union FabricsPillow PcCTN Satin - T200some textsome textsome textsome text2,420datedatesome textsome textsome textsome textsome text7,082Shipped
3540043241 F141dateCustomer 4Union FabricsDuvet CoverCTN Satin - T200some textsome textsome textsome text1,670datedatesome textsome textsome textsome textsome text21,951Shipped
3640043241 G141dateCustomer 4Union FabricsPillow PcCTN Satin - T200some textsome textsome textsome text4,850datedatesome textsome textsome textsome textsome text11,938Shipped
3740043241 H141dateCustomer 4Union FabricsDuvet CoverCTN Satin - T200some textsome textsome textsome text650datedatesome textsome textsome textsome textsome text6,338Shipped
3840043201 A142dateCustomer 4Union FabricsBed SetCTN Percale - T200some textsome textsome textsome text1,150datedatesome textsome textsome textsome textsome text16,388Shipped
3940043201 B142dateCustomer 4Union FabricsPillow PcCTN Percale - T200some textsome textsome textsome text1,680datedatesome textsome textsome textsome textsome text4,183Shipped
4040043201 C142dateCustomer 4Union FabricsDuvet CoverCTN Percale - T200some textsome textsome textsome text1,070datedatesome textsome textsome textsome textsome text13,425Shipped
4140043201 D142dateCustomer 4Union FabricsBed SetCTN Renforce - 115 Gsmsome textsome textsome textsome text1,700datedatesome textsome textsome textsome textsome text17,850Shipped
4240043201 E142dateCustomer 4Union FabricsPillow PcCTN Satin - T200some textsome textsome textsome text15,100datedatesome textsome textsome textsome textsome text36,349Shipped
4340043201 F142dateCustomer 4Union FabricsDuvet CoverCTN Satin - T200some textsome textsome textsome text3,935datedatesome textsome textsome textsome textsome text38,366Shipped
44133018143dateCustomer 1EasternTerry TowelCTN 360 GSMsome textsome textsome textsome text4,500datedatesome textsome textsome textsome textsome text24,300Shipped
4519022014/JR144dateCustomer 2Usman & SonsFitted SheetCTN Jersey - 125 Gsmsome textsome textsome textsome text14,680datedatesome textsome textsome textsome textsome text45,371Shipped
4640043826 A145dateCustomer 4Union FabricsBed SetCTN Renforce - 115 Gsmsome textsome textsome textsome text1,200datedatesome textsome textsome textsome textsome text7,140Shipped
4740043826 B145dateCustomer 4Union FabricsBed SetCTN Renforce - 115 Gsmsome textsome textsome textsome text1,300datedatesome textsome textsome textsome textsome text13,650Shipped
4840043826 C145dateCustomer 4Union FabricsPillow PcCTN Satin - T200some textsome textsome textsome text4,700datedatesome textsome textsome textsome textsome text11,328Shipped
4940043826 D145dateCustomer 4Union FabricsDuvet CoverCTN Satin - T200some textsome textsome textsome text650datedatesome textsome textsome textsome textsome text6,338Shipped
50101031146dateCustomer 3Liberty MillsBed SetCVC Renforce - 90 Gsmsome textsome textsome textsome text107,040datedatesome textsome textsome textsome textsome text544,137Shipped
51101032146dateCustomer 3Liberty MillsBed SetCVC Renforce - 90 Gsmsome textsome textsome textsome text115,222datedatesome textsome textsome textsome textsome text945,015Shipped
52101033146dateCustomer 3Liberty MillsBed SetCVC Renforce - 90 Gsmsome textsome textsome textsome text31,860datedatesome textsome textsome textsome textsome text287,099Shipped
53348147dateCustomer 3Gul AhmedBed SetCTN Flannel - 140 Gsmsome textsome textsome textsome text3,840datedatesome textsome textsome textsome textsome text51,149Shipped
54349147dateCustomer 3Gul AhmedBed SetCTN Flannel - 140 Gsmsome textsome textsome textsome text1,920datedatesome textsome textsome textsome textsome text33,062Shipped
55350147dateCustomer 3Gul AhmedBed SetCTN Flannel - 140 Gsmsome textsome textsome textsome text960datedatesome textsome textsome textsome textsome text19,613Shipped
56351147dateCustomer 3Gul AhmedPillow PairCTN Flannel - 140 Gsmsome textsome textsome textsome text1,616datedatesome textsome textsome textsome textsome text6,432Shipped
5713059310148dateCustomer 4Tex WorldTerry TowelCTN 400 Gsm PUNCHsome textsome textsome textsome text42,400datedatesome textsome textsome textsome textsome text42,588Shipped
5813059311149dateCustomer 4Tex WorldTerry TowelCTN 400 Gsm PUNCHsome textsome textsome textsome text50,310datedatesome textsome textsome textsome textsome text59,729Shipped
5940044281 A150dateCustomer 4Usman & SonsFitted SheetCTN Jersey - 140 Gsmsome textsome textsome textsome text5,200datedatesome textsome textsome textsome textsome text34,840Shipped
6040044281 B150dateCustomer 4Usman & SonsFitted SheetCTN Jersey - 140 Gsmsome textsome textsome textsome text1,890datedatesome textsome textsome textsome textsome text17,766Shipped
6140044322 A151dateCustomer 4Union FabricsBed SetCTN Renforce - 115 Gsmsome textsome textsome textsome text1,000datedatesome textsome textsome textsome textsome text6,350Shipped
6240044322 B151dateCustomer 4Union FabricsBed SetCTN Renforce - 115 Gsmsome textsome textsome textsome text1,000datedatesome textsome textsome textsome textsome text10,920Shipped
63102755153dateCustomer 3Gul AhmedPillow PairCTN Flannel - 140 Gsmsome textsome textsome textsome text88,416datedatesome textsome textsome textsome textsome text265,248Shipped
64102756153dateCustomer 3Gul AhmedBed SetCTN Flannel - 140 Gsmsome textsome textsome textsome text65,504datedatesome textsome textsome textsome textsome text736,920Shipped
65102757153dateCustomer 3Gul AhmedBed SetCTN Flannel - 140 Gsmsome textsome textsome textsome text38,604datedatesome textsome textsome textsome textsome text526,945Shipped
6640044489 A154dateCustomer 4Union FabricsBed SetPC Renforce - 115 Gsmsome textsome textsome textsome text700datedatesome textsome textsome textsome textsome text6,195Shipped
6740044489 B154dateCustomer 4Union FabricsPillow PcCTN Renforce - 115 Gsmsome textsome textsome textsome text11,900datedatesome textsome textsome textsome textsome text27,710Shipped
6840044489 C154dateCustomer 4Union FabricsDuvet CoverCTN Renforce - 115 Gsmsome textsome textsome textsome text1,800datedatesome textsome textsome textsome textsome text17,280Shipped
69687558155dateCustomer 5Usman & SonsFitted SheetCombed Jersey 135 GSMsome textsome textsome textsome text2,500datedatesome textsome textsome textsome textsome text8,791Shipped
7040044896 A156dateCustomer 4Union FabricsPillow PcCTN Satin - T200some textsome textsome textsome text4,500datedatesome textsome textsome textsome textsome text11,061Shipped
7140044896 B156dateCustomer 4Union FabricsDuvet CoverCTN Satin - T200some textsome textsome textsome text600datedatesome textsome textsome textsome textsome text5,850Shipped
7240045226 A157dateCustomer 4Union FabricsPillow PcCTN Renforce - 115 Gsmsome textsome textsome textsome text5,000datedatesome textsome textsome textsome textsome text11,750Shipped
7340045226 B157dateCustomer 4Union FabricsDuvet CoverCTN Renforce - 115 Gsmsome textsome textsome textsome text500datedatesome textsome textsome textsome textsome text4,800Shipped
7440045226 C157dateCustomer 4Union FabricsPillow PcCTN Satin - T200some textsome textsome textsome text5,000datedatesome textsome textsome textsome textsome text12,482Shipped
7540045226 D157dateCustomer 4Union FabricsDuvet CoverCTN Satin - T200some textsome textsome textsome text400datedatesome textsome textsome textsome textsome text3,900Shipped
7613119634 A158dateCustomer 4Union FabricsDuvet CoverCTN Satin - T200some textsome textsome textsome text465datedatesome textsome textsome textsome textsome text2,037Shipped
7713119634 B158dateCustomer 4Union FabricsDuvet CoverCTN Satin - T200some textsome textsome textsome text578datedatesome textsome textsome textsome textsome text8,269Shipped
DATABASE


And I want some specific data on sheet PO wise like this

Starting from Cell B4
Column B: All unique ref # from DATABASE sheet
Column C: All PO # against Ref # (all PO # concatenated into one cell - will be unique - no need to remove duplicates)
Column D: Sample text against the Ref # (just look at the first instance as all sample text will be same against a REF #)
Column E: Customer against Ref # (just look at the first instance as all customers will be same against a REF #)
Column F: Supplier against Ref # (just look at the first instance as all suppliers will be same against a REF #)
Column G: Articles against Ref # (all articles against REF # concatenated into one cell - remove duplicates if any)
Column H: Quality against Ref # (just look at the first instance as all quality will be same against a REF #)
Column I: Quantity aginst Ref # (sum of quantity in one cell against REF #)
Column J: Value against Ref # (sum of value in one cell aginst REF #)

The result should look like this ?

mr excel query.xlsx
BCDEFGHIJ
10Ref #PO #SamplingCustomerSupplierArticleQualityQtyValue
1112395822, 95825, 95828, 95830, 95831some textCustomer 3Gul AhmedBed Set, Side Pillow, Pillow PairCVC Renforce - 90 Gsm1,509,5145,748,250
1212740041541, 40043227some textCustomer 4EasternTerry TowelCTN 400 Gsm PUNCH59,88773,278
1312940042045some textCustomer 4Union FabricsBed SetCTN Renforce - 115 Gsm2,40012,840
1413012943122 A, 12943122 Bsome textCustomer 4Union FabricsPillow Pc, Duvet CoverCTN Renforce - 115 Gsm25,400102,742
15131672571some textCustomer 5Usman & SonsFitted SheetMicrofiber Interlock - 135 Gsm2,5009,604
1613278118, 78186, 78581some textCustomer 3Gul AhmedBed SetCVC Renforce - 90 Gsm575,5363,411,034
1713376424, 76535, 76546, 76627, 76634some textCustomer 3Gul AhmedBed Set, Pillow Pair, Side PillowCTN Renforce - 115 Gsm1,362,4367,024,692
18134675098some textCustomer 5Usman & SonsFitted SheetCombed Jersey 135 GSM5001,689
1913540043141some textCustomer 4Usman & SonsFitted SheetCTN Jersey - 140 Gsm9,25069,005
2013640043137some textCustomer 4Usman & SonsFitted SheetCTN Jersey - 140 Gsm14,40055,248
2113740043145some textCustomer 4Union FabricsBed SetCTN Renforce - 115 Gsm1,0006,100
2213840043138some textCustomer 4Usman & SonsFitted SheetCTN Jersey - 140 Gsm14,40055,248
2313940043139some textCustomer 4Usman & SonsFitted SheetCTN Jersey - 140 Gsm14,40055,248
2414040043150some textCustomer 4Usman & SonsFitted SheetCTN Jersey - 140 Gsm14,40055,248
2514140043241 A, 40043241 B, 40043241 C, 40043241 D, 40043241 E, 40043241 F, 40043241 G, 40043241 Hsome textCustomer 4Union FabricsBed Set, Pillow Pc, Duvet CoverCTN Renforce - 115 Gsm17,090127,755
2614240043201 A, 40043201 B, 40043201 C, 40043201 D, 40043201 E, 40043201 Fsome textCustomer 4Union FabricsBed Set, Pillow Pc, Duvet CoverCTN Percale - T20024,635126,560
27143133018some textCustomer 1EasternTerry TowelCTN 360 GSM4,50024,300
2814419022014/JRsome textCustomer 2Usman & SonsFitted SheetCTN Jersey - 125 Gsm14,68045,371
2914540043826 A, 40043826 B, 40043826 C, 40043826 Dsome textCustomer 4Union FabricsBed Set, Pillow Pc, Duvet CoverCTN Renforce - 115 Gsm7,85038,456
30146101031, 101032, 101033some textCustomer 3Liberty MillsBed SetCVC Renforce - 90 Gsm254,1221,776,251
31147348, 349, 350, 351some textCustomer 3Gul AhmedBed Set, Pillow PairCTN Flannel - 140 Gsm8,336110,256
3214813059310some textCustomer 4Tex WorldTerry TowelCTN 400 Gsm PUNCH42,40042,588
3314913059311some textCustomer 4Tex WorldTerry TowelCTN 400 Gsm PUNCH50,31059,729
3415040044281 A, 40044281 Bsome textCustomer 4Usman & SonsFitted SheetCTN Jersey - 140 Gsm7,09052,606
3515140044322 A, 40044322 Bsome textCustomer 4Union FabricsBed SetCTN Renforce - 115 Gsm2,00017,270
36153102755, 102756, 102757some textCustomer 3Gul AhmedPillow Pair, Bed SetCTN Flannel - 140 Gsm192,5241,529,113
3715440044489 A, 40044489 B, 40044489 Csome textCustomer 4Union FabricsBed Set, Pillow Pc, Duvet CoverPC Renforce - 115 Gsm14,40051,185
38155687558some textCustomer 5Usman & SonsFitted SheetCombed Jersey 135 GSM2,5008,791
3915640044896 A, 40044896 Bsome textCustomer 4Union FabricsPillow Pc, Duvet CoverCTN Satin - T2005,10016,911
4015740045226 A, 40045226 B, 40045226 C, 40045226 Dsome textCustomer 4Union FabricsPillow Pc, Duvet CoverCTN Renforce - 115 Gsm10,90032,932
4115813119634 A, 13119634 Bsome textCustomer 4Union FabricsDuvet CoverCTN Satin - T2001,04310,306
PO Wise



Any help would be appreciated..

Regards,

Humayun
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Give this a try. It might need some tweaking for the starting rows.

VBA Code:
Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, fn As Range, c As Range, r As Range, rng1 As Range, rng2 As Range, adr As String
Set sh1 = ActiveSheet
Set sh2 = Sheets.Add(After:=sh1)
lr = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    With sh1
        .Range("B3", .Cells(Rows.Count, 2)).AdvancedFilter xlFilterCopy, , .Cells(lr + 2, 1), True
        For Each c In .Cells(Rows.Count, 1).End(xlUp).CurrentRegion.Offset(1)
            If c <> "" Then
                Set fn = .Range("B:B").Find(c.Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                        sh2.Cells(Rows.Count, 2).End(xlUp)(2) = c.Value
                        .UsedRange.AutoFilter 2, fn.Value
                        Set rng1 = sh2.Cells(Rows.Count, 2).End(xlUp).Offset(, 1)
                        For Each r In .Range("A2:A" & lr).SpecialCells(xlCellTypeVisible)
                            rng1 = rng1.Value & r.Value & ", "
                        Next
                        Columns(rng1.Column).AutoFit
                        rng1 = Left(rng1.Value, Len(rng1) - 2)
                        rng1.Offset(, 1) = fn.Offset(, 15).Value
                        rng1.Offset(, 2) = fn.Offset(, 2).Value
                        rng1.Offset(, 3) = fn.Offset(, 3).Value
                        Set rng2 = rng1.Offset(, 4)
                        For Each r In .Range("F2:F" & lr).SpecialCells(xlCellTypeVisible)
                            If InStr(rng2.Value, Trim(r.Value)) = 0 Then
                                rng2 = rng2.Value & r.Value & ", "
                            End If
                        Next
                        Columns(rng2.Column).AutoFit
                        rng2 = Left(rng2.Value, Len(rng2) - 2)
                        rng2.Offset(, 1) = fn.Offset(, 5).Value
                        rng2.Offset(, 2) = Application.SumIf(.Range("B2:B" & lr), c.Value, .Range("L2:L" & lr))
                        rng2.Offset(, 3) = Application.SumIf(.Range("B2:B" & lr), c.Value, .Range("U2:U" & lr))
                    .AutoFilterMode = False
                End If
            End If
        Next
        .Cells(lr + 2, 1).CurrentRegion.ClearContents
    End With
End Sub
 
Upvote 0
Hi JLGWhiz,

First of all thanks very much for the code... I appreciate

I wanted the the data to extracted to the sheet called "PO Wise". So I have changed
From this
Set sh2 = Sheets.Add(After:=sh1)
To This
Set sh2 = Sheets("PO Wise")

Well that's the only thing I was able to do with my very little knowledge of VBA

There are some minor issues mentioned below:

1) As you pointed out that I want the data to be started at row # 11, whereas as of now it is starting at row # 2
2) If the mother source file has some hidden columns then it does not work
3) In PO and article column its written PO # and article in every cell... which is not required. pls see screen shot

1606732974826.png



4) The code takes about 6 to 8 seconds to complete the task. So we can do this to cut the load of the VB code

These below points under red box can be ignored if they can save some time for the code to run

1606732354098.png


I will type the Ref # once and it will remain there on the sheet

Columns D,E,F & H can be called with an index match formula in VBA - which can be done under 1 second

So now all the code has to do is this

Starting from cell B11
Column C: Look at cell B11 and find all PO's against that ref # from the database sheet and return concatenated into one single cell
Column G: Look at cell B11 and find all articles against ref # from the database sheet and return concatenated value - removing duplicates
Column I: Look at cell B11 and sum all quantities from the database sheet
Column J: Look at cell B11 and sum all values from the database sheet

Column C & G are the most important thing

Regards,

Humayun
 
Upvote 0
Hi,

This is what I have come up with and it does all what I want in 1 second - except 2 things

The PO # & articles

1) ALL PO # against Ref # needs to concatenated in single cell - there wont be any duplicates
2) All Article agianst Ref # needs to be concatenated in single cell - remove duplicates if any

as of now I am able to pull only the first instance :cry:

VBA Code:
[/B]
Sub humayun()

Range("C11:C583").Formula = "=iferror(index(po,match($B11,ref,0)),"""")"
Range("D11:D583").Formula = "=iferror(index(sampling,match($B11,ref,0)),"""")"
Range("E11:E583").Formula = "=iferror(index(customer,match($B11,ref,0)),"""")"
Range("F11:F583").Formula = "=iferror(index(supplier,match($B11,ref,0)),"""")"
Range("G11:G583").Formula = "=iferror(index(article,match($B11,ref,0)),"""")"
Range("H11:H583").Formula = "=iferror(index(quality,match($B11,ref,0)),"""")"
Range("I11:I583").Formula = "=SUMIF(ref,B11,quantity)"
Range("J11:J583").Formula = "=SUMIF(ref,B11,value)"

Range("C11:J583").Value = Range("C11:J583").Value
End Sub

[B]


Any idea ??

Regards,

Humayun
 
Upvote 0
No, the code I posted was my idea.
Regards, JLG
Thanks...

As of now its extracting results in sh.2 starting from cell B2

Can you pls make one change to make it start from from cell B11 instead of cell B2

Regards,

Humayun
 
Upvote 0
See if this works better.

VBA Code:
Sub t2()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, fn As Range, c As Range, r As Range, rng1 As Range, rng2 As Range, adr As String
Set sh1 = ActiveSheet
Set sh2 = Sheets.Add(After:=sh1)
lr = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    With sh1
        .Range("B10", .Cells(Rows.Count, 2)).AdvancedFilter xlFilterCopy, , .Cells(lr + 2, 1), True
        For Each c In .Cells(Rows.Count, 1).End(xlUp).CurrentRegion.Offset(1)
            If c <> "" Then
                Set fn = .Range("B:B").Find(c.Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    If sh2.Range("B11") = "" Then
                        sh2.Range("B11") = c.Value
                    Else
                        sh2.Cells(Rows.Count, 2).End(xlUp)(2) = c.Value
                    End If
                    .UsedRange.AutoFilter 2, fn.Value
                    Set rng1 = sh2.Cells(Rows.Count, 2).End(xlUp).Offset(, 1)
                    For Each r In .Range("A11:A" & lr).SpecialCells(xlCellTypeVisible)
                        rng1 = rng1.Value & r.Value & ", "
                    Next
                    Columns(rng1.Column).AutoFit
                    rng1 = Left(rng1.Value, Len(rng1) - 2)
                    rng1.Offset(, 1) = fn.Offset(, 15).Value
                    rng1.Offset(, 2) = fn.Offset(, 2).Value
                    rng1.Offset(, 3) = fn.Offset(, 3).Value
                    Set rng2 = rng1.Offset(, 4)
                    For Each r In .Range("F11:F" & lr).SpecialCells(xlCellTypeVisible)
                        If InStr(rng2.Value, Trim(r.Value)) = 0 Then
                            rng2 = rng2.Value & r.Value & ", "
                        End If
                    Next
                    Columns(rng2.Column).AutoFit
                    rng2 = Left(rng2.Value, Len(rng2) - 2)
                    rng2.Offset(, 1) = fn.Offset(, 5).Value
                    rng2.Offset(, 2) = Application.SumIf(.Range("B2:B" & lr), c.Value, .Range("L2:L" & lr))
                    rng2.Offset(, 3) = Application.SumIf(.Range("B2:B" & lr), c.Value, .Range("U2:U" & lr))
                    .AutoFilterMode = False
                End If
            End If
        Next
        .Cells(lr + 2, 1).CurrentRegion.ClearContents
    End With
End Sub
 
Upvote 0
Just tested the recent code..

I have changed the sh1 and sh2 names as required and it is working fine with that

From This
Set sh1 = ActiveSheet
Set sh2 = Sheets.Add(After:=sh1)

To This
Set sh1 = Sheets("DATABASE")
Set sh2 = Sheets("PO Wise")

But the problem is that it start looking at sh1 from Cell B11 whereas I want it to look at sh1 from Cell B4 and because of this some starting data when extracted on sh2.

I tried changing the range to B4 in this part

With sh1
.Range("B10", .Cells(Rows.Count, 2)).AdvancedFilter xlFilterCopy, , .Cells(lr + 2, 1), True


But I get an error 1004 (no cells were found) with this part of the code highlited ?

For Each r In .Range("A11:A" & lr).SpecialCells(xlCellTypeVisible)
 
Upvote 0
You will have to get someone else to help you with this. I have spent too much time on it now.
Regards, JLG
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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