Extend Code To Other Worksheets

[BENSON]

The VBA code below works fine on my worksheet called "gardens" where I require some help is: I have three identical worksheets named SEA POINT WATER FRONT and REGENT RD in the same workbook .I would like the macro to run on all the worksheets when I press the comand button.

Many Thanks


Sub Button1574_Click()
'
' Button1574_Click Macro
' Macro recorded 2007/02/01 by Reception
'
Application.ScreenUpdating = False
Range("B1").Select
ActiveCell.FormulaR1C1 = "5/22/2007"
Range("D5").Select
Cells.Replace What:="Period ending 14082006", Replacement:= _
"period ending 19062006", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase _
:=False, SearchFormat:=False, ReplaceFormat:=False
Range("D5").Select


Application.ScreenUpdating = True
End Sub

[Charlize]

All the worksheets must have the same layout ...

Dim ws As Worksheet
Application.Screenupdating = False
For Each ws In ThisWorkbook.Worksheets
ws.range("B1").FormulaR1C1 ...
'Rest of your code
'maybe use ws.activate when you use range instead of ws.range
Next ws
Application.Screenupdating = True
Charlize

[BENSON]

Thanks for the reply Charlize, I have altered code as per below but get the following error message :Run Time Error Wrong Number Of Auguments Or Invalid Property Assignment any Ideas ?

Thanks




Sub Button1584_Click()
'
' Button1584_Click Macro
' Macro recorded 2007/02/02 by Reception
'
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
ws.Activate("B5").FormulaR1C1 = "1/1/2007"

Range("D5").Select
Cells.Replace What:="Period ending 01012007", Replacement:= _
"period ending 29012007", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase _
:=False, SearchFormat:=False, ReplaceFormat:=False
Range("D5").Select
Next ws
Application.ScreenUpdating = True
End Sub

[Charlize]

You must declare Dim ws As Worksheet before you can use ws as a worksheetreference. Place option expliciton top of your module. Normally, you will be notified that you are using a variable that isn't declared.

Charlize

ps. first make a backup copie of your original when you want to test this code. You'll never know.
- Maybe make it public (on top of your module)
Public ws As Worksheet

[Bob Phillips]

Charlize's advice is sound, but it will not resolve your problem. Because you have not declared the variable ws, it will implicitly type as Variant, which can accommodate objects including worksheets.

Your problem lies in the line of code



ws.Activate("B5").FormulaR1C1 = "1/1/2007"

it should be



ws.Range("B5").FormulaR1C1 = "1/1/2007"


or even



ws.Range("B5").Value = "1/1/2007"

[Charlize]

I think he mixed up my ws.activate and ws.range suggestions. I meant to first activate with ws.activate and on the next line the range(...).formulaR1C1 = "" thing. Not a combination of the both.

Charlize

[BENSON]

I am sorry guys I seem to be making heavy going of this one .I have tried your suggestions and the code I now Have (Shown Below ) returns Error Message Run Time Error 1004 Application Defined Or Object Defined Error.
Could it be that I have some other worksheets in the workbook that should not be effected by the macro.

Thanks



Sub Button1584_Click()
'
' Button1584_Click Macro
' Macro recorded 2007/02/02 by Reception
'
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
ws.Range("B5").Value = "1/1/2007"

Range("D5").Select
Cells.Replace What:="Period ending 01012007", Replacement:= _
"period ending 29012007", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase _
:=False, SearchFormat:=False, ReplaceFormat:=False
Range("D5").Select
Next ws
Application.ScreenUpdating = True
End Sub

[Bob Phillips]

Which line?

[Charlize]

You haven't got any merged cells in that worksheet with the errorcode ?
Where is that buttoncode located. In a private sub of a form ?
Place code of button in a module in sub but_1584. In your button1584_click code refer to but_1584.

Charlize

[Norie]

Benson

You have unqualified references like this.

Cells


Sub Button1584_Click()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
With ws
.Range("B5").Value = "1/1/2007"
ws.Cells.Replace What:="Period ending 01012007", Replacement:= _
"period ending 29012007", LookAt:=xlPart, SearchOrder:=xlByRows
End With
Next ws
Application.ScreenUpdating = True
End Sub

[BENSON]

Thanks for the help you guys ,the line of code which seems to be the problem is


.Range("B5").Value = "1/1/2007"

[Norie]

BENSON

So that's the line giving the error?

It looks perfectly fine to me.:bug:

[Charlize]

Tried you code and it works fine for me. Inside your with loop you can change ws.cells with .cells because you already use with ws

I think cells means all the cells in a worksheet.

Must say that I've put your code in a normale module and not in a form module.

Charlize

ps. when you use inputbox you can ask for the period ending value that needs to be changed in a new value (so you don't have to alter the macro manually). say you input 01012007 and new value 29012007 - alter the line with ... what:="Period ending " & value_received_from_inputbox, Replacement:="Period ending " & _
inputbox_new_ending_value