COPYING DATA FROM SHEET 1 BASED ON VALUE IN COLUMN P

livetolearn4life

New Member
Joined
Jul 14, 2020
Messages
15
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Hello Im trying to move to a new sheet, specific rows and columns from sheet 1 if the value in column P meets a specific criteria ( eg tool shop). then loop for the next specific value in Column P (eg Stock Room) and move the specific rows and columns to sheet 3 data and so forth and so on.

Here is sheet 1 with all the data... below this I posted what the specific row and columns I would like to paste into the new sheet.

testing.xlsm
ABCDEFGHIJKLMNOPQ
1ORDNOREFNOJOBNOFITEMDESCOSTATCRDTODUDTCRUSR REMAININGMO AREADT ACCEPTEDDT REJECTEDDUE DTTARGETAREAFOCAL
2JBAA146AREDD870DIM8650VFMZZ4VFM TOOL4012104221210429GHGNVHTSHOPPY04/29/21KENTUCKYTOOL SHOPZEEKY
3JB27A38ADISB690DOM6830SAZZ2ZSA TOOL1012106021210608SFTGNVHTSHOPPY06/08/21ALABAMATOOL SHOPZEEKY
4JB237A5AMTRMLDIMTRMLSA2176SA TOOL4012105271210610SFTGNVHTSHOPPY06/10/21VEGASTOOL SHOPZEEKY
5JB27A39AREDD190DIM8720ADTZZ3DOOM TOOL1012106021210608SFTGNVHTSHOPPY06/08/21TEXASTOOL SHOPZEEKY
6JB94A3AAREDC620DIM8080HFZZ34HF TOOL4012104121210426SFTGNVHTSHOPPY04/26/21KENTUCKYTOOL SHOPZEEKY
7JB26126AREDD870DIM8650HFZZ4ZHF TOOL4012106011210602SFTGNVHTSHOPPY06/02/21KENTUCKYTOOL SHOPZEEKY
8JB26127AREDD870DIM8650FMLDZZJETSON1012106011210602SFTGNVHTSHOPPY06/02/21KENTUCKYTOOL SHOPZEEKY
9JB22253AREDD870DIM8650SF4165SF TOOL1012105261210526SFTGNVHTSHOPPY05/26/21KENTUCKYTOOL SHOPZEEKY
10JB3A667AREDD980DIM8680HFZZ32HF TOOL4012106091210610SFTGNVHTSHOPPY06/10/21FLORIDATOOL SHOPZEEKY
11JB94269ATRU6300DIM6300ADT241CUU TOOL1012104131210415SFTGNVHTSHOPPY04/15/21DELWARETOOL SHOPZEEKY
12JB23715ATRU6300DIM6300FDZ650FD TOOL1012105271210603SFTGNVHTSHOPPY06/03/21DELWARETOOL SHOPZEEKY
13JB23714ATRU6300DIM6300PBDZ65PBD TOOL4012105271210603SFTGNVHTSHOPPY06/03/21DELWARETOOL SHOPZEEKY
14JB23696ATRU6300DIM6300PBFDZ4PBFD TOOL4012105271210603SFTGNVHTSHOPPY06/03/21DELWARETOOL SHOPZEEKY
15JB3A799ADISC360DOM70502SAZZ4SA1012106091210616SFTGNVHTSHOPPY06/16/21MAINETOOL SHOPZEEKY
16JB3A723ADISC360DOM7050SAZZ49SHOP AIDE TOOL1012106091210616SFTGNVHTSHOPPY06/16/21MAINETOOL SHOPZEEKY
17JB3A731ADISC360DOM7050SAZZ49SHOP AIDE TOOL1012106091210616SFTGNVHTSHOPPY06/16/21MAINETOOL SHOPZEEKY
18JB237A4AMTRMLDIMTRMLSA4110SA TOOL4012105271210610SFTBJU, FSPCEJIM OR BOB?06/10/21VEGASTOOL SHOPZEEKY
19JB21654AREDD870DIM86502ADTZZDIM TOOL4012105251210531SFTBJU, FSPCEJIM OR BOB?05/31/21KENTUCKYTOOL SHOPZEEKY
20JB21656AREDD870DIM86503ADTZZDIM TOOL4012105251210531SFTBJU, FSPCEJIM OR BOB?05/31/21KENTUCKYTOOL SHOPZEEKY
21JB21611AREDD870DIM8650ADTZZ5DOG TOOL4012105251210531SFTBJU, FSPCEJIM OR BOB?05/31/21KENTUCKYTOOL SHOPZEEKY
22JB23635AREDD870DIM8650ZZ4Z30TIR4012105271210527SFTBJU, FSPCEJIM OR BOB?05/27/21KENTUCKYTOOL SHOPZEEKY
23JB94A7AADISD430JIM504ZZ2904SPACER-Z4004012104121210419GEDNOTESCERGY05/24/2104/19/21TULSASTOCK ROOMGLORY
24JBA7582ADISD430JIM504ZZ6315TURNIP GREEN4012105041210510GEDNOTESSTOCK CLERK06/08/2105/10/21TULSASTOCK ROOMGLORY
25JBA76A2ADISD430JIM504ZZ6315TURNIP GREEN4012105041210505GEDNOTESBOBAN06/08/2105/05/21TULSASTOCK ROOMGLORY
26JB67426AREDC810JIM504414110REAR INNER TUBE ASSY4012102221210211GEDNOTESBOBAN06/08/2102/11/21ALABAMASTOCK ROOMGLORY
27JB95289AREDD980JIM504ZZ3162SUPPORT-JACKET4012104151210506GEDNOTESCERGY05/27/2105/06/21FLORIDASTOCK ROOMGLORY
28JB91177AREDD980JIM504ZZ3274GOBBLER 4012104071210421DBYNOTESBOBAN06/07/2104/21/21FLORIDASTOCK ROOMGLORY
29JB94278AREDD980JIM504ZZ3217GOBBLER 4012104131210428DBYNOTESBOBAN06/03/2104/28/21FLORIDASTOCK ROOMGLORY
30JB74511AREDD730JIM504Z74595JACKET4012103031210505DBYNOTESBOBAN05/05/21KENTUCKYSTOCK ROOMGLORY
31JB17362AREDD940JIM504ZZ2903TURNIP GREEN4012105181210518NDSFSPCEGOOBS05/18/21FLORIDAQA INSPECTIONDUMMS
32JB17365AREDD940JIM504ZZ2903TURNIP GREEN4012105181210518NDSFSPCEGOOBS05/18/21FLORIDAQA INSPECTIONDUMMS
33JB1843AAREDD940JIM504ZZ2903TURNIP GREEN4012105191210527NDSFSPCEGOOBS05/27/21FLORIDAQA INSPECTIONDUMMS
34JB18431AREDD940JIM504ZZ2903TURNIP GREEN4012105191210527NDSFSPCEGOOBS05/27/21FLORIDAQA INSPECTIONDUMMS
35JB26117AREDD980JIM504ZZ3162SUPPORT-JACKET4012106011210525NDSFSPCEGOOBS06/09/2105/25/21FLORIDAQA INSPECTIONDUMMS
36JB21452ATRU6300JIM504416510BUNGY BALL BAR4012105251210525NDSFSPCEGOOBS05/25/21DELWAREQA INSPECTIONDUMMS
37JB74653AREDD730JIM504Z75922QUADRANT-STD,Z110W4012103031210325GEDFSPCE, HO, MSPCE, PRC, FSPCEGOOBS03/25/21KENTUCKYQA INSPECTIONDUMMS
38JB74654AREDD730JIM504Z75922QUADRANT-STD,Z110W4012103031210325GEDFSPCE, HO, MSPCE, PRC, FSPCEGOOBS03/25/21KENTUCKYQA INSPECTIONDUMMS
39JB79925AREDD100JIM504ZZ1242SPREADER-OFS,F/R,Z110I,UPPER4012103151751231GEDFSPCE, BG1, HO, VRGBP, RSPCE, FSPCEGOOBS12/31/75KANSASQA INSPECTIONDUMMS
40JB144A7AREDC200JIM50441Z612TUBE ASSY-FWD4012105131201130GEDFSPCEGOOBS11/30/20TEXASQA INSPECTIONDUMMS
41JB13789ADISD430JIM504ZZ1151TURNIP GREEN4012105121210512GEDFSPCEGOOBS05/12/21TULSAQA INSPECTIONDUMMS
42JB9A914ADISD430JIM504ZZ124ZGOBBLER 4012104061210603DBYFSPCEGOOBS06/03/21TULSAQA INSPECTIONDUMMS
43JBA99A3AREDC810JIM504413010INSERT4012105061210506DBYFSPCEGOOBS05/06/21ALABAMAQA INSPECTIONDUMMS
44JBA99A4AREDC810JIM504413010INSERT4012105061210506DBYFSPCEGOOBS05/06/21ALABAMAQA INSPECTIONDUMMS
45JB21581AREDD730JIM504Z69374GOBBLER 4012105251210525DBYFSPCEGOOBS05/25/21KENTUCKYQA INSPECTIONDUMMS
46JBAAA43AREDD980JIM504ZZ62Z6GOBBLER 4012104221210504DBYFSPCEGOOBS05/04/21FLORIDAQA INSPECTIONDUMMS
47JBAAA44AREDD980JIM504ZZ62Z6GOBBLER 4012104221210504DBYFSPCEGOOBS05/04/21FLORIDAQA INSPECTIONDUMMS
48JBAAA47AREDD980JIM504ZZ3274GOBBLER 4012104221210503DBYFSPCEGOOBS05/03/21FLORIDAQA INSPECTIONDUMMS
49JBA8514AREDD980JIM504ZZ4623GOBBLER 4012105051210519DBYFSPCEGOOBS05/19/21FLORIDAQA INSPECTIONDUMMS
50JB982A3AREDD980JIM504ZZ3272GOBBLER 4012104211210505DBYFSPCEGOOBS05/05/21FLORIDAQA INSPECTIONDUMMS
51JB27A64AREDD980JIM504Z77062JACKET4012106031210429DBYFSPCE, HP, VRPRC, RSPCE, FSPCEGOOBS04/29/21FLORIDAQA INSPECTIONDUMMS
52JB19466AREDD730JIM504Z69374GOBBLER 4012105241210527DBYFSPCEGOOBS05/27/21KENTUCKYQA INSPECTIONDUMMS
53JBA5537AREDD870DIM8650ZZ4Z31TIR4012104291751231SFTGNVST, GNVST, GNVHT, GNVST, SPCEHARRY12/31/75KENTUCKYFABRICATIONBILLY RAY
54JB24589ADISD430DOM8670ZZ46Z0TIR4012105281751231SFTGNVHT, GNVST, SPCEHARRY12/31/75TULSAFABRICATIONBILLY RAY
55JB2459AADISD430DOM8670ZZ46Z0TIR4012105281751231SFTGNVHT, GNVST, SPCEHARRY12/31/75TULSAFABRICATIONBILLY RAY
56JB277A8AREDD940DIM8670ZZ4525TIR4012106031210610SFTPI, GNVST, GNVST, SPCEHARRY06/10/21FLORIDAFABRICATIONBILLY RAY
57JB277A9AREDD940DIM8670ZZ4525TIR4012106031210610SFTPI, GNVST, GNVST, SPCEHARRY06/10/21FLORIDAFABRICATIONBILLY RAY
58JB2771AAREDD940DIM8670ZZ4525TIR4012106031210610SFTPI, GNVST, GNVST, SPCEHARRY06/10/21FLORIDAFABRICATIONBILLY RAY
59JB27711AREDD940DIM8670ZZ4525TIR4012106031210610SFTPI, GNVST, GNVST, SPCEHARRY06/10/21FLORIDAFABRICATIONBILLY RAY
60JB27712AREDD940DIM8670ZZ4525TIR4012106031210610SFTPI, GNVST, GNVST, SPCEHARRY06/10/21FLORIDAFABRICATIONBILLY RAY
61JB27713AREDD940DIM8670ZZ4525TIR4012106031210610SFTPI, GNVST, GNVST, SPCEHARRY06/10/21FLORIDAFABRICATIONBILLY RAY
62JB27686AREDD940DIM8670ZZ4525TIR4012106031210610SFTPI, GNVST, GNVHT, GNVST, SPCEHARRY06/10/21FLORIDAFABRICATIONBILLY RAY
63JB27685ATRU6300DIM6300Z57126TIR4012106031210610SFTPI, GNVST, SPCEHARRY06/10/21DELWAREFABRICATIONBILLY RAY
64JB27673ADIS5750DIM6300Z57126TIR4012106031210610SFTPI, GNVHT, GNVST, SPCEHARRY06/10/21TEXASFABRICATIONBILLY RAY
65JB27654AREDD870DIM8650ZZ6154TIR4012106031210610SFTPI, GNVHT, GNVST, SPCEHARRY06/10/21KENTUCKYFABRICATIONBILLY RAY
66JB26893ATRU6300DIM6300Z6Z32ZTIR4012106021210602SFTPI, GNVHT, GNVST, SPCEHARRY06/02/21DELWAREFABRICATIONBILLY RAY
67JB26894ATRU6300DIM6300Z5Z637TIR4012106021210608SFTPI, GNVHT, GNVST, SPCEHARRY06/08/21DELWAREFABRICATIONBILLY RAY
68JB27653ATRU6300DIM6300Z62Z07TIR4012106031210610SFTPI, GNVHT, GNVST, SPCEHARRY06/10/21DELWAREFABRICATIONBILLY RAY
69JB27668ATRU6300DIM6300Z57126TIR4012106031210610SFTPI, GNVHT, GNVST, SPCEHARRY06/10/21DELWAREFABRICATIONBILLY RAY
70JB3A666ARED9740DIM6160ZZ3166TIR1012106091210616SFTGPS, PI, PVF, DMS, SPCEHARRY06/16/21KANSASFABRICATIONBILLY RAY
71JB293A6AREDC620JIM504ZZ347ZGOBBLER 4012106071210608DBYPI, PVF, PLM, DMS, PLM, FSPCEHARRY06/08/21KENTUCKYFABRICATIONBILLY RAY
72JB272A9AREDD020JIM504ZZ3274GOBBLER 4012106031210420DBYPI, PVF, DMS, MD1, FSPCEHARRY04/20/21KANSASFABRICATIONBILLY RAY
73JB27211AREDD020JIM504ZZ3274GOBBLER 4012106031210421DBYPI, PVF, DMS, MD1, FSPCEHARRY04/21/21KANSASFABRICATIONBILLY RAY
74JB27A23AREDD100JIM504ZZ59Z3GOBBLER 4012106021210520DBYPI, PVF, DMS, FSPCEHARRY05/20/21KANSASFABRICATIONBILLY RAY
75JB27212AREDC810JIM504413010INSERT4012106031210421DBYPI, PLU, PLM, FSPCEHARRY04/21/21ALABAMAFABRICATIONBILLY RAY
76JB3A8A4AREDC980JIM504ZZ1249GOBBLER 1012106091210610DBYGPS, PI, PVF, DMS, PLM, FSPCEHARRY06/10/21ATLANTAFABRICATIONBILLY RAY
77JB3A8A5AREDC980JIM504ZZ1249GOBBLER 1012106091210610DBYGPS, PI, PVF, DMS, PLM, FSPCEHARRY06/10/21ATLANTAFABRICATIONBILLY RAY
78JB3A8A6AREDC980JIM504ZZ1249GOBBLER 1012106091210610DBYGPS, PI, PVF, DMS, PLM, FSPCEHARRY06/10/21ATLANTAFABRICATIONBILLY RAY
79JB3A8A7AREDC980JIM504ZZ1249GOBBLER 1012106091210610DBYGPS, PI, PVF, DMS, PLM, FSPCEHARRY06/10/21ALABAMAFABRICATIONBILLY RAY
80JB2721AAREDD980JIM504ZZ3274GOBBLER 1012106031210421DBYGPS, PI, PVF, DMS, MD1, FSPCEHARRY04/21/21FLORIDAFABRICATIONBILLY RAY
81JB3A639AREDD980JIM504ZZ3274GOBBLER 1012106091210421DBYGPS, PI, PVF, DMS, MD1, FSPCEHARRY04/21/21FLORIDAFABRICATIONBILLY RAY
82JB2A6A7AREDD100JIM504ZZ5967GOBBLER 1012105241210524DBYGPS, PI, PVF, DMS, FSPCEHARRY05/24/21KANSASFABRICATIONBILLY RAY
83JB26525AREDD870JIM504ZZ6156TURNIP GREEN1012106021210607NDSGPS, BJU, FSPCEJIMBO06/07/21KENTUCKYFABRICATIONJIMMY BOOB
84JB2696AAREDD870JIM504ZZ6156TURNIP GREEN1012106021210608NDSGPS, BJU, FSPCEJIMBO06/08/21KENTUCKYFABRICATIONJIMMY BOOB
85JB26961AREDD870JIM504ZZ6156TURNIP GREEN1012106021210608NDSGPS, BJU, FSPCEJIMBO06/08/21KENTUCKYFABRICATIONJIMMY BOOB
86JB26949AREDD100JIM504ZZ6105TURNIP GREEN1012106021210408NDSGPS, BJU, FSPCEJIMBO04/08/21KANSASFABRICATIONJIMMY BOOB
87JB2695AAREDD100JIM504ZZ6105TURNIP GREEN1012106021210408NDSGPS, BJU, FSPCEJIMBO04/08/21KANSASFABRICATIONJIMMY BOOB
88JB26524AREDD870JIM504ZZ6156TURNIP GREEN4012106021210607NDSBJU, FSPCEJIMBO06/07/21KENTUCKYFABRICATIONJIMMY BOOB
89JB52A13AREDD100JIM504ZZ5422LEG ASSY-AISLE,NAR,Z110I4012101201210127NDSGIP, SPA, LCA, FSPCEJIM OR BOB?01/27/21KANSASFABRICATIONJANEY
90JB2822AAREDD730JIM504ZZ6302WISH BONES1012106071210607DBYGIP, FA4, FSPCETRACKY06/07/21KENTUCKYFABRICATIONJANEY
91JB28221AREDD730JIM504ZZ6302WISH BONES1012106071210607DBYGIP, FA4, FSPCETRACKY06/07/21KENTUCKYFABRICATIONJANEY
92JB16947AREDD870JIM504ZZ6302WISH BONES1012105181210517DBYGIP, FA4, FSPCETRACKY05/17/21KENTUCKYFABRICATIONJANEY
93JB2766AAREDD980JIM504Z77062JACKET4012106031210429NDSMBM, ST2, DB1, FSPCE, HP, VRHO, AGE, RSPCE, VRPRC, RSPCE, FSPCEJIM OR BOB?04/29/21FLORIDAFABRICATIONJANEY
94JB3A812ATRU8300DIM8300ZZ07Z6TIR1012106091210610SFTGPS, PLU, SPCEGOOBS06/10/21TEXASFABRICATIONBOBBS
95JB89723ADISD430DOM8670ZZ1209BUNGY BALL1012104011210408SFTGPS, GG2, SPCEBOBAN04/08/21TULSAFABRICATIONTERRAN
96JB27674AREDD870DIM8650ZZ547ZTIR1012106031210608SFTGPS, GG2, SPCEBOBAN06/08/21KENTUCKYFABRICATIONTERRAN
97JB3A698AREDD870DIM8650ZZ4475TIR1012106091210623SFTGPS, GG2, SPCEBOBAN06/23/21KENTUCKYFABRICATIONTERRAN
98JB24595ATRU6300DIM6300ZZ2655TIR1012105281210414SFTGPS, GG2, SPCEBOBAN04/14/21DELWAREFABRICATIONTERRAN
99JB21388ATRU6300DIM6300416510TIR CF21012105251210331SFTGPS, GG2, GG2, SPCE, GG2BOBAN03/31/21DELWAREFABRICATIONTERRAN
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M:MCell Valuebetween 36897 and 54808textNO
L:LCell Valuebetween 36911 and 54808textNO




