Move data from daily to monthly sheet

Let me ask a couple of questions:

1. I notice that the monthly values are linked to the daily values. Do you need to retain these links or can the monthly sheet just have the values? Having a workbook link to another one or more is possible and handy at times, but can cause some delays.
2. Do you need to transfer the data to the monthly sheet on a daily basis, or do it in batches, like at the end of the month or after you made some change to a daily sheet?
3. Program control of the daily sheet names may be a better way for a macro to locate the daily sheets for the month? Is there some reason why the user needs control of the sheet names? How about a "New Daily Sheet" button that asks for the day and the macro builds the sheet with a name that it creates? That way the macros that transfers the data can find the sheets that it wants.

1 The links are there to show how (what) I want to happen. Not necessary when a macro starts placing the data

2 I think ideally that the macro I use to create a new daily sheet should include code to place the data in the monthly sheet(s) as there is an existing line for the data to be placed in and the row is identified by date.

3 I'm not sure what you're asking here. The daily sheets are named by their date as that is the sheet to record the store sales for that day. The monthly sheets are named by month for the same reason. The names will need to be modified a bit as I intend to do a couple of the monthly sheets off the daily one. The user box to allow naming the new sheets exists because the default new sheet name is yesterday's date and the renaming option is necessary if data wasn't input the day after for some reason. This simplifys getting the dates right.

I probably have few issues with a monthly sheet that pulls data from the daily sheets for a given month all at once since the sheets are used for tracking sales trends it would be nice to be able to review a month to date anytime. After some thought I am going to have the first daily sheet for a new month generate all the monthly sheets I use so they are there to "catch" the data

I can build code to transfer the daily data to a monthly sheet a day at a time but I don't understand the date processes or VBA well enough to have the process happen in a new month without rewriting all the code for a new month manually.

Great. Here are some suggestion on how to tackle this problem. You may have other suggestions, but here's what I think.
1. Create new daily sheet. This should ask you for the date that the sheet should be created for. This could use a template sheet to copy from.
2. Update one or more daily sheets. This I assume is done manually, so that the gross sales is entered by someone on a daily basis.
3. Export the daily info to monthly summary. Given a desired month, each time this is executed, all of the daily info would be complied into a monthly sheet. This allows for corrections to any daily sheet for a month and the monthly totals updated with the changes.

I would suggest that you keep all of the data for one month in a single workbook. You could have different workbooks for each month. A single "master" workbook could be used to contain the user interface and macros for creating new daily sheets and monthly summaries. It would also contain the daily template.

I may be going off in the wrong direction, but if this stuff is cleared up, the structure of the macros will follow.

The sheets are fine as is. What I'm after is a macro to:

look in the folder "store" that the "daily cash reconcile" workbook with sheet "Fri-Feb-27-2009" sheet is in. Copy cells A3, A5, A7, B6 to folder "monthly reconcile", workbook "variation", sheet "Feb2009", row Feb 27, Columns B, D, F, H

I simply want to update the data in the monthly sheet when I make the new daily sheet.

Sorry, you lost me on this. I am not sure what help you need. Do you need help on transferring data from one workbook to another?

Yes, That's what I'm after. Each of the worksheets I want to copy data to need to have various cells copied into them from my daily sheet. The only open workbook has the daily sheets in it.

OK. there are methods in the Workbooks collection that allow you to open and close workbooks. Say, for example, you want to open a file that contains a workbook.

Please Login or Register  to view this content.

assuming all is well opening this file, theMonthBook can now be manipulated.

Say you want to transfer data from one of the daily sheets to this monthly workbook, sheet 1. Let "theDailySheet" be the daily sheet you want to transfer the data from:

Please Login or Register  to view this content.

r1, r2, c1, and c2 are the rows and columns of the cells that you want to move the data between. In your problem, you know the daily sheet location. You have to figure out which row of the monthly sheet to use. That would depend of the day of the month.

Then you close the monthly book by

Please Login or Register  to view this content.

Does this help? Let me know f you need more.