Solved

Excel VBA - Gather data into a single summary sheet from multiple worksheets

Posted on 2016-11-17
7
23 Views
Last Modified: 2016-11-18
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
0
Comment
Question by:spicecave
  • 3
  • 2
  • 2
7 Comments
 

Author Comment

by:spicecave
Comment Utility
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
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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
0
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:spicecave
Comment Utility
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
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
Look at the scrollbar on the right for worksheet ducr-pl original. You will see the slider is fairly small, indicating that you must go down quite a few rows before hitting the end of your data. This was my clue that something was amiss.

For some reason, the UsedRange property of worksheets 1 through 5 extends down to AC301. I can't see anything there, but there might be an errant space character--or sometimes the UsedRange property doesn't get updated properly.

The consequence of this issue is that there were 177 blank rows between each pair of rows with data on worksheet ducr-pl original after running the macro. I believe that all 10 rows with data were returned--but you may not have thought so because of the blank rows.

So I changed tactics, and looked up column B starting with cell B194 for the last row of data. I then used that to define which rows to copy. And because I wasn't sure that column A would always be populated if column B had a value, I also changed the statement getting the next blank row on worksheet ducr-pl to use column B.
Sub Copy_Sheets()
     
    Dim ws As Worksheet
    Dim rg As Range
    Dim LastRow As Long
     
    Application.ScreenUpdating = False
     
    With Worksheets("ducr-pl")
        .Range("A1:E1").Value = Array("ducr", "carton", "ref_no", "part", "qty")
        For Each ws In Sheets(Array("1", "2", "3", "4", "5"))
            LastRow = ws.Range("B194").End(xlUp).Row
            Set rg = ws.Range("A15:E" & LastRow)
            rg.Copy
            .Range("A" & .Cells(.Rows.Count, "B").End(xlUp).Row + 1).PasteSpecial (xlPasteValues)
        Next
    End With
     
    Application.CutCopyMode = False
     
End Sub

Open in new window

The attached workbook includes your original worksheet ducr-pl (renamed as ducr-pl original) and a newly inserted blank worksheet named ducr-pl.
-Packing-ListQ28983869R1.xlsm
0
 

Author Closing Comment

by:spicecave
Comment Utility
HI Brad

Its perfect !!

Thank you so much for your help - its always appreciated

J
0
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
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.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now