Solved: Copying rows from one sheet to another

[lanhao]

Hi there, I think I will have to clear out the old post I had regarding copying unique rows. The information I was looking at was a little different than I had anticipated.

The situation is basically this - i have to have excel copy some information from one sheet to another depending on whether or not a series of information in three different cells match up in a row.

I have most of the code covered, the only part that is the problem is having it check to see if the information first shows up in column A (just as an example), if it does, it then would verify if columns b and C are the same. If they are, it skips that row and moves onto the next one.

Can the address function with using offset possibly take care of this issue or is there some other thing that would be alot simpler in design.

As always thanks for the help on this. This issue has been stumping me for about a week now.

[Ken Puls]

Honestly, this is what I would do...

Rather than looping through your cells to check if they meet the criteria, find out what your last column is. Then...
-Use VBA to place an index formula in the next column, just counting from 1 to the last record. (Row 1 = 1, Row 2+ = Row 1.value + 1)
-Use VBA to place a formula in the next column to evaluate if the conditions are true. Something like =if(and(A1="dog",B1="big",C1="hairy"),1,0)
-Sort by your now last column to group all the 1's together
-Add an Autofilter to filter all the 1's
-Copy them to the other sheet
-Remove the autofilter
-Sort by the second last column to put your data back in order
-Delete the last 2 columns so that the data looks normal again.

This may be a bit more than you're used to programming, but will leverage Excel's object model in the best way, making your code execution as fast as possible. The reason I'm suggesting the above is:
-Formulas are faster than evaluating by looping
-Specialcells can only deal with a certain number of non-contiguous cells, hence the sort
-The index is to sort it back to the same order.

I have an article at my site on deleting duplicates using this method that you could convert: http://www.excelguru.ca/node/24

If you need help adjusting it, let us know. :)

[lanhao]

well, i will attempt to work at it, i printed off the code you mentioned and i am going to try and customize it to what is needed. I think i understood what you were trying to go with on it.

I'm using Excel 2000 at work, and I think everything should fall into place with it. I will post whatever code I am having any problems with and see how well it works out. Thanks for the response btw :)

[lanhao]

though here is a question - what if there are multiple sheets?

This is why I think it's being a bigger problem than it already is... by default the way the page where it is grabbing the information from is already in date order... because they are set to automatically add the date the record was put in

Doing the autofilter by the lead name (which is also the same name as the worksheet it's supposed to go to) will have it looking at the right place, but the thing i am concerned with is that the names are going to be changing soon since there is a rolloever to new teams. Basically I need something that would not require updating that information again once the change occurs, and having it hide in the code would not be condusive (i also have to make this easy for others to use)... Any thoughts on that part.

I was thinking of using the the address/offset fucntion to have it look at other information on the row once it finds the main information. Think that might work for my needs?

[Ken Puls]

Is there any way you could attach a sample workbook with the before/after type view? Just sample data, nothing confidential, but something to demonstrate the issue.

I think it might make things much easier to work through here.

[lanhao]

Well, here's the most recent batch of code I have put together tied to this workbook. Nothing proprietary at all on it. I had an idea based on some stuff I was reading up while searching for a possible solution. The code does work nicely, however, if it finds a match - i need it to check two other cells in the row. If those match - then it won't copy, if either of them don't match - it will copy.

[lanhao]

I figured I would mark it as solved, since i did get something to work. I am including the code, since this was an odd one - well for me at any rate.

Thank you all for your help :)

Sub Datamove()
'
' Datamove Macro
' Macro recorded 10/13/2006 by Andy Lewis
'
'Baseline variable list
Set sht1 = Worksheets("Uncorrected QC")
'Counters for respective worksheet pages
Dim i As Integer
Dim k As Integer 'Row counter for sht1
Dim v As Integer
Dim tick As Long 'Counter for records copied
Dim eRow As Long 'Last row on sht2
Dim sht2 As Worksheet 'worksheet that will change name depending on a value
Dim Tac As String, Trep As String, Tindt As String 'values based on the find function
Application.ScreenUpdating = False
k = 2
v = 2
tick = 0
With sht1
For v = 2 To sht1.Cells(Rows.Count, "A").End(xlUp).Row 'Goes through each row on sht1
Dim shName As String
shName = sht1.Cells(k, "H")
Set sht2 = Sheets(shName)
eRow = sht2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
Dim c As Range
Set c = sht2.Columns(2).Find(sht1.Cells(k, "B").Value)
If c Is Nothing Then 'If it finds no match, it copies the row from sht1 to the respective sheet
Set c = Nothing
sht1.Rows(k).Copy Destination:=sht2.Rows(eRow)
tick = tick + 1
Else 'If it does find a match value wise, it compares those two cells as well to see if they match
'MsgBox "Already Exists"
Tac = c.Address
Trep = c.Offset(0, 2).Value
Tindt = c.Offset(0, 3).Value
If Trep <> sht1.Cells(k, "D").Value Or Tindt <> sht1.Cells(k, "E").Value Then
sht1.Rows(k).Copy Destination:=sht2.Rows(eRow)
tick = tick + 1
'If it finds that either of the two variables don't match - it will copy the row over
End If
'v = v + 1
'Does nothing else
End If
k = k + 1
Next v
MsgBox "Records copied: " & tick
End With
Application.ScreenUpdating = True
End Sub

[Ken Puls]

Sorry I didn't have a chance to help a bit more, but I'm glad you got a working solution.

I would definately encourage you to look at the Autofilter route for future solutions, though, as you'll find it much faster than looping. Maybe not the best solution here (I haven't really had a chance to look at your workbook), but definately something to keep on the toolbelt.

:)

[lanhao]

I will definitely look into that, only problem is that I needed to get something together for work on this - since this project has (in my opinion) taken longer than it should have getting to work, but that's all my problem with not being able to get something put together properly.

I think it will be something that can be used next time for it. I'm also thinking that idea of using the '1' as an indicator of whether or not the row has been copied will be a handy thing to use on some other things i have to put together.