MrExcel Publishing
Your One Stop for Excel Tips & Solutions

copying accross multiple worksheets

Posted by Colin on January 04, 2002 7:45 AM

I am trying to insert a new column into a number of worksheets.

They all have the exact same format and I need to insert a new column that deals with information regarding 2002. I would like to know how I could add this column to each worksheet at once without having to do it manually for each.

There is also some graphs in the various worksheets that deal with data in the workshet. The range of these graaphs only goes until 2001 and I need to extend the range to account for the new column for 2002.

I'm stumped and any help is appreciated. Thanks,

Posted by DonnaW on January 04, 2002 7:54 AM

Not sure about updating the graphs to include the new data (don't work with them much)but, to insert a new column or row in all of the sheets in a book, all you have to do is select all the sheets (i.e., have the first one displayed, hold shift and click on last one so all sheets are selected) then insert your row or column. Also works to change formulas in cell(s) on all sheets, etc. Perhaps would work for updating the charts too (I haven't tested).

Posted by Colin on January 04, 2002 8:05 AM

Thanks, That workes perfect for the columns. I can't figure out how to edit all the charts though because all the charts aren't on every sheet. If anyone can help me to change all the charts on all of my sheets at the same time PLEASE help.

Thanks, Colin

Posted by DonnaW on January 04, 2002 9:28 AM


I did a quick test and if you insert a row you add BETWEEN the beginning and end of the existing range used for the chart, the chart will be updated. However, the column references seem to be absolute so, you have to manually update them. I didn't do a lot of testing but, the same "select all" function doesn't work with selecting the charts ... i.e., to enable chart options such as source data. So, I don't know but you may end up having to do a right click, source
data and change the range for each chart.

Hope you find a better answer.