Excel Copy Cells from One Worksheet to Many Other Worksheets
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Copy Cells from One Worksheet to Many Other Worksheets
Problem: I have 12 monthly worksheets in a workbook. I’ve made changes to January and now need to copy those changes to the other 11 worksheets. Is there an easy way?
Strategy: After you’ve successfully made changes to January, you can follow these steps:
1. Select the January worksheet. Hold down the Shift key and select the December worksheet. Alternatively, right-click the January sheet tab and choose Select All Sheets. Excel will select all 12 worksheets and make the January worksheet the active sheet.
Note: If your changes are in a middle sheet, such as April, then the process is different. First, click the April worksheet. Next, Shift+click the December sheet, and then Ctrl+click the January, February, and March sheets.
2. Select the cells you want to copy. If the cells are not adjacent to one another, select the first range and then hold down Ctrl while selecting the remaining ranges.
3. Select Home, Fill, Across Worksheets.
Figure 90 The Fill icon is in the Editing group.
Figure 91 You can also use this to copy formatting to other sheets.
4. From the Fill Across Worksheets dialog select whether to copy values, formats, or both.
5. Right-click any sheet tab and select Ungroup.
Gotcha: If you fail to ungroup, any changes you make to the active worksheet will be made to all worksheets.
Additional Details: Home, Fill, Across Worksheets is fairly difficult to use. You have to be able to group sheets and then make the sheet with the changes to copy the active (top) sheet. The steps listed here are designed to help select all sheets. If you need to copy from March to only June, September, and December, however, you might do this:
1. Select March to make it the active sheet.
2. Hold down Ctrl and select June to add it to the group.
3. Hold down Ctrl and select September to add it to the group.
4. Hold down Ctrl and select December to add it to the group.