this is the data I want to move to sheet 2

testing.xlsm
ABCDEFGHIJK
1DUE DTORDNOREFNOFITEMDESCR REMAININGAREAFOCALDT ACCEPTEDDT REJECTEDTARGET
204/29/21JBAA146AREDD870VFMZZ4VFM TOOLGNVHTTOOL SHOPZEEKYKENTUCKY
306/08/21JB27A38ADISB690SAZZ2ZSA TOOLGNVHTTOOL SHOPZEEKYALABAMA
406/10/21JB237A5AMTRMLDISA2176SA TOOLGNVHTTOOL SHOPZEEKYVEGAS
506/08/21JB27A39AREDD190ADTZZ3DOOM TOOLGNVHTTOOL SHOPZEEKYTEXAS
604/26/21JB94A3AAREDC620HFZZ34HF TOOLGNVHTTOOL SHOPZEEKYKENTUCKY
706/02/21JB26126AREDD870HFZZ4ZHF TOOLGNVHTTOOL SHOPZEEKYKENTUCKY
806/02/21JB26127AREDD870FMLDZZJETSONGNVHTTOOL SHOPZEEKYKENTUCKY
905/26/21JB22253AREDD870SF4165SF TOOLGNVHTTOOL SHOPZEEKYKENTUCKY
1006/10/21JB3A667AREDD980HFZZ32HF TOOLGNVHTTOOL SHOPZEEKYFLORIDA
1104/15/21JB94269ATRU6300ADT241CUU TOOLGNVHTTOOL SHOPZEEKYDELWARE
1206/03/21JB23715ATRU6300FDZ650FD TOOLGNVHTTOOL SHOPZEEKYDELWARE
1306/03/21JB23714ATRU6300PBDZ65PBD TOOLGNVHTTOOL SHOPZEEKYDELWARE
1406/03/21JB23696ATRU6300PBFDZ4PBFD TOOLGNVHTTOOL SHOPZEEKYDELWARE
1506/16/21JB3A799ADISC3602SAZZ4SAGNVHTTOOL SHOPZEEKYMAINE
1606/16/21JB3A723ADISC360SAZZ49SHOP AIDE TOOLGNVHTTOOL SHOPZEEKYMAINE
1706/16/21JB3A731ADISC360SAZZ49SHOP AIDE TOOLGNVHTTOOL SHOPZEEKYMAINE
1806/10/21JB237A4AMTRMLDISA4110SA TOOLBJU, FSPCETOOL SHOPZEEKYVEGAS
1905/31/21JB21654AREDD8702ADTZZDIM TOOLBJU, FSPCETOOL SHOPZEEKYKENTUCKY
2005/31/21JB21656AREDD8703ADTZZDIM TOOLBJU, FSPCETOOL SHOPZEEKYKENTUCKY
2105/31/21JB21611AREDD870ADTZZ5DOG TOOLBJU, FSPCETOOL SHOPZEEKYKENTUCKY
2205/27/21JB23635AREDD870ZZ4Z30TIRBJU, FSPCETOOL SHOPZEEKYKENTUCKY
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J:JCell Valuebetween 36897 and 54808textNO
I:ICell Valuebetween 36911 and 54808textNO





