This is a discussion on Macro to find new rows and insert them into another sheet within the Excel Questions forums, part of the Question Forums category; Hi guys, I need a macro which will work with 2 Tables (Excel 2010). The first table contains different columns ...
I need a macro which will work with 2 Tables (Excel 2010).
The first table contains different columns and 2 ID columns which I need to insert into the second table if these values dont exist in the second one.
I want to create a mapping for these distinct values so I thought having a second table would be the best idea, especially considering that the first table will be changed every few days and me not wanting that to affect my mapping table.
I have come up with this (my vba knowledge is limited):
atm it does copy all values of my first table into the second one and deletes all duplicates.Code:Sub addNew() Dim i As Long Dim k As Long Dim l As Long Dim boo As Boolean Sheets("Increments_Status").Select 'k has to be a vriable starting position 'k = Range("Increment ID").Rows.Count k = 0 l = 0 Sheets("Increments").Select ' variable doesnt work atm For i = 1 To 1000 'Cells(Rows.Count, 1).End(xlUp).Row 'If Worksheets("Increments").Cells(j + 1, 2).Value = Worksheets("Increments_Status").Cells(l + 1, 2).Value Then If boo = True Then k = k + 1 Worksheets("Increments_Status").Cells(k + 1, 1).Value = Worksheets("Increments").Cells(i + 1, 1).Value Worksheets("Increments_Status").Cells(k + 1, 2).Value = Worksheets("Increments").Cells(i + 1, 24).Value End If Next i Sheets("Increments_Status").Select ActiveSheet.Range("$A$1:$B$1000").RemoveDuplicates Columns:=Array(1, 2), _ Header:=xlYes End Sub
2 things I did not manage to solve until now:
- I could not set the starting positions to be a variable accoriding to the first empty row in my mapping table and the last filled row in my first table.
- more importantly: atm it copies all distinctive values, but i want to check if the value exists in my mapping table and if thats the case to do nothing. / i only want new combinations of IDs in my mapping.
Help will be appreciated.
so maybe I am dumb, but I wasnt able to make it run.
I know that I need two loops, one to loop through the first table and another one to loop through the mapping checking for existence.
you also need to know, that I am working with 2 sheets.