copy value to all pages

[Jean-Paul De Winter]

Hi,
I would like to have a puchbutton on the first page of my worksheet that,
when puched, copies cells A1,A2 and A3 to evey page of the worksheet...
Can somebody explain how?
Thanks

 

[Ron de Bruin]

You can use this macro

Sub Test()
Dim I As Long
For I = 2 To ThisWorkbook.Worksheets.Count
Worksheets(1).Range("A1:A3").Copy Worksheets(I).Range("A1")
Next
End Sub

 

[Gord Dibben]

Jean

Assuming "page" means worksheet and "worksheet" means workbook.

Sub copytoall()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Range("A1:A3").Value = Worksheets("Sheet1").Range("A1:A3").Value
Next ws
End Sub

Gord Dibben Excel MVP

 

[Jean-Paul De Winter]

Thanks for your reply...
Had to modify a bit because the workbook is password protected so I wrote:

Sub Test()
Dim I As Long
ActiveSheet.Unprotect Password="DBI"
For I = 2 To ThisWorkbook.Worksheets.Count
Worksheets(1).Range("A1:A3").Copy Worksheets(I).Range("A1:A3")
Next
ActiveSheet.Protect Password="DBI"
End Sub

This seems to work, but cells A1 to A3 are blocked too.
There must be a way to unblock them, perform the copying and block them again.
Alas, I don't know how.
Maybe you do.
Thanks in advance
JP, Belgium

 

[Ron de Bruin]

Use this

protect and unprotect in the loop

Sub Test()
Dim I As Long
For I = 2 To ThisWorkbook.Worksheets.Count
Worksheets(I).Unprotect Password = "DBI"
Worksheets(1).Range("A1:A3").Copy Worksheets(I).Range("A1:A3")
Worksheets(I).Protect Password = "DBI"
Next
End Sub