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

Jase Alexander

ASKER

Sorry

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 = False

For Each ws In Sheets(Array("1", "2", "3", "4", "5"))
With ws
.Range("A14:A192").Copy
Worksheets("ducr-pl").Range("A" & Cells(Rows.Count, 1).End(xlUp).Row + 1).PasteSpecial (xlPasteValues)
End With
Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

.. did not seem to work

byundt

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

Open in new window

-Packing-ListQ28983869.xlsm

Roy Cox

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.

Jase Alexander

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

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jase Alexander

ASKER

HI Brad

Its perfect !!

Thank you so much for your help - its always appreciated

J

Roy Cox

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.