Copy range from one worksheet and paste to multiple worksheets

[mooring]

I am using EXCEL 2007.

In previous verions, I can remember selecting a range on a worksheet, then
selecting multiple worksheets, the pasting--this would result in the past to
all the selected or grouped worksheets.

This does not work in EXCEL 2007?

How can I do a past, data and/or formulats, a cell and/or a range, to
multiplpe workseets in the same workbook?

Thank you.

 

[Rick Rothstein \(MVP - VB\)]

Did you remember to copy the cell contents first? I just tried it on my XL2007 and it worked fine. I selected the cells, hit Ctrl+C to copy it into the Clipboard, selected multiple sheets, hit Ctrl+V to paste the Clipboard contents and every selected sheet got a copy of the originally selected cells.

Rick

 

[mooring]

Yes I did.

I have tried this with both formulas and data (integers).

If I use data (numbers) and open the clipboard on the left and select past
from the clipboard, it does seem to work as expected. However, if I copy
formulas and past from the clipboard on the left, it pastes numbers in all
the sheets.

When I simply copy a cell or range of cells, then group the sheets, then
past normally, the past only goes to the worksheet that is active.

This is Excel 2007

thanks for your help.

 

[Roger Govier]

Hi
As Rick says, this works the same in XL2007 as earlier versions.
I get no problem selecting a mixture of Text, Numbers and formulae, then
grouping a range of sheets and pasting.
It makes no difference if I use the icons for Copy and Paste, or using
Control+C to copy and Control+V to Paste.

 

[mooring]

Thanks for your reply.

Mine definately is not performing as you both suggest and observe. Is there
an Excel setting I may have corrupted?

I will try this again on a fresh reboot. (This is running under Vista).

 

[mooring]

Thanks again for the replies.

Could I get both of you to try the following simple test and report back
your results.

Open a blank workbook (should have three worksheets)
Enter data in 5 cells in column A
Enter data in 5 cells in column B
Enter a formula in 5 cells in column C based on the values in Colum A and B
(=A1*B2)
Copy all the data (should be 5 rows and 3 columns) with Ctrl C
Group Sheet 1 to Sheet three with click shift click
Paste with Cntrl V on Sheet 1

In my test, there is nothing pasted to Sheet 2 or Sheet 3.

If I select any group of worksheets, EXCLUDING the copy from worksheet, it
will paste across multiple worksheets.

Why would you want to past on top of what you just copied from. I have many
models that I create the first page and have multiple parallel worksheets.
When I cot to edit this model, I work on one worksheet. Then it seems simple
to me to just copy from that worksheet, and past to all parallel worksheets,
including the copy from worksheet.

Do I have a warped sense of reality here?

Thanks again.

 

[JR Hester]

I am using Excel XP and get exactly the same results you describe in 2007.
When you go to copy just group the worksheets you wish to copy to.
Why would you try to overwrite the contents you just copied?

 

[Rick Rothstein \(MVP - VB\)]

When I perform the test you indicated, I get the same results you posted. As
for including the worksheet in the group to copy to... it would never have
occurred to me to even try that. I'm not sure why doing so stops the rest of
the copies from happening; but I did note this interesting observation...
put the same data on Sheet3 instead of Sheet1, copy it, select all the
sheets and then paste it down... Sheet1 will receive a copy of the data but
not Sheet2.

Rick

 

[mooring]

Yes I noted that also. What is happening is you only get the paste into the
active sheet, i.e., Sheet 1. if you have more sheets inbetween 1 and 3,
those also do not get the data fro paste.

As to why would you copy to the same sheet you are copying from, consider
this.
You have 10 parellel worksheets. You are working in Sheet 7, making edits,
changing formulas, and formating. As you are working you say to yourself, "I
need to make these changes to all the other sheets." So you copy the block
in Sheet 7, then you Group Sheet 1 through Sheet 10, which includes Sheet 7
the copy from sheet, and then paste. THAT'S how I discovered the problem and
hence my frustration. If it pasted to the "from sheet" this could be done in
one step instead of two. It would also be more consistent with the copy
function--copying a cell or range in a slngle sheet can be copied onto
itself--not someting you do, but something I often try to do 3D or across
multiple sheets where I'm copying from the middle of the group of sheets.

As I mentioned before, I think this worked (copy to the copy from sheet) in
previous versions of Excel. Something changed, and I wish it had not.

Thanks for all the help.

 

[mooring]

May also want to Copy Special over the copy from cell/range.

 

[Roger Govier]

You are absolutely correct.
The behaviour has altered between XL2003 and XL2007.

As has been noted by others, in a simple scenario, copying from sheet1, I
would naturally group from sheet 2 onward.
I can see your requirement, where you have made the amendments on Sheet7,
but wish this to apply to all.
The only solution to this would be to hold down Shift and select Sheet1 and
Sheet6, then Control and select remaining (leaving out Sheet7), then do your
Paste.


Regards
Roger Govier