Excel VBA - Conditional copy data from one sheet to other

I have a workbook with three sheets. Data exists in Sheet2. In Sheet2, column A, I have criteria name, say Criteria1, Criteria2 etc for those records in Sheet2. I would like to enter my critieira in Sheet1, starting from B2 in subsequent cells and want to copy the data from Sheet2 to Sheet3 based on the criteria in Sheet1. Not much familiar with VBA, If anyone already done this type work and willing to help, I will really appricate.
Please let me know if it is unclear and need more details.

Thanks
Sharath
LVL 41
SharathData EngineerAsked:
Who is Participating?
 
MichaelConnect With a Mentor Business AnalystCommented:
Hi, thanks for the file :)

Try the following:
Sub copyFilteredRng()
    
    Dim Criteria As Variant
    Dim FilterRange As Range
    Dim i As Integer
    
    With Sheets("Sheet1")
        Set FilterRange = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
    End With
    
    ReDim Criteria(1 To FilterRange.Cells.Count)
    For Each cell In FilterRange
    i = i + 1
        Criteria(i) = cell.Value
    Next

    With Sheets("Sheet2").Range("A1")
        .AutoFilter _
                Field:=1, _
                Criteria1:=Criteria, _
                Operator:=xlFilterValues
        .CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Sheet3").Range("A1")
        .AutoFilter
    End With
End Sub

Open in new window

If you have any questions, please let me know.
0
 
MichaelBusiness AnalystCommented:
Hi Sharath,

can you post a file with sample data?
0
 
SharathData EngineerAuthor Commented:
Thanks JazzyJoop for the response.

Attached sample file. In Sheet1, I have criteria in B2 and subsequent cells. Need to copy data from Sheet2 to Sheet3 based on the criteria in Sheet2.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
MichaelBusiness AnalystCommented:
I don't see an attachment. Can you try again?
0
 
SharathData EngineerAuthor Commented:
Sorry, missed the attachment.
EE.xlsx
0
 
SharathConnect With a Mentor Data EngineerAuthor Commented:
Thanks JazzyJoop.Its working. Can you please explain this portion?
With Sheets("Sheet2").Range("A1")
        .AutoFilter _
                Field:=1, _
                Criteria1:=Criteria, _
                Operator:=xlFilterValues
        .CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Sheet3").Range("A1")
        .AutoFilter
    End With

Open in new window

0
 
MichaelConnect With a Mentor Business AnalystCommented:
Hi Sharath, I'm glad it worked for you. See below short comments on what the code does.

With Sheets("Sheet2").Range("A1")
        'The first part uses AutoFilter to filter the datarange based on your criteria
        'Rows which do not meet the criteria are hidden
        .AutoFilter _
                Field:=1, _
                Criteria1:=Criteria, _
                Operator:=xlFilterValues

        'The second part copies the visible cells in the datarange to the destination sheet
        .CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Sheet3").Range("A1")

        'This disables the autofilter again; all cells on sheet 2 are visible again
        .AutoFilter
    End With

Open in new window


Hope this explains it. If you have any further question, please specify on which particular piece of code.

Regards,
Joop
0
 
SharathData EngineerAuthor Commented:
0
 
MichaelBusiness AnalystCommented:
Hi Sharath,

I started out with these books, which I found very helpful to grasp the basics:
Excel Power Programming with VBA by John Walkenbach
VBA and Macros: Microsoft Excel by Bill Jelen

Personally, I found the writing style and explaining of the second more appealing, but with either one you are getting a good book.
0
 
SharathData EngineerAuthor Commented:
Thanks  JazzyJoop for explaining this. Its working fine. Need to learn some techniques in VBA. Off the topic, do you have any suggestions/books to start learning VBA. There is lot of material available online and I am getting it in bits and pieces. Not sure where to start. Thanks anyways.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.