Hi all,

I'm new to the forum so hopefully this question isn't redundant. I tried searching for something similar, but didn't exactly know what to search. I'm also new to VBA and macros so please bear with me. (Please also note this is not my actual data and is simply a hypothetical situation that would represent the same circumstances, so please forgive me if the math for number of cells doesn't add up correctly in this example.)

I'm creating a database and would like to create charts from a summary sheet linked to the changing data. I have six different sheets that are all set up identically, however have data from different sources, lets call these sheets by years, so 2000, 2001, 2002, 2003, etc. For instance cells B2:G2 in sheet "2000" are the mean, median, mode, max, min, SD, and count for January 2000 sales. Cells B3:G3 in sheet "2000" are the mean, median, mode, max, min, SD, and count for February 2000 sales. Similarly, cells B2:G2 in sheet "2001" are the mean, median, mode, max, min, SD, and count for January 2001 sales.

Now I would like to create a summary sheet that takes the data from each individual sheet and lines it up side by side, so all of the statistics for January 2000-2005 are lined up side by side to create a chart. For example, I would like to take cells B2:G2 from sheet "2000" and link them to cells C3:C7 in sheet "summary." Then I would like to take cells B2:G2 from sheet "2001" and link them to cells D3:D7 in sheet "summary." Then I would like to take cells B2:G2 from sheet "2002" and link them to cells E3:E7 on sheet "summary." This would be repeated for six sheets. The data may also be updated in each individual sheet, so I would like to have the summary linked, rather than just cut and paste. After January 2000-2005 is summarized in cells C3:H7 in the summary sheet, I would like to put the February summary below it in cells C10:H16.

Is it possible to setup a macro to do this? Right now I am having to go into each individual cell on the summary sheet, press "=" then click on the corresponding cell in a different worksheet. I have six worksheets in total with around 20 variables in each, and each variable has a set of 7 statistics, so it really ends up eating up a lot of time the way I am doing it now. So what I am really trying to do is line up all 7 of the statistics, from each variable in each sheet, side by side, to create a chart.

I really appreciate any help! Thank you!