Help with macros|VBA & Macros|Excel Forum|My Online Training Hub

Although you specifically asked for a macro/VBA solution, I would like you to consider PowerQuery for a task like this. It's relatively easy to extract data from the shipping certificate and then append the current information to the previously extracted information. That way you can build a history table of all shipping certificates. 

The attached file contains such a PQ solution. Change certificate numbers and shipping details in Sheet1, press Refresh All and check out the DATA tab. 

Note that I defined two named ranges to which PQ connects. One for the certificate header and one for the certificate body. 

It depends a little bit on how accustomed you are with PQ, but when you look at the queries in the file, you should be able to follow the applied steps. The process works as follows.

First create two named ranges. One for the information on row 9 in Sheet1 and one for the block of shipping details in rows 14 to 38. I just called them "ship_hdr" and "ship_body".

Load these ranges into PQ. From the ship_hdr query I drilled down to the value in Column2 to create a variable for the ship_date (right-click and Drill Down). A similar procedure for the certificate number (Column8) creating a variable ship_nbr.

Now, load the ship_body query and extract all rows with data. Use Add Column, Custom to add columns for the date and certificate numbers. For the date it looks like this and it's similar for the certificate number:

Reorder and remove some columns and set the correct data types. Loading it back to Excel creates a table called "ship_body_2". That's the green table in the DATA sheet. Being the very first extracted data set from Sheet1.

Now, it becomes a bit tricky as you have to edit the M-code to arrive at the script that you find in the file. The bold/red code is what needs to be added/changed.

let
   Source = Excel.CurrentWorkbook(){[Name="ship_body"]}[Content],
   Source2 = Excel.CurrentWorkbook(){[Name="ship_body_2"]}[Content],
   PromoteHdr = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
   Filter = Table.SelectRows(PromoteHdr, each ([#"No."] <> null and [#"No."] <> "")),
   AddDate = Table.AddColumn(Filter, "Date", each ship_date),
   AddCertNbr = Table.AddColumn(AddDate, "Cert", each ship_nbr),
   Reorder = Table.ReorderColumns(AddCertNbr,{"Date", "Cert", "No.", "1", "Column3", "2", "Column5", "3", "Column7", "4", "5", "6", "7"}),
   RemoveCols = Table.SelectColumns(Reorder,{"Date", "Cert", "No.", "1", "2", "3", "4", "5", "6", "7"}),
   Append = Table.Combine({Source2, RemoveCols}),
   RemoveDupl = Table.Distinct(Append, {"Cert", "No."}),
   Type = Table.TransformColumnTypes(RemoveDupl,{{"Date", type date}})
in
   Type

What this does is add the initial table "ship_body_2" table as a second source. Later on, you append the ship_body table (= current query at the RemoveCols step) to the previous version of this table which is in Source2. The remove duplicates step is needed to avoid duplication of rows when you press Refresh All without having changed the data in Sheet1.

All of this can be invisible for the users. Once set-up correctly, all they need to know is to press Refresh All on the Data ribbon. And what this does is take the newly entered data from Sheet1 and appends it to the existing ship_body_2 table, thus building a history table off all shipping certificates created.

Glad you like the proposed solution. Perhaps, I'm not the best to judge what macros can do. Personally, I'm not very good with them and have learned I don't need them in my line of business.  PQ has given me much more joy and proved to be much easier to learn. One can do magic with PQ through just pushing the correct buttons 80% and if you dive into M for the other 20%, you can do real magic. With macro's it's the other way around I believe. But that's just my personal opinion.