Excel Copy To All Sheets? 1

[Mictain]

Hi All,

Currently fiddling with several 52 sheet workbooks, and some text needs to be added to all sheets. Instead of doing this in a copy & paste scenario for each individual sheet, is there a way to copy said text into a particular cell on all sheets in one go? One workbook at a time, obviously. I hope so, otherwise it's going to be a loooooong day!

All help appreciated. Thanks.

 

[dawnd3]

Click on the first sheet and hold down the shift key and then click on the last sheet. You will have all sheets selected. Now what you type in one sheet will show up in all.

Dawn

http://www.calliopeconsulting.net

 

[xlbo]

Hi
Try summat like

For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Name <> &quot;Sheet1&quot; --sheet to copy from-- Then
Sheet.Range(&quot;A1&quot.Value = Sheets(&quot;Sheet1&quot.Range(&quot;A1&quot.Value
Else
End If
Next Sheet

Put it in a standard module and press play

HTH
~Geoff~

 

[Mictain]

Fantastic. Thanks!

Dawn's theory worked so I haven't got round to trying yours yet, Geoff, but I will!

Thanks again.

Neil.

 

[dawnd3]

Wow, I guess others are up late too.... (do I get a star for answering a question at 1AM?)

http://www.calliopeconsulting.net

 

[Mictain]

Go on then... even though it's approaching 10am here... :-D

Now I have to sort out doing the same sort of thing but pulling formulae across - same basic blurb but sheet 2 pulls info from sheet 1 cell plus sheet 2 cell; sheet 3 pulls info from sheet 2 cell & sheet 3 cell, etc ad nauseam.

Then I'll be even happier...

Neil

 

[iGrant]

As a slight variation to Dawn's suggestion, you can right click the sheet tabs & choose select all. And if you already have the text you want in the first sheet, you can choose Edit - Fill - Across Worksheets to copy contents, formats or both across all sheets.

Also, and I don't know if this is going to help, but did you know you can put sheet ranges in your formula? For example,

=SUM(Sheet1:Sheet2!A1:A3)

will sum A1:A3 on all sheets between Sheet1 & Sheet 2 inclusive.