Summary Sheet macro needed please

February 10, 2011 at 02:58:35
Specs: Windows XP
Hi all,
I'm hoping you can help, I've been tasked with solving the following problem. We have a spreadsheet which surveyors take out on site, this is filled in manually on site, then typed into excel when they return, each different address creates 1 new worksheet in a workbook with the address as the worksheet name. They may do 2 or 10 surveys per day. Every sheet is the same.
I need a way of transfering all of this inputted information to a summary sheet and of updating this everyday but just with the updated information, one address per summary sheet line.
I can manage the formula's to bring the information across but how do I get excel to look just for the new sheets created and bring this information only across to the summary sheet?

Any help would be appreciated to cut the hours I am spending on updating formula's and manually updating a sheet.
Thanks


See More: Summary Sheet macro needed please

Report •


#1
February 10, 2011 at 12:32:27
If all you need is to add the data from the new sheets to the summary sheet, and not worry about updating existing data from sheets that already existed, then some fairly simple VBA would probably handle it.

Assuming the sheet names match (exactly) some data in the summary sheets, such as addresses in Column A, then the code could loop through the sheets and compare the sheet names to the list of addresses. If it can't find a sheet name in the list, then it would copy that sheet's data to the summary sheet.

Even easier would be if there were 4 addresses in the summary sheet and 4 sheets on Monday, but 4 addresses in the summary sheet and 7 sheets on Tuesday, then the code could just copy over the data from the last 3 (new) sheets.

This all depends on the layout of your sheets, the workbook, etc.

If you can see where I'm headed with my suggestions, then perhaps you can tell us how the sheets are laid out that might make something like one of those (or something else) possible.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 10, 2011 at 15:15:44
Hi Derbydad03,
thanks for the reply, the address in column 1 of the summary is unique and relates exactly to the worksheet names, there are something like 76 pieces of data per sheet to bring through onto the summary sheet, and anything between 70 - 170 sheets to summarise, thankfully all the sheets are identical.
Is it possible to list all of the forthcoming addresses on the summary sheet and to run a macro through once per day to pick up the new worksheets created but ignore those it can't find yet?
Thanks

Report •

#3
February 10, 2011 at 18:34:17
re: "there are something like 76 pieces of data per sheet to bring through onto the summary sheet"

Is all this data in a single row? If not, how is it laid out?

VBA code has to be very specific about the ranges it is working on and since we can't see your spreadsheet from where we're sitting, you have to be very specific and detailed in describing your sheet lay out.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 11, 2011 at 00:34:22
Sorry I should have been more specific.
The data sheet that the surveyors take on site is laid out like a questionaire to make it easier for them to work with on site. I need to take specific cells from the spreadsheet, not entire rows or columns of data, so I need something that will look for a new sheet and then look specifically in certain cells to bring the data required through to the summary. Could I upload a blank copy of the form here to illustrate?

Report •

Ask Question