Copy data of 1 sheet to another if criteria matched

[Closed]
Report
-
Posts
1316
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 21, 2021
-
Hello,

1 have 3 sheet master, damage & shortage. in master sheet if i enter in n column damage then full row should be copied in sheet damage also & same way for shortage. pls help

2 replies

Posts
1316
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 21, 2021
237
Hello Preeti,

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.
thanks it works but pls provide for column X instead of N & where should this formula to be copied
Posts
1316
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 21, 2021
237
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.