appending records
 

Here is my question:

I've got the code to do the following:

Sheets("Sheet1").Select
Range("A2:D100").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste

Pretty straight forward. It copies data from Sheet1 to Sheet2.

But when it's time to copy new data, I need to append it to the end of
sheet2, not put it in the active cell.

1. How would one go about doing that?
2. Also, how could I check for duplicates BEFORE copying to sheet2.
Basing a duplication as only havve duplicate entries for columns B and
C. Regardless of what's in Columns A and D.

Probably should use Worksheet("sheetnumber").Activate instead of
Sheets("sheetnumber").Select.

Thanks.
-Michael

appending records
 

Hi Michael

You don't have to select or activate either sheet:

Dim eRow As Long
eRow = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row + 1
Sheets("Sheet1").Range("A2:D100").Copy _
Sheets("Sheet2").Cells(eRow, 1)


With regard to removing duplicates you will need to give some more
detail. If you have two records with the same value (or is it text?) in
column B which do you want ot keep? Or do you want to add the values
from the other columns so that the records are consolidated?

Hope this helps
Rowan

wrote:
Here is my question:

I've got the code to do the following:

Sheets("Sheet1").Select
Range("A2:D100").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste

Pretty straight forward. It copies data from Sheet1 to Sheet2.

But when it's time to copy new data, I need to append it to the end of
sheet2, not put it in the active cell.

1. How would one go about doing that?
2. Also, how could I check for duplicates BEFORE copying to sheet2.
Basing a duplication as only havve duplicate entries for columns B and
C. Regardless of what's in Columns A and D.

Probably should use Worksheet("sheetnumber").Activate instead of
Sheets("sheetnumber").Select.

Thanks.
-Michael

appending records
 

Rowan,

Thanks for the code. I'll work on figuring out how it works (I don't
like to 'copy and paste'. I like to learn something along the way).

Here's an example:

datacol1 datacol2 datacol3 datacol4
row1 A B C C
row2 A C B D
row3 Z B C A
row4 Y C B H

In this example, I want to match on datacol2 and datacol3. Based on
that,
row1 = row3 and row2 = row4. I dissregard datacol1 and datacol4. I
want to keep row1 and row2 in this scenerio.

Thanks.
-Michael.

appending records
 

Hi Michael

If you don't mind having the data sorted then this is one way to get rid
of duplicates (assumes we are still talking about the range ("A2:D100"):

Sub dups()
Dim i As Integer
Range("A2:D100").Sort Key1:=Range("B2"), Order1:=xlAscending _
, Key2:=Range("C2"), Order2:=xlAscending, Header:=xlNo
For i = 100 To 2 Step -1
If Cells(i, 2).Value = Cells(i - 1, 2).Value And _
Cells(i, 3).Value = Cells(i - 1, 3).Value Then
Rows(i).Delete
End If
Next i
End Sub

Regards
Rowan

wrote:
Rowan,

Thanks for the code. I'll work on figuring out how it works (I don't
like to 'copy and paste'. I like to learn something along the way).

Here's an example:

datacol1 datacol2 datacol3 datacol4
row1 A B C C
row2 A C B D
row3 Z B C A
row4 Y C B H

In this example, I want to match on datacol2 and datacol3. Based on
that,
row1 = row3 and row2 = row4. I dissregard datacol1 and datacol4. I
want to keep row1 and row2 in this scenerio.

Thanks.
-Michael.

appending records
 

Rowan,

Thanks! I understand the appending code. I got something similiar to
it running and it works good.

Scary thing too, I actually understood the duplicates too. I'll be
writing that one soon too.

Thanks again for all your help!
-Michael

appending records
 

You're welcome.

wrote:
Rowan,

Thanks! I understand the appending code. I got something similiar to
it running and it works good.

Scary thing too, I actually understood the duplicates too. I'll be
writing that one soon too.

Thanks again for all your help!
-Michael

appending records
 

I'm trying to complete something similar and most of this code is working
very well for me. I would like to know how append records copied based on
how many rows are in the spreadsheet (as it can vary depending on the office
I am appending data from). Any insight would be appreciated.

Sheets("Sheet1").Select
Range("A2:D100").Select

the code above I need to have varied based on the number of rows as it can
change



Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste


DebP


"Rowan Drummond" wrote:

You're welcome.

wrote:
Rowan,

Thanks! I understand the appending code. I got something similiar to
it running and it works good.

Scary thing too, I actually understood the duplicates too. I'll be
writing that one soon too.

Thanks again for all your help!
-Michael