Solved: Replicating changes to one WS to others

[mugcy]

HI guys I have a problem. I'm a newbie

I have a workbook like in the attachment.it's got employee names for each restaurant with their workhours. the

problem I have is when adding a new employee or editing an employee.

For example lets say I need to add anew employee @"Period2" in the "aabc" restaurant. when I type in the new

employee details (Name,FT/PT,Payroll Number, Restaurant) it should replecate on "period3" to "period7"

same with updating these details... they should replicate from the next worksheet onwards.



IS THERE An easy way of doing these. It dosen't have to be VB script. CAN SOME ONE HELP ME PLEASE

[Bob Phillips]

Try this

[mugcy]

Thank you Very Much. That works. But I was wondering if there is a way to do it in a MACRO

[Bob Phillips]

IS THERE An easy way of doing these. It dosen't have to be VB script.

Why use code when there is no need?

[mugcy]

HI,

It's just that in reality I have 10 of these workbooks with 20 worksheets. Some WBs have 4Pages of data on each WS. It would take a long time to finish them all.

However I did create a macro. Is there any way to enter the sheet name (for instance as a string) so I could adapt it to update all the other work sheets. The new workbook is attached.


Thank you verymuch for all the help



[VBA]Sub UPdate()
'
' UPdate Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveWindow.SmallScroll Down:=-12
Range("B7").Select
ActiveCell.FormulaR1C1 = "=IF('1May09-7May09'!RC="""","""",'1May09-7May09'!RC)"
Range("B7").Select
Selection.AutoFill Destination:=Range("B7:E7"), Type:=xlFillDefault
Range("B7:E7").Select
Selection.AutoFill Destination:=Range("B7:E11"), Type:=xlFillDefault
Range("B7:E11").Select

Range("B13").Select
ActiveCell.FormulaR1C1 = "=IF('1May09-7May09'!RC="""","""",'1May09-7May09'!RC)"
Range("B13").Select
Selection.AutoFill Destination:=Range("B13:E13"), Type:=xlFillDefault
Range("B13:E13").Select
Selection.AutoFill Destination:=Range("B13:E18"), Type:=xlFillDefault
Range("B13:E18").Select

ActiveWindow.SmallScroll Down:=6
Range("B20").Select
ActiveCell.FormulaR1C1 = "=IF('1May09-7May09'!RC="""","""",'1May09-7May09'!RC)"
Range("B20").Select
Selection.AutoFill Destination:=Range("B20:E20"), Type:=xlFillDefault
Range("B20:E20").Select
Selection.AutoFill Destination:=Range("B20:E24"), Type:=xlFillDefault
Range("B20:E24").Select

ActiveWindow.SmallScroll Down:=9
Range("B31").Select
ActiveCell.FormulaR1C1 = "=IF('1May09-7May09'!RC="""","""",'1May09-7May09'!RC)"
Range("B31").Select
Selection.AutoFill Destination:=Range("B31:E31"), Type:=xlFillDefault
Range("B31:E31").Select
Selection.AutoFill Destination:=Range("B31:E35"), Type:=xlFillDefault
Range("B31:E35").Select

ActiveWindow.SmallScroll Down:=6
Range("B37").Select
ActiveCell.FormulaR1C1 = "=IF('1May09-7May09'!RC="""","""",'1May09-7May09'!RC)"
Range("B37").Select
Selection.AutoFill Destination:=Range("B37:E37"), Type:=xlFillDefault
Range("B37:E37").Select
Selection.AutoFill Destination:=Range("B37:E41"), Type:=xlFillDefault
Range("B37:E41").Select

ActiveWindow.SmallScroll Down:=6
Range("B43").Select
ActiveCell.FormulaR1C1 = "=IF('1May09-7May09'!RC="""","""",'1May09-7May09'!RC)"
Range("B43").Select
Selection.AutoFill Destination:=Range("B43:E43"), Type:=xlFillDefault
Range("B43:E43").Select
Selection.AutoFill Destination:=Range("B43:E48"), Type:=xlFillDefault
Range("B43:E48").Select
End Sub[/VBA]

[Bob Phillips]

[vba]

Sub Update(wsName)
'
' Update Macro
'
' Keyboard Shortcut: Ctrl+q
'
Dim mFormula As String


With Worksheets(wsName)

mFormula = "=IF('" & .Name & "'!RC="""","""",'" & .Name & "'!RC)"

.Range("B7").FormulaR1C1 = mFormula
.Range("B7").AutoFill Destination:=.Range("B7:E7"), Type:=xlFillDefault
.Range("B7:E7").AutoFill Destination:=.Range("B7:E11"), Type:=xlFillDefault

.Range("B13").FormulaR1C1 = mFormula
.Range("B13").AutoFill Destination:=.Range("B13:E13"), Type:=xlFillDefault
.Range("B13:E13").AutoFill Destination:=.Range("B13:E18"), Type:=xlFillDefault

.Range("B20").FormulaR1C1 = mFormula
.Range("B20").AutoFill Destination:=.Range("B20:E20"), Type:=xlFillDefault
.Range("B20:E20").AutoFill Destination:=.Range("B20:E24"), Type:=xlFillDefault

.Range("B31").FormulaR1C1 = mFormula
.Range("B31").AutoFill Destination:=.Range("B31:E31"), Type:=xlFillDefault
.Range("B31:E31").AutoFill Destination:=.Range("B31:E35"), Type:=xlFillDefault

.Range("B37").FormulaR1C1 = mFormula
.Range("B37").AutoFill Destination:=.Range("B37:E37"), Type:=xlFillDefault
.Range("B37:E37").AutoFill Destination:=.Range("B37:E41"), Type:=xlFillDefault

.Range("B43").FormulaR1C1 = mFormula
.Range("B43").AutoFill Destination:=.Range("B43:E43"), Type:=xlFillDefault
.Range("B43:E43").AutoFill Destination:=.Range("B43:E48"), Type:=xlFillDefault
End With
End Sub
[/vba]

[mugcy]

Thankyou very much