and for every unique value in column p keep putting the data in new sheets
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Saurabhj

Well-known Member
Joined
Jun 6, 2020
Messages
661
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi,

I used a button and on click of button used below VBA to move data from one sheet to another. Sheet4 is the target sheet.

Please check if this solves the problem.

VBA Code:
Private Sub cmdMoveData_Click()
Dim lastRow As Integer, rowno As Integer
Dim destRowNo As Integer
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
destRowNo = 2
For rowno = 2 To lastRow
    If ActiveSheet.Range("P" & rowno) = "TOOL SHOP" Then
        Sheets("Sheet4").Cells(destRowNo, 1) = ActiveSheet.Range("N" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 2) = ActiveSheet.Range("A" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 3) = ActiveSheet.Range("B" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 4) = ActiveSheet.Range("D" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 5) = ActiveSheet.Range("E" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 6) = ActiveSheet.Range("J" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 7) = ActiveSheet.Range("P" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 8) = ActiveSheet.Range("Q" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 9) = ActiveSheet.Range("L" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 10) = ActiveSheet.Range("M" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 11) = ActiveSheet.Range("O" & rowno)
        destRowNo = destRowNo + 1
    End If
Next
Application.ScreenUpdating = True
End Sub
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,332
Office Version
  1. 365
Platform
  1. Windows
