Sheet that slowly builds up information

Microsoft Office excel 2007 home & stude...
June 15, 2010 at 17:11:00
Specs: Windows XP
I have a form in Sheet 1 outlining daily production reports for bread products.

I want certain cells from this form to be copying to a new sheet that gathers throughtout the month.

How do I make a macro or function that once the form is completed it copies that information to the monthly sheet. NEW ROWS FOR EACH NEW FORM.

See I do not want to have thousands of these forms copied in there and have them each indivudally copied to a specific row in the monthly sheet.

I would like to find a way to keep that form in its same format then press a macro button that cuts it onto the monthly sheet.

See More: Sheet that slowly builds up information

Report •

June 15, 2010 at 18:30:27

It would be good thing to start with information on the worksheet name and the addresses of the cells you want to copy.

It would be possible to create a macro that copies the key or summary information from the form each day (or each time the form is completed), to a new row on a summary worksheet.

To do this we need a lot more detail. Then I am sure that someone will be able to help.


Report •

June 16, 2010 at 15:29:59
That sounds great! I dont know if there is way to link a worksheet but I can just say it.

Sheet 1 = Monthly Overview
Sheet 2 = Daily Control Report

Starting with Sheet 1 (Monthly Overview) Column A Row 6 all the way to Column Y

I want the following cells from Sheet 2 (Daily Control Report) to start copying on Sheet 1 (Monthly Overview):
On Sheet 1
A ------------------------------------------------------------------------------------Y
(from Sheet 2 \/\/\/)
C4 - $B$2 - G2 - J2 - F3 - B3 - E5 - A11 - D14 - B11 - D11 - C11 - E11 - K11 - K13 - B6 - D6 - G14 - I13 - E17 - F17 - C18 - C20 - E14 - F14

Report •

June 16, 2010 at 17:54:42

Here is a macro that will copy the following cells from the Daily Control Report worksheet:
C4,B2,G2,J2,F3,B3,E5,A11,D14,B11,D11,C11,E11,K11,K13,B6,D6,G14,I13, E17,F17,C18,C20,E14,F14
to the next available row on the Monthly Overview worksheet.

Assuming that there is header data in row 5 of the Monthly Overview worksheet, then new data will be pasted, starting on row 6.

The macro is attached to a button on the Monthly Overview worksheet.
When a new Daily Control Report has been pasted into the Daily Control Report worksheet, clicking the button will put the summary data onto the next line of the Monthly Overview worksheet.

To add the button do this:
From the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)

In Developer - Controls select Insert and choose the button icon.
Draw the button on the worksheet
In the 'Assign Macro' dialog box select 'New'

In the code window that opens enter this:

Option Explicit

Private Sub Button1_Click()
Dim rngMonthEnd As Range
Dim varSource As Variant
Dim n As Integer

'put source addresses into  a variant array
'this list of cells can be changed without
'changing the rest of the code
varSource = Array("C4", "B2", "G2", "J2", "F3", "B3", "E5", "A11", "D14", _
            "B11", "D11", "C11", "E11", "K11", "K13", "B6", "D6", "G14", _
            "I13", "E17", "F17", "C18", "C20", "E14", "F14")

'find row after end of monthly overview data
Set rngMonthEnd = Worksheets("Monthly Overview") _
        .Range("A" & CStr(Application.Rows.Count)).End(xlUp).Offset(1, 0)

'copy each cell in array to cells in columns starting at A on next row
For n = 0 To UBound(varSource)
    Worksheets("Monthly Overview").Range(rngMonthEnd.Address).Offset(0, n) _
        .Value = Worksheets("Daily Control Report").Range(varSource(n)).Value
Next n
End Sub

Note that Sub Button1_Click() and End sub will already be present, so don't duplicate them. Option explicit goes before Sub Button1_Click().

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.

Right click the button and Edit the name to something meaningful

As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

After selecting any cell, the new command button should now respond to a click and run the macro.

I hope that this is what you were looking for.
If not please provide additional details.

If the macro does not work 'as expected' please post what happens when you run it and what is not correct.


Report •

Related Solutions

June 22, 2010 at 16:17:50
Thank you very much it works for the most part. The thing is I need it to copy so the format keeps the equations from Daily Control Report or allows me to keep equations in Monthly Control Report.

Also is there a way to edit this macro so that it explicitly starts in Row 6 not just the next available row?

Report •

June 22, 2010 at 16:40:14

If there is data in cell A5 of the monthly overview, the first data should start on row 6
I thought that you had five rows with headers on the sheet.
This line in the macro finds the first empty row based on looking at the cells in column A

Set rngMonthEnd = Worksheets("Monthly Overview") _
        .Range("A" & CStr(Application.Rows.Count)).End(xlUp).Offset(1, 0)
so make sure you have something in cell A5.

When you say you want the macro to keep equations. What is it you want to keep. Is it formulas like =A10 or =A10+B11
or do you want to keep the formatting,
or is it something else.
An example of the data or a cell formula would help.


Report •

June 29, 2010 at 10:29:56
In Daily Report Worksheet:
For example B2 needs to keep the formatting of of a date.

C11 needs to keep the equation "=A11/B11".

D11 needs to keep a similar equation "=B11/(60)".

When they copy into Monthly Overview I know these cells need to be different for the equations.

I know this may be asking alot, but is there also a clause that could be added to the macro that would also copy the whole Daily Report worksheet into another excel file entirely and each time I press that same button (used for the other macro) it copies the Daily Report into worksheet 1, workseet 2, worksheet 3 and so on of this new file.

Thank you so much. This will greatly help my employees.

Report •

June 30, 2010 at 05:08:48

Copying formatting should not be a problem.

The issue of formulas such as C11 contains =A11/B11 requires further consideration.

This summary worksheet ("Monthly Overview") consists of rows of data taken each day from specific cells in a worksheet named: "Daily Control Report"

What is on the "Daily Control Report" worksheet changes every day, but what is retained on each row of "Monthly Overview" is an historical record of each day's control report.
As a result it should not change.

If you have cells A11, B11 and C11 copied from one day's control report and they have been copied to A2, B2 and C2 on the Monthly Overview worksheet, these cells will never change value.

If the original C11 on the daily control worksheet contained the formula =A11/B11, then the copied C11 (now in cell C2 on the Monthly Overview worksheet) would have to have the formula =A2/B2
The next day the same cells would be copied to A3, B3 and C3 and C3 would contain =A3/B3

In both cases, the results of the formula would never change, because the A2 and B2 or A3 and B3 are copies of what is now historical data.

Anyway, let me know what you think.

As to the copy of the Daily Control Report, yes it would be possible to save the worksheet each day.
You have a choice:
1. Save each daily report to a new worksheet in a single Workbook - perhaps named by Year, with each worksheet named by date (or just numbered 1 to 365/366)
Each year would have a workbook named "DCR2010.xls" and so on.
2. Save each daily report to a new Workbook, each containing just one worksheet. The workbook would be named according to the date, such as "DCR20June2010.xls"

Let me know which approach you would like to use.


Report •

Ask Question