Solved

Copy and Paste data if Data matched

Posted on 2011-02-16
2
768 Views
Last Modified: 2012-06-21
Hi Experts,

I would like to request Experts help to modify the attached script to allow copy data from Filter sheet into Archive sheet if the data at Column D is matched with data at Column G. Data at Column I need to be omitted. Hope Experts will help me to modify the script. I have attached as well the workbook for Experts perusal.



Sub CopyOver()
Dim sheetFilter As Worksheet
Dim sheetArchive As Worksheet
Set sheetFilter = Worksheets("Filter")
Set sheetArchive = Worksheets("Archive")
Dim collectRange As Range, curRange As Range
Set curRange = sheetFilter.Range("A2")
While curRange.Value <> ""
    If curRange.Offset(, 3) = curRange.Offset(, 6) And _
        curRange.Offset(, 6) = curRange.Offset(, 8) Then
        If collectRange Is Nothing Then
            Set collectRange = curRange
        Else
            Set collectRange = Union(collectRange, curRange)
        End If
    End If
    Set curRange = curRange.Offset(1)
Wend
Dim targetRange As Range
Set targetRange = sheetArchive.Range("A" & Rows.Count).End(xlUp)
If targetRange.Value <> "" Then Set targetRange = targetRange.Offset(1)
If Not collectRange Is Nothing Then
    Application.EnableEvents = False
    collectRange.EntireRow.Copy targetRange
    collectRange.EntireRow.Delete
    Sheets("Filter").Range("A2:A65536").SpecialCells(2).EntireRow.Delete
    Application.EnableEvents = True
End If
End Sub

Open in new window

DataArchive.xls
0
Comment
Question by:Cartillo
2 Comments
 
LVL 3

Accepted Solution

by:
imaki06 earned 500 total points
ID: 34913911
I cannot see the problem. The macro does copy the lines where D and G columns has equal values..

Do you want to modify the macro to not check column I or do not want to copy column I to archive?

If the first is what you want, then just remove checking from lines 9 and 10..
change:
..
 If curRange.Offset(, 3) = curRange.Offset(, 6) And _
        curRange.Offset(, 6) = curRange.Offset(, 8) Then
        If collectRange Is Nothing Then
..
to:
..
 If curRange.Offset(, 3) = curRange.Offset(, 6)  Then
        If collectRange Is Nothing Then
..
0
 

Author Closing Comment

by:Cartillo
ID: 34914015
Thanks for the help
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

937 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

5 Experts available now in Live!

Get 1:1 Help Now