Copy data of 1 sheet to another if criteria matched
Closed
preeti
-
Aug 30, 2016 at 05:19 AM
vcoolio
vcoolio
- Posts
- 1347
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- June 10, 2022
Related:
- Copy data from one sheet to another if criteria met
- How to transfer data from 1 sheet to another in excel - Guide
- Transfer data from 1 sheet to another with cond. ✓ - Forum - Excel
- Filtering data from 1 sheet to another ✓ - Forum - Excel
- How to copy a data from one sheet to another, when specific criteria is met - Forum - Excel
- Copy data from 1 workbook to another based on sheet tabs ✓ - Forum - Excel
2 replies
vcoolio
Aug 30, 2016 at 07:14 AM
- Posts
- 1347
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- June 10, 2022
Aug 30, 2016 at 07:14 AM
Hello Preeti,
Perhaps the following code will do the task for you:-
The code filters Column N in the Master sheet (sheet1) for the criteria "Damage" and "Shortage" and then transfers the relevant row of data to the appropriate sheet.
Following is the link to my test work book for you to peruse:-
https://www.dropbox.com/s/7p31hcb08wz7i1o/Preeti%28Master%20sht%20to%20multi%20shts%2Cautofilter%29.xlsm?dl=0
Enter the criteria "Damage" or "Shortage" in any cell in Column N of sheet1 as many times as you like then click on the run button to see it work.
I hope that this helps.
Cheerio,
vcoolio.
Perhaps the following code will do the task for you:-
Option Explicit Sub TransferData() Application.ScreenUpdating = False Dim ar As Variant Dim i As Integer ar = Array("Damage", "Shortage") For i = 0 To UBound(ar) Sheets(ar(i)).UsedRange.ClearContents Sheet1.Range("N2", Sheet1.Range("N" & Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, ar(i) [A2].CurrentRegion.Copy Sheets(ar(i)).Range("A" & Rows.Count).End(3) Sheets(ar(i)).Columns.AutoFit Next i [N2].AutoFilter Application.CutCopyMode = False Application.ScreenUpdating = True MsgBox "Data transfer completed!", vbExclamation, "Status" End Sub
The code filters Column N in the Master sheet (sheet1) for the criteria "Damage" and "Shortage" and then transfers the relevant row of data to the appropriate sheet.
Following is the link to my test work book for you to peruse:-
https://www.dropbox.com/s/7p31hcb08wz7i1o/Preeti%28Master%20sht%20to%20multi%20shts%2Cautofilter%29.xlsm?dl=0
Enter the criteria "Damage" or "Shortage" in any cell in Column N of sheet1 as many times as you like then click on the run button to see it work.
I hope that this helps.
Cheerio,
vcoolio.
vcoolio
Aug 30, 2016 at 08:10 AM
- Posts
- 1347
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- June 10, 2022
Aug 30, 2016 at 08:10 AM
Hello Preeti,
In line 13 in the code above, wherever you see an "N" change it to an "X".
To implement the code, press Alt + F11 to open the VB Editor. In the ribbon at the top, click on Insert and then select Module. In the big white field that then appears on the right hand side, paste the above code. You will then need to assign the macro to a button to run the code. Alternatively, you can run the code by pressing Alt + F8 and in the Macro dialogue box that appears, select the code name (in this case, it is named "TransferData") and then click on "Run".
Cheerio,
vcoolio.
In line 13 in the code above, wherever you see an "N" change it to an "X".
To implement the code, press Alt + F11 to open the VB Editor. In the ribbon at the top, click on Insert and then select Module. In the big white field that then appears on the right hand side, paste the above code. You will then need to assign the macro to a button to run the code. Alternatively, you can run the code by pressing Alt + F8 and in the Macro dialogue box that appears, select the code name (in this case, it is named "TransferData") and then click on "Run".
Cheerio,
vcoolio.
Aug 30, 2016 at 07:40 AM