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

Zeak

New Member
Joined
Jan 21, 2020
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKL
1Release DateDayPrimary LineIDWork OrderSequenceDue DateParent ItemDescriptionWork Order StatusComponent StatusQty Ordered
228/12/2019Saturday211233566254060094128/12/2019113DRUM STDFPlanned Receipts8850
328/12/2019Saturday908377371611130348228/12/2019132DRUM T&CFPlanned Receipts17750
428/12/2019Saturday101834671746280011328/12/2019115DRUM PFFPlanned Receipts13867
528/12/2019Saturday2126|92033938885090043428/12/2019107DRUM GFGFPlanned Receipts13750
629/12/2019Sunday1003311321010220236129/12/2019102DRUM BFPlanned Receipts4009
729/12/2019Sunday35271337301371229/12/2019103DRUM GAFPlanned Receipts2112
829/12/2019Sunday100933512874030128329/12/2019105DRUM DGBFPlanned Receipts9150
929/12/2019Sunday100533348353260144429/12/2019113DRUM STDFPlanned Receipts13275
1030/12/2019Monday101435849308270009130/12/2019110DRUM PGFPlanned Receipts4250
111/01/2020Wednesday334332332900515/01/2020106BAR GFNo Status2380
121/01/2020Wednesday2106|21053290844225028211/01/2020112DRUM LFNo Status14400
131/01/2020Wednesday1016|10003581190826135721/01/2020101DRUM BOFNo Status11850
141/01/2020Wednesday10233436980611205931/01/2020117DRUM PCFNo Status3810
152/01/2020Thursday338081043001748/01/2020102BAR GFGFNo Status6000
162/01/2020Thursday358134182615084/01/2020101BAR BOFNo Status1390
172/01/2020Thursday3320137316005512/01/2020205DRUMED GFNo Status3
182/01/2020Thursday10193467173628001012/01/2020115DRUM PFFNo Status15657
192/01/2020Thursday3014714805005813/01/2020205DRUMED GFNo Status3050
202/01/2020Thursday1081|10803533243801009122/01/2020124DRUM MFNo Status19555
212/01/2020Thursday10173515549724005032/01/2020102DRUM BFNo Status4009
223/01/2020Friday354446680701399/01/2020107BAR L STDFNo Status3625
233/01/2020Friday358135582615224/01/2020112BAR PFNo Status1100
243/01/2020Friday3669716100401345/01/2020110BAR PFNo Status1360
254/01/2020Saturday2108|21073281273215005214/01/2020100DRUM ACFNo Status5426
Sheet1
 
targetIDColumn
sourceIDColumn

I wasn't sure if it was necessary but I did a Dim for tIDColumn and sIDColumn.
VBA Code:
  Dim Source, Target, Row As Range, _
      LastRow, InsertRowAddress As Long, _
      IDList, DateColumn, tIDColumn, sIDColumn, SeqColumn As String
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Well only when you use Option Explicit

The reason I left them out was copy paste error. Opps sorry.
 
Upvote 0
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.
 
Upvote 0
Here is the code that I have so far.
VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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...
VBA Code:
Application.EnableEvents = False
'Change stuffs
Application.EnableEvents = True
 
Upvote 0
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.
VBA Code:
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
 
Last edited:
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top