Hello LTL4L,

My understanding of your query is that you have one "Master" sheet and four destination sheets(named "TOOL SHOP", "STOCK ROOM", "QA INSPECTION" AND "FABRICATION").
In Column P, you have criteria named the same as the destination worksheets and you'd like a VBA code to iterate through the criteria and transfer the relevant rows of data to their respective worksheets. If I've assumed correctly, then this code should do the task for you:-

VBA Code:
Option Explicit
Sub Test()

        Dim sh As Worksheet, wsD As Worksheet, x As Long, nrow As Long
        Dim ar As Variant, ClArr As Variant, pArr As Variant, i As Long
        
        ar = Array("TOOL SHOP", "STOCK ROOM", "QA INSPECTION", "FABRICATION")
        ClArr = Array(14, 1, 2, 4, 5, 10, 16, 17, 15)
        pArr = Array("A", "B", "C", "D", "E", "F", "G", "H", "K")
        Set sh = Sheet1

Application.ScreenUpdating = False
        
        For i = 0 To UBound(ar)
                        Set wsD = Sheets(ar(i))
                        wsD.UsedRange.Offset(1).Clear
                        nrow = wsD.Cells(Rows.Count, 1).End(xlUp).Row + 1
                With sh.[A1].CurrentRegion
                        .AutoFilter 16, ar(i)
                                With .Offset(1)
                                        For x = LBound(ClArr) To UBound(ClArr)
                                              .Columns(ClArr(x)).Copy wsD.Range(pArr(x) & nrow)
                                        Next x
                                End With
                        .AutoFilter
                        wsD.Columns.AutoFit
                End With
        Next i
        
