Create History List

DWH01 · Nov 28, 2018

I've been looking for a set of macros I could use to put together to keep track of items I order.

I want to take the data from one worksheet and mesh it together into a history worksheet on another.

Basically to keep track of how many of an item I buy by date and add new part numbers as they are added.

My first worksheet is structured like this:
https://www.dropbox.com/s/o0b8a4chzu07t1a/WS1.jpg?dl=0

My second worksheet is structured like this:
https://www.dropbox.com/s/e5b0n83boe06ir2/WS2.jpg?dl=0

What is added I put in bold.
Obviously these sheets are just to play, hence why they are pretty basic.

Any help would be greatly appreciated.
Thanks

Fluff · Nov 28, 2018

Hi & welcome to MrExcel.
How about

Code:

Sub test1()
   Dim Hws As Worksheet, Ows As Worksheet
   Dim Cl As Range
   Dim Oset As Long
   
   Set Hws = Sheets("Archive")
   Set Ows = Sheets("Orders")
   Oset = Hws.Cells(2, Columns.Count).End(xlToLeft).column
   Hws.Range("A2").Offset(, Oset).Value = Ows.Range("B1").Value
   With CreateObject("scripting.dictionary")
      For Each Cl In Hws.Range("A3", Hws.Range("A" & Rows.Count).End(xlUp))
         Set .Item(Cl.Value) = Cl.Offset(, Oset)
      Next Cl
      For Each Cl In Ows.Range("B4", Ows.Range("B" & Rows.Count).End(xlUp))
         If .Exists(Cl.Value) Then
            .Item(Cl.Value).Value = Cl.Offset(, 2).Value
         Else
            With Hws.Range("A" & Rows.Count).End(xlUp)
               .Offset(1).Resize(, 2).Value = Cl.Resize(, 2).Value
               .Offset(1, Oset).Value = Cl.Offset(, 2).Value
            End With
         End If
      Next Cl
   End With
End Sub

DWH01 · Nov 28, 2018

That worked like a charm.
Thanks!!!

Fluff · Nov 28, 2018

You're welcome & thanks for the feedback

Create History List

DWH01

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

DWH01

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Create History List

DWH01

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

DWH01

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock