Need help simplifying a code Please

**Lostinexcel2002** · 12-02-2008, 02:51 AM

Hello,
I am using Excel 2002 and I have 7 workbooks with names of each month starting with April 2008 up to October 2008.

Each workbook consists of 52 spreadsheets with names of shops.

I have 4 staff members that are managing at least 13 of the shops listed in each of the workbooks.

What I am wanting to do is to take data from each workbook from a specific spreadsheet and add them to another spreadsheet that I have created which also provides a chart to measure certain areas.

I am wanting to do this for all 52 shops from each monthly workbook.

Below is an example of one shop data being populated to another worksheet for each month. The information is populating to columns E - K. The example is only showing rows E and F. I would like to have a simple formula to address all of the rows from this one shop which data will come from each month, i.e. April, may June....October.

Is there a way to simplyfy the code below to show months April - October, which will populate to another spreadsheet from rows E - K

Thanks

Please Login or Register  to view this content.

**royUK** · 12-02-2008, 04:45 AM

I think you need a better system, look at combining the data into one table containing a new column to identify the shop & another to identify the date. You could then probably use a PivotTable.

**MatrixMan** · 12-02-2008, 08:41 AM

I agree with roy that a better system is required here; although I think there may be a better way to do this within your current structure ... Can you post up:
- one of the monthly workbooks, and
- show the chart summary data you want in that month's workbook.

I think the summary data can be done via lookups within the book and the creation of that data can be automated ... If this is what you're looking for, then post up the sample & I'll take a look for you.

Hope that helps. MM.

**Lostinexcel2002** · 12-02-2008, 09:58 AM

Please Login or Register  to view this content.

**Lostinexcel2002** · 12-02-2008, 09:59 AM

I placed this code (Which is only part of it due to its length, but it goes to the month of October) in my chart collecting worksheet, which is pulling data from one of the worksheet from each month out of 7 different workbooks, and then it is inputing the data into the my separate worksheet.

Cell G9 of my worksheet is inputing the macro for the month of July of the refrenced shop in my code above but instead of the data, it is putting in

#REF

into cells G9:K13 & G16:K20 where a window that contains all my workbooks on my desktop is popping up to Update values.

How do I simplify this to be able to input data without the #REF symbol?

Thanks

**MatrixMan** · 12-02-2008, 10:28 AM

Honestly, without seeing the data (not the code) it's hard to help. I really don't think that you need VBA to do your summary sheet and you can probably greatly simplify the process ... if you post your workbook, I'll take a look. If you'd rather not, the #Ref usually means you're trying to reference something that's not there; possibly a typo in the file name or path, or worksheet name.

**Lostinexcel2002** · 12-06-2008, 10:32 AM

How do I post my workbook in here?

Thanks

**Lostinexcel2002** · 12-09-2008, 09:21 PM

I could have sworn that I have attached my workbooks earlier. Here is my workbook that I am needing help with. This attachment contains information that I am gathering for each shop individually from the monthly workbook and entering all the data in its own individual tab which charts each shops data individually. Each month's workbook is set up the same way.

Need help with formula to pull data to chart in order to identify trends.

Thanks

Charts from each monthly workbook.zip