MsgBox "All done", vbExclamation
Application.ScreenUpdating = True

End Sub

I've created a sample workbook based on your supplied information which you'll find here.
Click on the "TEST" button to see how it works.

I hope that this helps.

Cheerio,
vcoolio.
 
Solution

livetolearn4life

New Member
Joined
Jul 14, 2020
Messages
15
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Hi,

I used a button and on click of button used below VBA to move data from one sheet to another. Sheet4 is the target sheet.

Please check if this solves the problem.

VBA Code:
Private Sub cmdMoveData_Click()
Dim lastRow As Integer, rowno As Integer
Dim destRowNo As Integer
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
destRowNo = 2
For rowno = 2 To lastRow
    If ActiveSheet.Range("P" & rowno) = "TOOL SHOP" Then
        Sheets("Sheet4").Cells(destRowNo, 1) = ActiveSheet.Range("N" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 2) = ActiveSheet.Range("A" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 3) = ActiveSheet.Range("B" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 4) = ActiveSheet.Range("D" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 5) = ActiveSheet.Range("E" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 6) = ActiveSheet.Range("J" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 7) = ActiveSheet.Range("P" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 8) = ActiveSheet.Range("Q" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 9) = ActiveSheet.Range("L" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 10) = ActiveSheet.Range("M" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 11) = ActiveSheet.Range("O" & rowno)
        destRowNo = destRowNo + 1
    End If
