Compare and Append
 

Hi all,

I am hoping someone can help me. I have a workbook with an import
sheet that is populated from an external data source. I have another
sheet (working copy) that contains columns B and C from the import
sheet. I need a way to update the working copy without changing the
order that it was already in or overwriting the existing info it in.

Is there a way to write a macro that will compare the import sheet to
the working copy and then append the newly imported rows or columns B
and C from the new imported rows to the bottom of the working copy?

Thanks in advance!

Bryon

Compare and Append
 

Bryon: Try something like this where book1 is the import sheet and book2 is
where to append. Assume that book1 is completely overwritten at each
import -

Dim rowcount As Integer
Dim rowcount2 As Integer

Sub Copy1to2()
Workbooks("book1").Sheets("Sheet1").Activate
Range("B1").Select
Cells.CurrentRegion.Select
rowcount = Selection.Rows.Count

Workbooks("book2").Sheets("Sheet1").Activate
Range("B1").Select
Cells.CurrentRegion.Select
rowcount2 = Selection.Rows.Count
Range("B" & rowcount2 + 1 & ":C" & rowcount + rowcount2).Value =
Workbooks("book1").Sheets("Sheet1").Range("B1:C" & rowcount).Value
End Sub

MIke

"BFord" wrote in message
om...
Hi all,

I am hoping someone can help me. I have a workbook with an import
sheet that is populated from an external data source. I have another
sheet (working copy) that contains columns B and C from the import
sheet. I need a way to update the working copy without changing the
order that it was already in or overwriting the existing info it in.

Is there a way to write a macro that will compare the import sheet to
the working copy and then append the newly imported rows or columns B
and C from the new imported rows to the bottom of the working copy?

Thanks in advance!

Bryon

Compare and Append
 

Bryon,

Hope this does what you're after:

Sub testit()
Dim rngExternal As Range, blnFound As Boolean
Dim rng As Range, rngSource As Range, rngDest As Range

For Each rngExternal In Sheet1.Cells(1, 1).CurrentRegion.Rows
blnFound = False
For Each rng In Sheet2.Cells(1, 1).CurrentRegion.Columns(1).Cells
If rng.Value = rngExternal.Cells(1, 2).Value And _
rng.Offset(0, 1).Value = rngExternal.Cells(1, 3).Value Then
blnFound = True
Exit For
End If
Next
If Not blnFound Then
If rngSource Is Nothing Then
Set rngSource = Range(rngExternal.Cells(1, 2),
rngExternal.Cells(1, 3))
Else
Set rngSource = Union(rngSource, Range(rngExternal.Cells(1,
2), rngExternal.Cells(1, 3)))
End If
End If
Next

If Not rngSource Is Nothing Then
Set rngDest = Sheet2.Cells(Sheet2.Cells(1,
1).CurrentRegion.Rows.Count + 1, 1)
rngSource.Copy rngDest
End If
End Sub


Rob


"BFord" wrote in message
om...
Hi all,

I am hoping someone can help me. I have a workbook with an import
sheet that is populated from an external data source. I have another
sheet (working copy) that contains columns B and C from the import
sheet. I need a way to update the working copy without changing the
order that it was already in or overwriting the existing info it in.

Is there a way to write a macro that will compare the import sheet to
the working copy and then append the newly imported rows or columns B
and C from the new imported rows to the bottom of the working copy?

Thanks in advance!

Bryon