?
Solved

Excel VBA - Conditional copy data from one sheet to other

Posted on 2013-06-06
10
Medium Priority
?
6,175 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 6

Expert Comment

by:Michael
ID: 39228107
Hi Sharath,

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

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 41

Author Comment

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

Accepted Solution

by:
Michael earned 1000 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 41

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

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 41

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

762 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