Question about a project

[gocolts12]

I work at a lab that takes lab data from hospitals, and places the info into a spreadsheet. Every month has its own set of daily sheets for the whole month with all the necessary data listed.

The way the system works now is that someone goes in at the end of every month and has to manually copy-paste the data from each individual day onto one sheet that covers the whole month.

My task is to come up with a way to automate the "end of the month aggregation" so that all the data that is input on a daily basis is being written into the end of the month sheet at the same time.

The solution I came up with is to create the entire month (each day and the one sheet for the total) at the start of each month, and link the last page to each daily page. Creating everything by hand on a monthly basis doesn't seem very elegant, but I don't know how it could be further automated.

Does anyone have any other ideas? I'm kinda new to Excel, so I don't know everything its capable of.

Thanks

 

[dchaney]

Excel is a VERY flexible program when it comes to ways to accomplish tasks. You could actually create a macro that would copy all of the data at the end of the month with a push of a button, you can have a macro that runs as soon as you close the spreadsheet that will copy all of your data then close. The options are numerous when it comes to Excel.

Personally I would create a macro that copied the data to the "Monthly" sheet as soon as the person closed the sheet. So they fill in the day tab, close the workbook it runs the code to copy all the data, automatically saves and closes.

 

[gocolts12]

Interesting. Could you walk me through how to do that?

 

[dchaney]

If you know very little about VBA coding then "walking you through it" would be very difficult. Maybe if you can provide an example of your workbook, or at least how many columns are in use, the naming convention you use for the tabs/sheets I may be able to provide a code.

 

[gocolts12]

Yea, I'm not very familiar with VBA. I cannot share my workbook because of HIPAA, but i can tell you there are columns from A through P, and each column can have either numbers, names, or plain words. I tried using consolidate, but it didn't work because it could only use number functions.

If you could just provide code for taking every row of data from each day, and putting them into one page, that'd be awesome. You don't have to implement the closing the workbook part.

 

[dchaney]

First, if you do not have the Developer ribbon on your excel program then go here for instructions on installing it.

Once that is active on your ribbon bar at the top you need to add a Command Button (ActiveX control) to the Monthly Tab/Sheet, go here for instructions on that.

After that is completed, on step 4 of the command button instructions it shows how to open the VBA Editor, add the following code AFTER "Private Sub CommandButton1_Click()

Dim ws As Worksheet, lastRow As Long, finalRow As Long

lastRow = Sheets("MONTHLY").Range("A" & Rows.Count).End(xlUp).Row


    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "MONTHLY" Then
            finalRow = ws.Range("A" & Rows.Count).End(xlUp).Row
            ws.Range("A2:P" & finalRow).Copy
            Sheets("MONTHLY").Range("A" & lastRow + 1).PasteSpecial
            lastRow = Sheets("MONTHLY").Range("A" & Rows.Count).End(xlUp).Row
        End If
    Next ws

This code will take all data from each tab and add to the Monthly tab (If your Monthly tab is call anything but monthly then change that in the code. If you push the button more then once it will add the data again, so only hit it once.

 

[gocolts12]

Man, you are a life saver! Greatly appreciated!!!

I learned some visual basic a few years back, but I never learned it in the context of Excel, so I'm going to look through your code to see how it works!

 

[gocolts12]

However, I do have another question. Would it be a lot of trouble if instead of a straight copy, it would only copy the cells themselves so that they could fit the format? The trouble is that many of the daily sheets have a lot of unused lines, or lines that have one piece of data in the first column, and none in the rest. How could I alter the code to stop copying once you hit a row with no specifically data in any of the cells (some cells are colored, but have no data in them)?

 

[dchaney]

What columns are mandatory?

 

[gocolts12]

I actually solved that issue, so its not a big deal.

My new issue is that I cannot enable macros on these workbooks (I'm trying to pre-make workbooks for an entire year). Every time I make a direct copy of my original book, the new copies do not have the code for the button, and when I add it and try to save, it says macros aren't enabled, even though I went into the trust center and enabled all macros and VB projects. Any ideas? I'm on 2007