Solved

Excel VBA - Conditional copy data from one sheet to other

Posted on 2013-06-06
10
5,135 Views
Last Modified: 2013-06-12
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
0
Comment
Question by:Sharath
  • 5
  • 5
10 Comments
 
LVL 6

Expert Comment

by:Michael
ID: 39228107
Hi Sharath,

can you post a file with sample data?
0
 
LVL 40

Author Comment

by:Sharath
ID: 39228238
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
 
LVL 6

Expert Comment

by:Michael
ID: 39228252
I don't see an attachment. Can you try again?
0
 
LVL 40

Author Comment

by:Sharath
ID: 39228258
Sorry, missed the attachment.
EE.xlsx
0
 
LVL 6

Accepted Solution

by:
Michael earned 250 total points
ID: 39228387
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 0 total points
ID: 39230010
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
 
LVL 6

Assisted Solution

by:Michael
Michael earned 250 total points
ID: 39230506
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
 
LVL 40

Author Comment

by:Sharath
ID: 39231142
0
 
LVL 6

Expert Comment

by:Michael
ID: 39231726
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
 
LVL 40

Author Closing Comment

by:Sharath
ID: 39240457
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now