none
Pasting colums in different sheet using VBA RRS feed

  • Question

  • I have the below code that will find and give the count if it matches the specific criteria:

    Sub WBR()
    Dim Count1Criteria As Variant
    Dim Count3Criteria As Variant
    Dim test As Variant
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    
    Count1Criteria = Array(Array("AE4", "AE5", "Latency", "O:O", "Pass", "Fail"))
    For Each test In Count1Criteria
    With Worksheets(test(2))
        Range(test(0)) = wf.CountIfs(.Range(test(3)), test(4))
        Range(test(1)) = wf.CountIfs(.Range(test(3)), test(5))
        End With
        End Sub

    I want to do the following:

    • Once this criteria is met, instead of adding the count to the designated cell, the values in "M" col (named as "LATENCY") should be copied and pasted in "D" col in sheet.

    Wednesday, January 11, 2017 5:29 AM

All replies

  • Which sheet would you like to copy? You can use something like

    Sheets("Sheet2").Activate
    Range("D1").value=Range(test(0))
    Sheets("Sheet1").Activate

    You can put a counter variable in for the D column so that it pastes values in the next row each time you have a new match.

    Hope this helps.


    Kish Learning never ends!! Please don't forget to click "Mark As Answer" for the post in which you found your solution.

    Wednesday, January 11, 2017 6:00 AM
  • Thanks for the answer. Sorry if i was not clear with my question.

    If the search criteria matches, then the "M" col in that sheet should be copied and pasted in another sheet (shhet2) in D column.

    Wednesday, January 11, 2017 6:25 AM
  • Replace Sheet1 by your exisiting sheet name and Sheet2 by your destination sheet name.

    last_row = Cells.Find("*", SearchOrder:=XlSearchOrder.xlByRows, SearchDirection:=XlSearchDirection.xlPrevious).Row Range("M1:M" & last_row").select Selection.Copy Sheets("Sheet2").Activate Range("D1").Select Activesheet.Paste Sheets("Sheet1").Activate

    This will copy the values in M column from Sheet1 and paste in Column D of Sheet2.


    Kish Learning never ends!! Please don't forget to click "Mark As Answer" for the post in which you found your solution.

    • Proposed as answer by KishlayaB Thursday, January 12, 2017 4:26 AM
    Wednesday, January 11, 2017 6:48 AM
  • awesome..Where should i use this code? below the code that i have? (the one i have pasted)
    Wednesday, January 11, 2017 6:52 AM
  • I don't see the code where you test the criteria. What is your criteria? You will probably need an If clause to test it and inside that you can put my code.


    Kish Learning never ends!! Please don't forget to click "Mark As Answer" for the post in which you found your solution.

    Wednesday, January 11, 2017 7:07 AM
  • This is the code with criteria :

    Sub WBR()
    Dim Count1Criteria As Variant
    Dim Count3Criteria As Variant
    Dim test As Variant
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    
    Count1Criteria = Array(Array("AE4", "AE5", "Latency", "O:O", "Pass", "Fail"))
    For Each test In Count1Criteria
    With Worksheets(test(2))
        Range(test(0)) = wf.CountIfs(.Range(test(3)), test(4))
        Range(test(1)) = wf.CountIfs(.Range(test(3)), test(5))
        End With
        End Sub
    CountIfs does the job. Where do I add your code in this?

    Wednesday, January 11, 2017 7:37 AM
  • Countifs will give you the number of occurences. It is not a criteria by itself. 

    Kish Learning never ends!! Please don't forget to click "Mark As Answer" for the post in which you found your solution.

    Wednesday, January 11, 2017 8:14 AM
  • Yes, sorry. What i meant was the criteria is inside countifs -
     wf.CountIfs(.Range(test(3)), test(4))
    Wednesday, January 11, 2017 8:45 AM
  • Yes, sorry. What i meant was the criteria is inside countifs -
     wf.CountIfs(.Range(test(3)), test(4))
    Assuming you are expecting 2 occurences 
    If wf.Countifs(.Range(test(3)),test(4)) =2 Then
    
    last_row = Cells.Find("*", SearchOrder:=XlSearchOrder.xlByRows, SearchDirection:=XlSearchDirection.xlPrevious).Row
    Range("M1:M" & last_row").select
    Selection.Copy
    Sheets("Sheet2").Activate
    Range("D1").Select
    Activesheet.Paste
    Sheets("Sheet1").Activate
    
    End If


    Kish Learning never ends!! Please don't forget to click "Mark As Answer" for the post in which you found your solution.

    Wednesday, January 11, 2017 8:57 AM
  • Sorry again. I dont want the count to meet a particular value for this to happen.

    I just want to filter data (using keywords) and then select the values corresponding in Col M and paste it in different sheet.

    Wednesday, January 11, 2017 10:19 AM
  • In that case any value other than zero should work. Replace =2 with <>0. But I am not sure this is an efficient way to do this.

    Kish Learning never ends!! Please don't forget to click "Mark As Answer" for the post in which you found your solution.

    Wednesday, January 11, 2017 10:36 AM