Next
Application.ScreenUpdating = True
End Sub
Hi Saurabhj,
thanks for the work making this code. It does do what i was asking for in my post. i just need for the code to keep looping for every unique value in P and put them on separate sheets and there was another post where someone read my mind, lol.. it is an array and i will go with that one. again thanks so much!
 

livetolearn4life

New Member
Joined
Jul 14, 2020
Messages
15
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Hello LTL4L,

My understanding of your query is that you have one "Master" sheet and four destination sheets(named "TOOL SHOP", "STOCK ROOM", "QA INSPECTION" AND "FABRICATION").
In Column P, you have criteria named the same as the destination worksheets and you'd like a VBA code to iterate through the criteria and transfer the relevant rows of data to their respective worksheets. If I've assumed correctly, then this code should do the task for you:-

VBA Code:
Option Explicit
Sub Test()

        Dim sh As Worksheet, wsD As Worksheet, x As Long, nrow As Long
        Dim ar As Variant, ClArr As Variant, pArr As Variant, i As Long
       
        ar = Array("TOOL SHOP", "STOCK ROOM", "QA INSPECTION", "FABRICATION")
        ClArr = Array(14, 1, 2, 4, 5, 10, 16, 17, 15)
        pArr = Array("A", "B", "C", "D", "E", "F", "G", "H", "K")
        Set sh = Sheet1

