copy data to each worksheet
 

Hello:
I wrote a macro to copy one worksheet for each week of the year and name
each tab to each weeks date using Dataserial and it works fine. Now what I
need to do when some areas change on the worksheet is to be able to change it
on one and have the macro copy the changes to each worksheet. For example on
the timesheet when billing codes change that may be in range B2:B12 is to
have the macro copy that range onto each worksheet. please help.
--
He4Giv (Dick)

copy data to each worksheet
 

I know 2 ways:

1- having the comon data on a another sheet (kinda of database for prices)

2- using the change event of the thisworkbook module
Private Sub Workbook_SheetChange( _
ByVal Sh As Object, ByVal Target As Range)
' Sh is the changed sheet
' we will propagate to the other sheets
Dim S As Worksheet, cell As Range
For Each S In ThisWorkbook.Sheets
If S.Name < Sh.Name Then ' it's one of the other sheets
For Each cell In Sh.Range(Target.Address)
S.Range(cell.Address).Value = Sh.Range(cell.Address).Value
Next cell
End If
Next S
End Sub

copy data to each worksheet
 

SORRY I forgot a little detail:
(here the right code)

the point is that the macro change some cells and react when a cell is
changed - this is called an infinite loop. With this code we stop the
events while changing the cells, and reactivate events just after

Private Sub Workbook_SheetChange( _
ByVal Sh As Object, ByVal Target As Range)
' Sh is the changed sheet
' we will propagate to the other sheets
Application.EnableEvents = False
Dim S As Worksheet, cell As Range
For Each S In ThisWorkbook.Sheets
If S.Name < Sh.Name Then ' it's one of the other sheets
For Each cell In Sh.Range(Target.Address)
S.Range(cell.Address).Value = Sh.Range(cell.Address).Value
Next cell
End If
Next S
Application.EnableEvents = True
End Sub

copy data to each worksheet
 

Hi:
I may have said this wrong..I want to copy the data in range B2:B12 from
sheet 1 onto all the other worksheets. All 52 worksheets in this file are
exactly the same. they are timesheets that employees fill out weekly. At
times the reason codes change in the headers.
--
He4Giv (Dick)


"abcd" wrote:

SORRY I forgot a little detail:
(here the right code)

the point is that the macro change some cells and react when a cell is
changed - this is called an infinite loop. With this code we stop the
events while changing the cells, and reactivate events just after

Private Sub Workbook_SheetChange( _
ByVal Sh As Object, ByVal Target As Range)
' Sh is the changed sheet
' we will propagate to the other sheets
Application.EnableEvents = False
Dim S As Worksheet, cell As Range
For Each S In ThisWorkbook.Sheets
If S.Name < Sh.Name Then ' it's one of the other sheets
For Each cell In Sh.Range(Target.Address)
S.Range(cell.Address).Value = Sh.Range(cell.Address).Value
Next cell
End If
Next S
Application.EnableEvents = True
End Sub

copy data to each worksheet
 

What do you mean ? You do not want "any" cell to be duplicated but only
a specific area in the Sheet1 ?

This does not change a lot of thing: just check first if the Target
(changed) cell is or is not in the wanted area. Then you also need to
put this code inside the Sheet1 (not in ThisWorkbook)


so the code (simplified) becomes:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WantedAreas$ = "B2:B12"
If Not (Intersect(Target, Me.Range(WantedAreas$)) Is Nothing) Then
'propagate to other sheets
Application.EnableEvents = False
Dim S As Worksheet, cell As Range
For Each S In ThisWorkbook.Sheets
If S.Name < Me.Name Then ' it's one of the other sheets
S.Range(WantedAreas$).Formula = Me.Range(WantedAreas$).Formula
End If
Next S
Application.EnableEvents = True
End If
End Sub


now only a change in this area on Sheet1 will be propagated...
(Me represents the sheet where the code is written, so Sheet1)

copy data to each worksheet
 

Even shorter,
now we only detect from Sheet1, the macro will never call itself,
so we can write it shorter (no more need to stop the events inside the
macro). In Sheet1 vba code:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WantedAreas$ = "B2:B12"
If Not (Intersect(Target, Me.Range(WantedAreas$)) Is Nothing) Then
'propagate to other sheets
Dim S As Worksheet, cell As Range
For Each S In ThisWorkbook.Sheets
If S.Name < Me.Name Then ' it's one of the other sheets
S.Range(WantedAreas$).Formula = Me.Range(WantedAreas$).Formula
End If
Next S
End If
End Sub