Copy range from sheet1 to sheet2 not deleting older dates and not doubling up.

[Zeak]

Hi All,

I've been searching though the web and though the different threads on here but I am unable to find anything that will work for me.
So if the magician's on here could help me with their wizardry, I would much appreciate it.
My problem is that I need to copy data in columns (Release Date, ID, Sequence, Parent Item, Qty Ordered) in sheet 1 to sheet 2 and as said in the title it needs to not overwrite the data in sheet 2 or duplicate what is already there.
Sheet 1 updates weekly and can have overlapping/same data from week to week.
Sheet 2 is basically a running history of orders, where sheet 1 is the current order we have from week to week and some of these order can push over to the following week.
Currently i'm selecting the rows that are new orders and pasting them into sheet 2, I also need to add comments to the rows pasted in sheet 2 that need to stay with that row of information (this can take a lot of time out of my week just keeping sheet 2 up to date when new orders come in), Please help.

Example.xlam
	A	B	C	D	E	F	G	H	I	J	K	L
1	Release Date	Day	Primary Line	ID	Work Order	Sequence	Due Date	Parent Item	Description	Work Order Status	Component Status	Qty Ordered
2	28/12/2019	Saturday	2112	3356625	4060094	1	28/12/2019	113	DRUM STD	F	Planned Receipts	8850
3	28/12/2019	Saturday	908	3773716	11130348	2	28/12/2019	132	DRUM T&C	F	Planned Receipts	17750
4	28/12/2019	Saturday	1018	3467174	6280011	3	28/12/2019	115	DRUM PF	F	Planned Receipts	13867
5	28/12/2019	Saturday	2126|920	3393888	5090043	4	28/12/2019	107	DRUM GFG	F	Planned Receipts	13750
6	29/12/2019	Sunday	1003	3113210	10220236	1	29/12/2019	102	DRUM B	F	Planned Receipts	4009
7	29/12/2019	Sunday		3527133	7301371	2	29/12/2019	103	DRUM GA	F	Planned Receipts	2112
8	29/12/2019	Sunday	1009	3351287	4030128	3	29/12/2019	105	DRUM DGB	F	Planned Receipts	9150
9	29/12/2019	Sunday	1005	3334835	3260144	4	29/12/2019	113	DRUM STD	F	Planned Receipts	13275
10	30/12/2019	Monday	1014	3584930	8270009	1	30/12/2019	110	DRUM PG	F	Planned Receipts	4250
11	1/01/2020	Wednesday		3343323	3290051		5/01/2020	106	BAR G	F	No Status	2380
12	1/01/2020	Wednesday	2106|2105	3290844	2250282	1	1/01/2020	112	DRUM L	F	No Status	14400
13	1/01/2020	Wednesday	1016|1000	3581190	8261357	2	1/01/2020	101	DRUM BO	F	No Status	11850
14	1/01/2020	Wednesday	1023	3436980	6112059	3	1/01/2020	117	DRUM PC	F	No Status	3810
15	2/01/2020	Thursday		3380810	4300174		8/01/2020	102	BAR GFG	F	No Status	6000
16	2/01/2020	Thursday		3581341	8261508		4/01/2020	101	BAR BO	F	No Status	1390
17	2/01/2020	Thursday		3320137	3160055	1	2/01/2020	205	DRUMED G	F	No Status	3
18	2/01/2020	Thursday	1019	3467173	6280010	1	2/01/2020	115	DRUM PF	F	No Status	15657
19	2/01/2020	Thursday		3014714	8050058	1	3/01/2020	205	DRUMED G	F	No Status	3050
20	2/01/2020	Thursday	1081|1080	3533243	8010091	2	2/01/2020	124	DRUM M	F	No Status	19555
21	2/01/2020	Thursday	1017	3515549	7240050	3	2/01/2020	102	DRUM B	F	No Status	4009
22	3/01/2020	Friday		3544466	8070139		9/01/2020	107	BAR L STD	F	No Status	3625
23	3/01/2020	Friday		3581355	8261522		4/01/2020	112	BAR P	F	No Status	1100
24	3/01/2020	Friday		3669716	10040134		5/01/2020	110	BAR P	F	No Status	1360
25	4/01/2020	Saturday	2108|2107	3281273	2150052	1	4/01/2020	100	DRUM AC	F	No Status	5426
Sheet1

 