Application.ScreenUpdating = False
       
        For i = 0 To UBound(ar)
                        Set wsD = Sheets(ar(i))
                        wsD.UsedRange.Offset(1).Clear
                        nrow = wsD.Cells(Rows.Count, 1).End(xlUp).Row + 1
                With sh.[A1].CurrentRegion
                        .AutoFilter 16, ar(i)
                                With .Offset(1)
                                        For x = LBound(ClArr) To UBound(ClArr)
                                              .Columns(ClArr(x)).Copy wsD.Range(pArr(x) & nrow)
                                        Next x
                                End With
                        .AutoFilter
                        wsD.Columns.AutoFit
                End With
        Next i
       
MsgBox "All done", vbExclamation
Application.ScreenUpdating = True

End Sub

I've created a sample workbook based on your supplied information which you'll find here.
Click on the "TEST" button to see how it works.

I hope that this helps.

Cheerio,
vcoolio.
Hello LTL4L,

My understanding of your query is that you have one "Master" sheet and four destination sheets(named "TOOL SHOP", "STOCK ROOM", "QA INSPECTION" AND "FABRICATION").
In Column P, you have criteria named the same as the destination worksheets and you'd like a VBA code to iterate through the criteria and transfer the relevant rows of data to their respective worksheets. If I've assumed correctly, then this code should do the task for you:-

