Solved

Excel VBA - Conditional copy data from one sheet to other

Posted on 2013-06-06
10
5,447 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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
 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

770 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