Copy data from one workbook and either replace existing data insert on last row OR in new workbook with VBA

[Benjamin Verdelin]

Hi all

I am completely new to this community and somewhat new to VBA.
It was not possible for me to find a solution to this so I now reach out to you.

As the title says I am looking for a way to:
1. Copy data from Ark1 A3:DD3 in workbook "Lotseddel"
2. Open workbook "Masterfile" (where all data from previous lotseddel are stored)
3. Look at column A and see if the input in cell B3 from woorkbook Lotseddel is present (8 digit lot code)
4. Select the row for which the input in B3 is found
5. Replace data in that row with what was copied (it might be that data was previously copied for this 8 digit lot code so I would like to replace it, but NOT delete data for all the other lot codes)
6. If not found I want to select last row in the workbook Masterfile and simply insert the copied data
7. Close workbook Masterfile and leave Lotseddel open

I currently have a macro (button to click) for copying data from Lotseddel and inserting the data to the last row in workbook Masterfile.
However this means that I will get multiple entries of the same lot code as the macro is sometimes run 3-4 times.

Attached are two pictures:
1; List of lot codes where some appear two times (data was exported two times maybe by different people working on the same Lotseddel)
2; Current macro to copy data and insert to last row in Masterfile

Hope you will take the time to help.
Thx

 

[Kaper]

It's a bit crazy to paste code as picture, but if that's the way you like it ... here is my proposition of ammendment which checks if the code from A3 (if it shall be B3 - ammend the code) exists and if it does - uses the row where it was found, otherwise uses first empty row

 

[Benjamin Verdelin]

It's a bit crazy to paste code as picture, but if that's the way you like it ... here is my proposition of ammendment which checks if the code from A3 (if it shall be B3 - ammend the code) exists and if it does - uses the row where it was found, otherwise uses first empty row

Thanks for taking your time Kaper.
Strict company rules makes it impossible to get the add in.
I will try to get hold of personal laptop and post code as minisheet later.

I tried to run the code but get the following error:
"Run-time error '438':
Object doesn't support this property or method"

The error appears when a get to LR = rng.Now

 

[Kaper]

it's

rng.Row

not

rng.Now

and the same way as you published above just one line of code you coulkd publish all the text of the VBA code (as text inside the post, not the screenshot)

BTW. please note CODE tags in my answer, and how they improve readability

 

[Benjamin Verdelin]

it's

rng.Row

not

rng.Now

I just noticed..
Suppose that what happends when using pictures. My bad.

The code runs and works when the lot code is unique.
However, if it already exist it chooses the row below and not the actual row

 

[Kaper]

Sorry for that, I was writing on a picture, so obviously missed that this row number is used below as LR+1

So in refering to found code (rng is not nothing):

LR = rng.Row-1

 

[Benjamin Verdelin]

Sorry for that, I was writing on a picture, so obviously missed that this row number is used below as LR+1

So in refering to found code (rng is not nothing):

LR = rng.Row-1

Thank your very much for your help!
I struggled a bit to make it work but now it does.

It's not very nice yet but here is the final code:

Sub export_master()

Dim LR As Long, yourID As Variant, rng As Range

'copy data from this workbook

ActiveWorkbook.Worksheets("Ark1").Range("A3:DD3").Copy
yourID = Sheets("Sheet1").Range("B4").Value
'open the Master file

Workbooks.Open Filename:="P:\YE production\Lot Files GE\Master\Lot_Seddel_MASTER.xlsx"

'copy the information into the next free row in the table

Workbooks("Lot_Seddel_MASTER.xlsx").Sheets("Library").Activate
Set rng = ActiveSheet.Cells.Find(What:=yourID, lookat:=xlWhole)
If rng Is Nothing Then
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.Range("A" & LR + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues
Else
LR = rng.Row
ActiveSheet.Range("A" & LR).Select
Selection.PasteSpecial Paste:=xlPasteValues
  End If
  
'Save and Close workbook
    ActiveWorkbook.Close SaveChanges:=True
    Application.CutCopyMode = False
End Sub