Jase Alexander
asked on
Excel VBA - Gather data into a single summary sheet from multiple worksheets
Hey Experts
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.
J
Copy-of-Packing-List.xlsm
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.
J
Copy-of-Packing-List.xlsm
I modified your code to copy over just the data that exists in worksheets 1 through 5. I also made explicit the references to worksheet ducr-pl when determining the next blank row.
Sub Copy_Sheets()
Dim ws As Worksheet
Dim rg As Range
Application.ScreenUpdating = False
With Worksheets("ducr-pl")
.Rows(1).Value = Worksheets("1").Rows(13).Value 'Copy header labels
For Each ws In Sheets(Array("1", "2", "3", "4", "5"))
Set rg = ws.Range("A14:I192")
Set rg = Intersect(rg, ws.UsedRange)
rg.Copy
.Range("A" & .Cells(.Rows.Count, 1).End(xlUp).Row + 1).PasteSpecial (xlPasteValues)
Next
End With
Application.CutCopyMode = False
End Sub
-Packing-ListQ28983869.xlsm
I would say that you should consider a different approach.
When I create something like this I create data sheets for the the actual line data, Suppliers etc. I then use one template sheet that is populated from these data sheets to create copies as required. I would have an input sheet to enter the necessary data which could then be saved to the database. This will be a much more efficient way of doing this.
I'll post an example after work.
When I create something like this I create data sheets for the the actual line data, Suppliers etc. I then use one template sheet that is populated from these data sheets to create copies as required. I would have an input sheet to enter the necessary data which could then be saved to the database. This will be a much more efficient way of doing this.
I'll post an example after work.
ASKER
Hi Brad
Thank you so much for the code
It works perfect
Could you take a look at the attached as Ive modified the sheets slightly as it seems the external uploader wishes to have the summary sheet in a certain format with bespoke titles
In the attached file I have hidden the columns on sheet 1 but have left them open on sheets 2 to 5 so you can see what I have inserted
With these changes I modified the code to just look at the range A15 to E193 to capture the data however, when I run it with these changes, it only displays two lines instead of ten, as in your original code.
Am I doing something wrong as it is now not pulling from all sheets?
Any help would be much appreciated
J
Copy-of--Packing-ListQ28983869.xlsm
Thank you so much for the code
It works perfect
Could you take a look at the attached as Ive modified the sheets slightly as it seems the external uploader wishes to have the summary sheet in a certain format with bespoke titles
In the attached file I have hidden the columns on sheet 1 but have left them open on sheets 2 to 5 so you can see what I have inserted
With these changes I modified the code to just look at the range A15 to E193 to capture the data however, when I run it with these changes, it only displays two lines instead of ten, as in your original code.
Am I doing something wrong as it is now not pulling from all sheets?
Any help would be much appreciated
J
Copy-of--Packing-ListQ28983869.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
HI Brad
Its perfect !!
Thank you so much for your help - its always appreciated
J
Its perfect !!
Thank you so much for your help - its always appreciated
J
That code is limited, what happens when you get past 5 sheets? If you insist on using code for this then the code needs to be dynamic.
I have never seen a successful system like the one that you are using, but good luck with it.
I have never seen a successful system like the one that you are using, but good luck with it.
ASKER
I did use the following code in a bid to have something working to modify if necessary:-
Sub Copy_Sheets()
Dim ws As Worksheet
Application.ScreenUpdating
For Each ws In Sheets(Array("1", "2", "3", "4", "5"))
With ws
.Range("A14:A192").Copy
Worksheets("ducr-pl").Rang
End With
Next ws
Application.CutCopyMode = False
Application.ScreenUpdating
End Sub
.. did not seem to work