none
Use Data from a Specific Sheet RRS feed

  • Question

  • I have a sheet named 'a' containing all data containing.

    I want to create a new sheet named 'b' and filtrate and retrieve specific data from the sheet 'a' and use it in sheet 'b'

    How should I do it?

    Sunday, October 26, 2014 7:44 AM

Answers

  • Hi Fullmetalboy,

    It's easy in VBA for Excel, you mean you want to do this programmatically right?

    Besides the answer in Amol's reply using Rang.AutoFilter, you could also loop through all the rows in Sheet a, find the needed rows, copy them into Sheet b.

    Check the sample code in this KB article:

    How to loop through a list of data on a worksheet by using macros in Excel

    I paste one of the function you could use here:

    To Search a List with a Constant, Known Number of Rows

    Sub Test1()

          Dim x As Integer

          ' Set numrows = number of rows of data.

          NumRows = Range("A2",   Range("A2").End(xldown)).Rows.Count

          ' Select cell a1.

          Range("A2").Select

          ' Establish "For" loop to   loop "numrows" number of times.

          For x = 1 To NumRows

             ' Insert your code here.

             ' Selects cell down 1 row from   active cell.

             ActiveCell.Offset(1, 0).Select

          Next

       End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 27, 2014 8:06 AM
    Moderator

All replies

  • Can you elaborate more regarding data in sheet 'a' and criteria for filter and specific data?

    See below link for example.

    https://social.msdn.microsoft.com/Forums/office/en-US/19328ea2-fd1a-427c-98e3-b096ab62e5f2/vba-code-to-filter-data-copy-and-paste-in-new-sheet?forum=exceldev
    Monday, October 27, 2014 6:29 AM
  • Hi Fullmetalboy,

    It's easy in VBA for Excel, you mean you want to do this programmatically right?

    Besides the answer in Amol's reply using Rang.AutoFilter, you could also loop through all the rows in Sheet a, find the needed rows, copy them into Sheet b.

    Check the sample code in this KB article:

    How to loop through a list of data on a worksheet by using macros in Excel

    I paste one of the function you could use here:

    To Search a List with a Constant, Known Number of Rows

    Sub Test1()

          Dim x As Integer

          ' Set numrows = number of rows of data.

          NumRows = Range("A2",   Range("A2").End(xldown)).Rows.Count

          ' Select cell a1.

          Range("A2").Select

          ' Establish "For" loop to   loop "numrows" number of times.

          For x = 1 To NumRows

             ' Insert your code here.

             ' Selects cell down 1 row from   active cell.

             ActiveCell.Offset(1, 0).Select

          Next

       End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 27, 2014 8:06 AM
    Moderator