1

This might be easy for many of you, but i am new on VBA; Following is my code to add multiple Excel file values in to one mastersheet. While i do this , i would like to update the values regarding their key value.

Sub tekSheetMerging()
Dim masterSheet As Worksheet

Set masterSheet = sheets("KoMKo")

'Variable to save the used Range of the master sheet
Dim usedRangeMaster As Integer

     Dim ws As Worksheet

     'loop through each worksheet in current workbook
    For Each ws In Worksheets

    'If sheetname contains "data" (UCase casts the Name to upper case letters)
    If InStr(1, UCase(ws.Name), "DATA", vbTextCompare) > 0 Then

        'calculate the used range of the master sheet
        usedRangeMaster = masterSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1

        'Variable to save the used Range of the sub sheet
        Dim usedRangeSub As Integer

        'calculate the used range of the sub sheet
        usedRangeSub = ws.UsedRange.SpecialCells(xlCellTypeLastCell).Row

        'copy relevant range from the subsheet
        ws.Range("C1:C" & usedRangeSub).Copy

        'paste the copied range after the used range in column a
        masterSheet.Range("A" & usedRangeMaster).PasteSpecial
    End If
     Next ws

    End Sub

So, now I have an Excel table which includes a key value in Column A and other values in other Columns in that row. When i find a key which is duplicate of the key value which is added before, I would like to delete that cell and its complete row. By doing so i would like to update the values, regarding their key value. If i would add another key, it should stay in the list with no Problem though.

How can i do this?

Karthick Gunasekaran
  • 2,697
  • 1
  • 15
  • 25
Yigit Tanverdi
  • 161
  • 6
  • 18
  • 1
    Is the above code working or are you getting an error? If so, please give us the error code / message and at which line the error occurs. If the code is running without errors then please elaborate on (1) what the current outcome of the code is and (2) what the expected outcome of the above code is (and how it differs). At present I see only `.Copy` and `.PasteSpecial` in your code and I wonder how you want to achieve the `.Delete` you are talking about in your post. – Ralph May 03 '16 at 09:02

1 Answers1

0

It would be better to first go through the keys in the sub sheets (sources) and compare them one by one to the keys in the master. Any matches should be deleted from the master. Then you copy the whole data from the sub sheet into the master.

My code therefore goes before this line:

'copy relevant range from the subsheet
ws.Range("C1:C" & usedRangeSub).Copy

Replace my variables with yours:

With mastersheet
    for i = firstrow to lastrow   'first and lastrow of data in subsheet
        set f = .Range("A:A").Find(ws.Range("A" & i).Value, _
                LookIn:=xlValues, LookAt:=xlWhole)
        If Not f is Nothing then
            .Range("A" & f.Row).EntireRow.Delete
        End If
    next i
end with

Don't forget to update usedRangeMaster after this, as it will be shorter due to the deletions. Then copy paste your data.

Note: this code assumes that your key is unique, and you will never have duplicates in your master (which will be the case if the master is only written through this code). That is why it only searches the master once.

Edit: I see that freakfeuer already had provided you with some links? Anyways, let me know if it works.

Sun
  • 762
  • 4
  • 10