Hope you can help or give me some advice on a project I have been asked to work on
Our warehouse team have created a workbook with a number of worksheets (numbered 1 to 5 in the example) each containing a template in order to complete details of goods received. Each sheet is required as a separate sheet needs to be completed per purchase order (each purchase order has a unique reference). These sheets are necessary and stored for compliance purposes, and in an agreed format, so the summary sheet cannot just be used to input the data.
However, for the purpose of receipting procedures,they have asked for elements of data contained in these sheets to be assembled into a separate summary sheet (ducr-pl in the example) in order for this data to be used to upload to a scanning / booking system.
On this summary sheet (ducr-pl in the example) there are five headings. The first column just contains a formula to pull the internal reference. The other four headings are labelled as required and the subsequent data needs to be pulled from the numbered sheets.
Each workbook is saved as an individual entry, per delivery.
At present, and in the example, I have used the primitive method of mapping the summary sheet to each numbered sheet, after which i was contemplating inserting a basic 'remove empty rows' macro (i understand you can filter but these users are not seasoned IT personnel), so when run, the summary sheet (ducr-pl) would contain the condensed data and in a format that can be used for the upload.
My question is, is there some VBA that would scan each numbered sheet in the workbook and copy the data into the summary sheet under their respective headings? For this, it would have to look at the filled rows in each numbered sheet and when the empty rows are reached in each numbered sheet, it would go on to the next numbered sheet and so on until all available data in the workbook has been copied into the summary sheet.
I have attached the work in progress version (its quite large and takes a while to open) that I have reduced to just 5 numbered sheets for the purpose of trying to find a realistic way of accomplishing this feat and have removed the code I originally had in. There is just dummy data in the numbered sheets its all the same as I was just using it for mapping purposes. In the summary sheet (ducr-pl) in row 2 you will be able to see in B2 to B6 where the data is pulling from in the numbered sheets. In B5 of the summary sheet, there is a part number that has been concatenated from the numbered sheets comprising of the Master Code, Colour and Size columns.
Any help or advice would be much appreciated as always.