Updating target Worksheet from changing source Worksheet

[adambc]

I get extracts from a source system daily (source WS) …

The source WS has 8 columns including a Record ID in column A …

I have a target WS with the same 8 columns, with 3 additional columns that are used to manually annotate comments to records …

When I get a new source WS, I want to check (using the Record ID) whether the record exists in the target WS …

- if it does, I want to update the first 8 columns in the target WS with the data from the same 8 columns in the source WS …
- if it doesn’t exist, I want to add a new record to the target WS, populating the first 8 columns with the data from the same columns in the source WS (leaving the additional columns in the target WS blank)

I then want to check whether all records in the target WS exist in the new source WS and if they don’t, delete the record in the target WS …

But I’m going round in circles! - can anyone give me a helping hand please?

Many thanks …

 

[igold]

To double check- the code is now working properly for you.

How many total records on average are you working with. If the record count is not high, perhaps a copy and paste routine where you can copy formats and values would work better for you than doing the work with Arrays (the logic is already thought through). I naturally go to Arrays because it is usually the quickest, easiest route. Dictionaries can be quicker but you still don't get your formatting.

 

[adambc]

Other than adding the writ = false, I stupidly had some "1"s in the arrays where I meant for there to be an "i", that too was changed. Let me look again and add more than one or two lines of my own fictitious data.

A sampling of your data would go a long way!

@igold

Strange one!!!

Everything working exactly as I need - thanks to your help …

But when I converted my target to a table, I noticed a large number of blank rows at the end - I deleted them and re-ran the macro and it seems that every other run blank rows are added at the end of the table (FYI … I converted it back to a range and found the last row which also included blank rows) …

56 data rows in target, 61 in sources - 61 in target after running macro, plus 60 blank rows every other run!!!

Any ideas?

 

[adambc]

@igold

Strange one!!!

Everything working exactly as I need - thanks to your help …

But when I converted my target to a table, I noticed a large number of blank rows at the end - I deleted them and re-ran the macro and it seems that every other run blank rows are added at the end of the table (FYI … I converted it back to a range and found the last row which also included blank rows) …

56 data rows in target, 61 in sources - 61 in target after running macro, plus 60 blank rows every other run!!!

Any ideas?

Have done some more testing/thinking …

After deleting the blank rows and re-running the macro (still looks like it’s every other run), I end up with 117 data rows (increments in subsequent runnings) …

Is this because in the following …

lRow = SlRow + TlRow
sArr = wsS.Range("A2:H" & SlRow)
tArr = wsT.Range("A2:L" & lRow)

Slrow = 56
Tlrow = 61

… so lrow = 117 - and tArr = wsT.Range(“A2:L” & lrow) ie tArr = wsT.Range(”A2:L117”)?

Why isn’t tArr = tArr = wsT.Range(“A2:L” & Tlrow)?

 

[adambc]

To double check- the code is now working properly for you.

How many total records on average are you working with. If the record count is not high, perhaps a copy and paste routine where you can copy formats and values would work better for you than doing the work with Arrays (the logic is already thought through). I naturally go to Arrays because it is usually the quickest, easiest route. Dictionaries can be quicker but you still don't get your formatting.

My adapted code has been working just fine until a particularly large source update caused an out of range error!

After a bit of thinking I realised that …

lRow = SlRow + TlRow

Needs to be …

If SlRow > TlRow Then
lRow = SlRow + SlRow
Else
lRow = SlRow + TlRow
End If

I’m sure you’ll see the reason for this but thought I’d update the post in case anyone else picks it up at some time