[Zeak]

targetIDColumn
sourceIDColumn

I wasn't sure if it was necessary but I did a Dim for tIDColumn and sIDColumn.

  Dim Source, Target, Row As Range, _
      LastRow, InsertRowAddress As Long, _
      IDList, DateColumn, tIDColumn, sIDColumn, SeqColumn As String

 

[CSmith]

Well only when you use Option Explicit

The reason I left them out was copy paste error. Opps sorry.

 

[Zeak]

Hi CSmith,
Got a new question for you. Do I need to be on Sheet 2 when running the macro? is there a way I could be on lets say sheet 5.
Also I've tried modifying the code to not only do Sheet 1 to Sheet 2 but also Sheet 3 to Sheet 4 that has different columns to copy.
Sheet 5 being my summary/calculation sheet.

 

[Zeak]

Here is the code that I have so far.

Sub UpdateData()

Workbooks(ThisWorkbook.Name).RefreshAll

  Dim Source, Target, Row As Range, _
  LastRow, InsertRowAddress As Long, _
  btIDColumn, bsIDColumn, bIDList, bDateColumn, bSeqColumn As String, _
  ptIDColumn, psIDColumn, pIDList, pLineColumn, pDateColumn, pSeqColumn As String
  
'Plan
  Set Source = ActiveWorkbook.Worksheets("BCSV").UsedRange
  Set Target = ActiveWorkbook.Worksheets("SelectionData")
  LastRow = ActiveWorkbook.Worksheets("SelectionData").UsedRange.SpecialCells(xlCellTypeLastCell).Row
  bIDList = "|"
  btIDColumn = "A"
  bsIDColumn = "D"
  bDateColumn = "B"
  bSeqColumn = "C"
  For Each Row In Target.UsedRange.Rows 'Build Sheet2 list of items.
    bIDList = bIDList & Row.Cells(Row.Count, btIDColumn) & "|"
  Next Row
  InsertRowAddress = LastRow + 1
  For Each Row In Source.Rows 'Copy items not on sheet2 already from sheet1
    If 0 = InStr(1, bIDList, Row.Cells(Row.Count, bsIDColumn)) Then
      Target.Range("A" & InsertRowAddress).Offset(0, 0).Value = Row.Offset(0, 3).Value
      Target.Range("B" & InsertRowAddress).Offset(0, 0).Value = Row.Offset(0, 0).Value
      Target.Range("C" & InsertRowAddress).Offset(0, 0).Value = Row.Offset(0, 5).Value
      Target.Range("D" & InsertRowAddress).Offset(0, 0).Value = Row.Offset(0, 7).Value
      Target.Range("E" & InsertRowAddress).Offset(0, 0).Value = Row.Offset(0, 8).Value
      Target.Range("F" & InsertRowAddress).Offset(0, 0).Value = Row.Offset(0, 11).Value
      Target.Range("G" & InsertRowAddress).Offset(0, 0).Value = Row.Offset(0, 23).Value
      InsertRowAddress = InsertRowAddress + 1
    End If
  Next Row
  'Sort list
  Target.Activate
  Target.UsedRange.Sort _
    key1:=Range(bDateColumn & ":" & bDateColumn), order1:=xlAscending, _
    key2:=Range(bSeqColumn & ":" & bSeqColumn), order2:=xlAscending, Header:=xlYes
    
'Reset
    Source = ""
    Target = ""
    Row = "0"
    LastRow = ""
    InsertRowAddress = ""
        
