Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

Excel VBA - Conditional copy data from one sheet to other

Posted on 2013-06-06
10
Medium Priority
?
6,359 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
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…
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…

647 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