Copy/paste VBA with issues

[pete4monc]

I have a simple vba code that works absolutely fine, it copies data from one Workbook 1 column A and pastes it into Workbook 2 column A.

Problem is workbook 1 is updated weekly and the data in some cells in column A change and I do not want the original data copied over to Workbook 2 to be over written every time the vba is run.
I need some help writing some vba that will copy over only new data from workbook1 and not delete data already previously copied over into Workbook2 ( ie: Workbook 2 Column A will just get longer and longer)

Hopefully I have explained it well enough so you can understand.

Sub CopyColumnToWorkbook()
Dim sourceColumn As Range, targetColumn As Range

Set sourceColumn = Workbooks("Bought-Out-Part Orders.xlsx").Worksheets("Live").Columns("A")
Set targetColumn = Workbooks("BOP Orders.xlsm").Worksheets("Sheet3").Columns("A")

sourceColumn.Copy Destination:=targetColumn
End Sub

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not a pictures) of your two sheets.
Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Joe4]

Maybe try something like this:

Sub CopyColumnToWorkbook()
Dim sourceColumn As Range, targetColumn As Range
Dim lrs As Long
Dim lrt As Long

'Find last rows on each sheet
lrs = Workbooks("Bought-Out-Part Orders.xlsx").Worksheets("Live").Cells(Rows.Count, "A").End(xlUp).Row
lrt = Workbooks("BOP Orders.xlsm").Worksheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row

'See if new data was added, and if so, copy
If lrs > lrt Then
    Workbooks("Bought-Out-Part Orders.xlsx").Worksheets("Live").Range(Cells(lrt + 1, "A"), Cells(lrs, "A")).Copy _
        Destination:=Workbooks("BOP Orders.xlsm").Worksheets("Sheet3").Cells(lrt + 1, "A")
End If

End Sub

Note: I am not sure if it will let you find the last row without being in that workbook, so you may need to activate it first if the code does not work "as-is".

 

[pete4monc]

Maybe try something like this:

Sub CopyColumnToWorkbook()
Dim sourceColumn As Range, targetColumn As Range
Dim lrs As Long
Dim lrt As Long

'Find last rows on each sheet
lrs = Workbooks("Bought-Out-Part Orders.xlsx").Worksheets("Live").Cells(Rows.Count, "A").End(xlUp).Row
lrt = Workbooks("BOP Orders.xlsm").Worksheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row

'See if new data was added, and if so, copy
If lrs > lrt Then
    Workbooks("Bought-Out-Part Orders.xlsx").Worksheets("Live").Range(Cells(lrt + 1, "A"), Cells(lrs, "A")).Copy _
        Destination:=Workbooks("BOP Orders.xlsm").Worksheets("Sheet3").Cells(lrt + 1, "A")
End If

End Sub

Note: I am not sure if it will let you find the last row without being in that workbook, so you may need to activate it first if the code does not work "as-is".

Hi Joe
Unfortunately nothing seems to happen when I try your code. I have both workbooks open, maybe it is something i am missing?

 

[pete4monc]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not a pictures) of your two sheets.
Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Hi Mumps

First time using XL2BB so I hope it comes out right.
Basically the 1st workbook needs to look into the 2nd workbook column A, find any new entries not already in workbook1.
Enter the MSO number into the 1st workbook without over writing the original data in column A.
New entries could be inserted (not always the last row entry) or deleted in workbook 2 in any order.
Once the orders are completed they will be removed from workbook1 but still need to remain in workbook2.
There will be an issue with the lookup ranges but I shall look at a vba to copy cells based on the MSO number and certain cells later.

Many thanks for your help and please let me know if the XL2BB thing has worked as it should?

BOP Orders.xlsm
	A	B	C	D	E
1	Order No	PO number	Item	Alloy	CK no
2	MSO24260	0	W018094	HR33PR	P21/126
3	MSO24390	MP43363	M045966MC	HR36A	CKYN87
4	MSO24390	MP43363	M045969MC	HR36A	CKYN88
5	MSO24390	MP43363	M045970MC	HR36A	CKYN89
6	MSO24390	MP44481	M045966HT	HR36A	CKYN87
7	MSO24390	MP44481	M045969HT	HR36A	CKYN88
8	MSO24390	MP44481	M045970HT	HR36A	CKYN89
9	MSO24412	MP42834	A027535	HR13CO	CKYN90
10	MSO24414	MP44120	N025702S	310	CKE218
11	MSO24414	MP44120	N025703S	310	CKE219
12	MSO24551	MP42352	N019118	330	CKX314
Sheet3