'Production Schedule
  Set Source = ActiveWorkbook.Worksheets("PCSV").UsedRange
  Set Target = ActiveWorkbook.Worksheets("ProductionSchedule")
  LastRow = ActiveWorkbook.Worksheets("ProductionSchedule").UsedRange.SpecialCells(xlCellTypeLastCell).Row
  pIDList = "|"
  ptIDColumn = "A"
  psIDColumn = "X"
  pLineColumn = "B"
  pDateColumn = "C"
  pSeqColumn = "D"
  For Each Row In Target.UsedRange.Rows 'Build Sheet2 list of items.
    pIDList = pIDList & Row.Cells(Row.Count, ptIDColumn) & "|"
  Next Row
  InsertRowAddress = LastRow + 1
  For Each Row In Source.Rows 'Copy items not on sheet2 already from sheet1
    If 0 = InStr(1, pIDList, Row.Cells(Row.Count, psIDColumn)) Then
      Target.Range("A" & InsertRowAddress).Offset(0, 0).Value = Row.Offset(0, 0).Value
      Target.Range("B" & InsertRowAddress).Offset(0, 0).Value = Row.Offset(0, 3).Value
      Target.Range("C" & InsertRowAddress).Offset(0, 0).Value = Row.Offset(0, 5).Value
      Target.Range("D" & InsertRowAddress).Offset(0, 0).Value = Row.Offset(0, 7).Value
      Target.Range("E" & InsertRowAddress).Offset(0, 0).Value = Row.Offset(0, 8).Value
      Target.Range("F" & InsertRowAddress).Offset(0, 0).Value = Row.Offset(0, 11).Value
      Target.Range("G" & InsertRowAddress).Offset(0, 0).Value = Row.Offset(0, 23).Value
      InsertRowAddress = InsertRowAddress + 1
    End If
  Next Row
  'Sort list
  Target.Activate
  Target.UsedRange.Sort _
    key1:=Range(pLineColumn & ":" & pLineColumn), order1:=xlAscending, _
    key2:=Range(pDateColumn & ":" & pDateColumn), order2:=xlAscending, _
    key3:=Range(pSeqColumn & ":" & pSeqColumn), order3:=xlAscending, Header:=xlYes

End Sub

 

[CSmith]

So what you want to look at is the Source and Target that will determine what goes from Source to Target and columns are similar.
If you need help more information will be needed and if possible you can start a new Topic listing Sheet3 to Sheet4 and or to 5

Hi CSmith,
Got a new question for you. Do I need to be on Sheet 2 when running the macro? is there a way I could be on lets say sheet 5.
Also I've tried modifying the code to not only do Sheet 1 to Sheet 2 but also Sheet 3 to Sheet 4 that has different columns to copy.
Sheet 5 being my summary/calculation sheet.

 

[CSmith]

Hi CSmith,
Got a new question for you. Do I need to be on Sheet 2 when running the macro? is there a way I could be on lets say sheet 5.
Also I've tried modifying the code to not only do Sheet 1 to Sheet 2 but also Sheet 3 to Sheet 4 that has different columns to copy.
Sheet 5 being my summary/calculation sheet.

ActiveSheet should not matter you are setting all the items with based off of a TARGET and SOURCE which should interact with those objects/sheets as identified in the code above...

'Reset Source = "" Target = "" Row = "0" LastRow = "" InsertRowAddress = ""

Only ones I would worry about is the SET items

 

[Zeak]

Hi CSmith,

I ended up just running two modules to not cause conflicts.
I'm not sure why but for some reason running either one of these seems to take upwards of 20 minutes to stop "Calculating: (4 Processor(s)):" it just keeps flickering with different percentages.
almost like its getting stuck in a loop at some point or just that there is that much information to process.

 

[CSmith]

Would need to see the full code set there could be a circular loop/ redundancy / data change triggering loads of refreshes, but glad you got it working.

I try using this when changing a bunch of stuff you can also run a sheet refresh/recalc after...

Application.EnableEvents = False
'Change stuffs
Application.EnableEvents = True

 

[Zeak]

I ended up running all these to make it faster, it was because of how much data I am transferring and not because of a loop
Only takes about 2 to 3 minutes now.

With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .DisplayStatusBar = False
    .EnableEvents = False
End With
'Data transfer
With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .EnableEvents = True
End With

 

[CSmith]

Excellent!! The Events being turned off should have been all you needed. Glad it seems to be working out for you! Thanks for letting us know, and glad 2 help.