Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COPY / APPEND` | Excel Worksheet Functions | |||
How do you append one row to another? | Excel Worksheet Functions | |||
Find and append | Excel Discussion (Misc queries) | |||
Append to open CSV | Excel Programming | |||
open for append | Excel Programming |