CODEWITHSUNDEEP
excelvba
PeanutsMonkey
PeanutsMonkey

Reputation: 7105

Append new record in Excel using VBA

I have 2 worksheets in a workbook,namely "MasterSheet" and "Operations". The data in the mastersheet is constantly changing whereas the data in operations is largely static.

I would like to be able to copy all records in column A in the master sheet if it does not exist in column A of the "Operations" worksheet. If it exists, it is simply ignored. I also need it to appear in the same order as it appears in the master sheet as well as copy columns B,C and D from the master sheet to the "Operations" worksheet.

If a record is deleted from the mastersheet, no data changes in the "Operations" worksheet.

How do i achieve this using VBA?

The version of Excel installed is 2010 and 2013.

** EDIT **

I tried with the following code and it doesn't work i.e. no records are copied. I also changed the worksheet names to 1 and 2.

Sub x()

    Dim r1 As Excel.Range
    Dim r2 As Excel.Range
    Dim r3 As Excel.Range
    Dim c As Excel.Range

    Set r1 = Sheets(1).Range("A1").Resize(Range("A" & Rows.Count).End(xlUp).Row)
    Set r2 = Sheets(2).Range("A1").Resize(Range("A" & Rows.Count).End(xlUp).Row)

    For Each c In r2

        Set r3 = r1.Find(What:=c.Value, MatchCase:=False, Lookat:=xlWhole)

        If r3 Is Nothing Then

            Sheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1).Value = c.Value

        End If
    Next

End Sub

Upvotes: 0

Views: 2448

Answers (1)

jcl
jcl

Reputation: 11

Have you looked into the Range.AdvancedFilter Method? It may work for you.

into http://msdn.microsoft.com/en-us/library/office/ff841242(v=office.15).aspx

In your requirement you did not specify the order sequence of the merged document just that the source (master data) values need to be in source order. This will affect how you handle the combined sheet. Adding a column to represent sort order with unique id's (ordered sequence of numbers) to both worksheets may be helpful.

Assumption/Clarification

  • Column A represents a unique key for the data across master and operations data
  • If there is a duplicate row between master and operations data there is no preference to which one you keep. (e.g. other data in the sheet is always the same or irrelevant if different).
  • both master and operations worksheets have identical column headings and ordering for the critera range selected
  • no data below table of master tab, operations tab or destination tab

Pseudo code

  1. name range for master database

    you can do this manually or automate it

  2. count number of rows in master

    Dim masterSheet As Worksheet
Dim masterRows As Long As Long
Set masterSheet = ThisWorkbook.Sheets("master")
masterRows = master.UsedRange.Rows.Count

  3. name range for operations database

  4. copy range for master to new tab

    Worksheets("master").Range("MasterDataRange").Copy _ 
destination:=Worksheets("newMasterTabName").Range("A1")

  5. copy range for operations to new tab underneath the master data (masterRows + 1)

    Worksheets("operations").Range("OperationsDataRange").Copy _
destination:=Worksheets("newMasterTabName").Range("master.UsedRange.Rows.Count")

  6. name new consolidated range (from the destination tab - s/b eqal to number rows master + number rows operations)

  7. invoke AdvancedFilter on consolidated table

    Range("consolidatedNewMasterRangeName").AdvancedFilter _ 
Action:=xlFilterInPlace, _ 
CriteriaRange:=Range("Criteria") _
Unique:=True

Once again, if you add a sort order to your Criteria so you have A,B,C,D,sortId you can use sortId after merge to reorder the list at intended.

My advise is to get it working using the gui first. Then replicate in VBA.

Upvotes: 1

Related Questions