Cell Formulas
Range	Formula
B2:B12	B2	=INDEX('V:\Purchasing\BOP\[Bought-Out-Part Orders.xlsx]Live'!$L$2:$L$500,AGGREGATE(15,6,(ROW('V:\Purchasing\BOP\[Bought-Out-Part Orders.xlsx]Live'!$L$2:$L$500)-ROW('V:\Purchasing\BOP\[Bought-Out-Part Orders.xlsx]Live'!$L$2)+1)/('V:\Purchasing\BOP\[Bought-Out-Part Orders.xlsx]Live'!$A$2:$A$500=$A2),COUNTIFS($A$2:$A2,$A2)))
C2:C12	C2	=INDEX('V:\Purchasing\BOP\[Bought-Out-Part Orders.xlsx]Live'!$D$2:$D$500,AGGREGATE(15,6,(ROW('V:\Purchasing\BOP\[Bought-Out-Part Orders.xlsx]Live'!$D$2:$D$500)-ROW('V:\Purchasing\BOP\[Bought-Out-Part Orders.xlsx]Live'!$D$2)+1)/('V:\Purchasing\BOP\[Bought-Out-Part Orders.xlsx]Live'!$A$2:$A$500=$A2),COUNTIFS($A$2:$A2,$A2)))
D2:D12	D2	=INDEX('V:\Purchasing\BOP\[Bought-Out-Part Orders.xlsx]Live'!$E$2:$E$500,AGGREGATE(15,6,(ROW('V:\Purchasing\BOP\[Bought-Out-Part Orders.xlsx]Live'!$E$2:$E$500)-ROW('V:\Purchasing\BOP\[Bought-Out-Part Orders.xlsx]Live'!$E$2)+1)/('V:\Purchasing\BOP\[Bought-Out-Part Orders.xlsx]Live'!$A$2:$A$500=$A2),COUNTIFS($A$2:$A2,$A2)))
E2:E12	E2	=INDEX('V:\Purchasing\BOP\[Bought-Out-Part Orders.xlsx]Live'!$F$2:$F$500,AGGREGATE(15,6,(ROW('V:\Purchasing\BOP\[Bought-Out-Part Orders.xlsx]Live'!$F$2:$F$500)-ROW('V:\Purchasing\BOP\[Bought-Out-Part Orders.xlsx]Live'!$F$2)+1)/('V:\Purchasing\BOP\[Bought-Out-Part Orders.xlsx]Live'!$A$2:$A$500=$A2),COUNTIFS($A$2:$A2,$A2)))

Bought-Out-Part Orders.xlsx
	A	B	C	D	E	F	G	H	I
1	Order No	Item	Alloy	CK no	Description	Sales qty	Stock	Supplier	PO number
2	MSO24260	W018094	HR33PR	P21/126	Support Grid	160	0
3	MSO24390	M045966MC	HR36A	CKYN87	Subcon Machining - Upper Centre Platen	1	0	Mollart Engineering	MP43363
4	MSO24390	M045969MC	HR36A	CKYN88	Subcon Machining - Upper Outer Platen	1	0	Mollart Engineering	MP43363
5	MSO24390	M045970MC	HR36A	CKYN89	Subcon Machining - Upper Outer Platen	1	0	Mollart Engineering	MP43363
6	MSO24390	M045966HT	HR36A	CKYN87	Subcon Heat Treat - Upper Centre Platen	1	0	Thermofax
7	MSO24390	M045969HT	HR36A	CKYN88	Subcon Heat Treat - Upper Outer Platen	1	0	Thermofax
8	MSO24390	M045970HT	HR36A	CKYN89	Subcon Heat Treat - Upper Outer Platen	1	0	Thermofax
9	MSO24412	A027535	HR13CO	CKYN90	Subcon Machine, Heat Treat & Assemble - SPF Tool	1	0	Cube Precison Engineering	MP42834
10	MSO24414	N025702S	310	CKE218	Bar ø1 1/2" x 14" Long	2	0	Applied Alloys	MP44120
11	MSO24414	N025703S	310	CKE219	Bar ø1 1/2" x 17" Long	2	0	Applied Alloys	MP44120
12	MSO24745	N049507	304	CKT129	HANDLE C/W LUG YK79100153	80	0	Almor	MP43589
Live

Cell Formulas
Range	Formula
D2:D12	D2	=VLOOKUP(B2,Items!$1:$65004,3,FALSE)
E2:E12	E2	=VLOOKUP(B2,Items!$1:$65004,2,FALSE)

 

[Joe4]

Yeah, just as I thought, you may need to activate the one sheet.
This seems to work, regardless of which file you are in to start:

Sub CopyColumnToWorkbook()
Dim sourceColumn As Range, targetColumn As Range
Dim lrs As Long
Dim lrt As Long

'Find last rows on each sheet
lrs = Workbooks("Bought-Out-Part Orders.xlsx").Worksheets("Live").Cells(Rows.Count, "A").End(xlUp).Row
lrt = Workbooks("BOP Orders.xlsm").Worksheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row

'See if new data was added, and if so, copy
If lrs > lrt Then
    Workbooks("Bought-Out-Part Orders.xlsx").Activate
    Set sourceColumn = Workbooks("Bought-Out-Part Orders.xlsx").Worksheets("Live").Range(Cells(lrt + 1, "A"), Cells(lrs, "A"))
    Set targetColumn = Workbooks("BOP Orders.xlsm").Worksheets("Sheet3").Cells(lrt + 1, "A")
    sourceColumn.Copy Destination:=targetColumn
End If

End Sub