VBA Code:
Option Explicit
Sub Test()

        Dim sh As Worksheet, wsD As Worksheet, x As Long, nrow As Long
        Dim ar As Variant, ClArr As Variant, pArr As Variant, i As Long
       
        ar = Array("TOOL SHOP", "STOCK ROOM", "QA INSPECTION", "FABRICATION")
        ClArr = Array(14, 1, 2, 4, 5, 10, 16, 17, 15)
        pArr = Array("A", "B", "C", "D", "E", "F", "G", "H", "K")
        Set sh = Sheet1

Application.ScreenUpdating = False
       
        For i = 0 To UBound(ar)
                        Set wsD = Sheets(ar(i))
                        wsD.UsedRange.Offset(1).Clear
                        nrow = wsD.Cells(Rows.Count, 1).End(xlUp).Row + 1
                With sh.[A1].CurrentRegion
                        .AutoFilter 16, ar(i)
                                With .Offset(1)
                                        For x = LBound(ClArr) To UBound(ClArr)
                                              .Columns(ClArr(x)).Copy wsD.Range(pArr(x) & nrow)
                                        Next x
                                End With
                        .AutoFilter
                        wsD.Columns.AutoFit
                End With
        Next i
       
MsgBox "All done", vbExclamation
Application.ScreenUpdating = True

End Sub

I've created a sample workbook based on your supplied information which you'll find here.
Click on the "TEST" button to see how it works.

I hope that this helps.

Cheerio,
vcoolio.

Hello Vcoolio,

sorry i didn't explain it well in my post but you assumed 100% correct. The Code works great! thanks for your work it is greatly appreciated!

p.s. i got to learn how to do this array stuff.
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,332
Office Version
  1. 365
Platform
  1. Windows
You're welcome LTL4L. I'm glad to have been able to assist and thanks for the feed back.

Cheerio,
vcoolio.
 

Forum statistics

Threads
1,176,247
Messages
5,902,125
Members
434,941
Latest member
seekinganswers3

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
Top