Solved: Want to apply code to all sheets but wont work

[Hamond]

I've adopted some code that users helped me with develop previously on this site. The aim of this macro is to update cells (autofill formulas) in all worksheets other than those specfied to be exempt.

This macro works fine when you run it with a given sheet selected but doesn't seem to apply the code to the other sheets in the workbook. Furthemore when I activate one of the sheets that I have listed the main code not to run on (e.g the sheet called "data"), it runs it anyway even though it is listed as one of the sheets not to run the code on!

Can anyone shed some light? I'm very confused as the code seems quite logical to me!


Sub AAATest()
Dim Sh As Worksheet
Dim lastdatecell As Integer

For Each Sh In Worksheets

If Sh.Name <> "Current Quotes" And Sh.Name <> "test" And Sh.Name <> "data" Then

With Sh
lastdatecell = (Cells(4, 1).End(xlDown).Row) 'determine last populated cell
Range("e4:g4").Select
Selection.AutoFill Destination:=Range("e4:g" & lastdatecell) 'autofills selection
End With
End If
Next Sh
End Sub

[HaHoBe]

Hi, Hamond,

there´s no relation between the worksheet and the range on each sheet:


Sub AAATest()
Dim Sh As Worksheet
Dim lastdatecell As Integer

For Each Sh In Worksheets
Select Case Sh.Name
Case "Current Quotes", "test", "data"
Case Else
With Sh
lastdatecell = .Cells(4, 1).End(xlDown).Row 'determine last populated cell
.Range("e4:g4").AutoFill Destination:=.Range("e4:g" & lastdatecell) 'autofills selection
End With
End Select
Next Sh
End Sub Ciao,
Holger