Running VBA Code on two worksheets simultaneously

[Wamhoi]

Hi All, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I wanted to know if it is possible to run a VBA simultaneously on two worksheets (in order to increase speed and reduce coding) <o></o>
<o></o>
For example, I want the following code to run on worksheet A & B<o></o>
<o></o>
Sheets("A").Select<o></o>
'Weeknum
Range("AE2").Formula = "=Int(Day(AD2) / 7 + 0.99)"
Range("AE2").AutoFill Range("AE2").Resize(Lastline - 1)
Range("AE1") = "WeekNum"
Range("AE:AE").Value = Range("AE:AE").Value<o></o>
<o></o>
Sheets("B").Select<o></o>
'Weeknum
Range("AE2").Formula = "=Int(Day(AD2) / 7 + 0.99)"
Range("AE2").AutoFill Range("AE2").Resize(Lastline - 1)
Range("AE1") = "WeekNum"
Range("AE:AE").Value = Range("AE:AE").Value<o></o>
<o></o>
Can I combine these codes so it runs without jumping from one sheet to the next? <o></o>

 

[VoG]

Try like this

Dim ws As Worksheet
For Each ws In Worksheets(Array("A", "B"))
    With ws
        .Range("AE2").Formula = "=Int(Day(AD2) / 7 + 0.99)"
        .Range("AE2").AutoFill .Range("AE2").Resize(Lastline - 1)
        .Range("AE1") = "WeekNum"
        .Range("AE:AE").Value = .Range("AE:AE").Value
    End With
Next ws

 

[Wamhoi]

Tested and works great thanks!

 

[Wamhoi]

Sorry to reopen the chat but I'm having a bit of trouble adding some additional code. I keep getting a runtime error.

Dim ws As Worksheet
For Each ws In Worksheets(Array("A", "B"))
With ws
.Range("AE2").Formula = "=Int(Day(AD2) / 7 + 0.99)"
.Range("AE2").AutoFill .Range("AE2").Resize(Lastline - 1)
.Range("AE1") = "WeekNum"
.Range("AE:AE").Value = .Range("AE:AE").Value

'This part isn't working properly
.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

.Range("A1").Select
If ActiveSheet.AutoFilterMode = True Then Selection.AutoFilter
If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData

.Range("$A$1:$AH$18").AutoFilter Field:=33, Criteria1:="No"
.Rows("2:2").Select 'delete from row 2 to lastline
.Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
.Range("A1").AutoFilter

End With
Next ws

 

[VoG]

Try

Dim ws As Worksheet, LR As Long
For Each ws In Worksheets(Array("A", "B"))
    With ws
        .Range("AE2").Formula = "=Int(Day(AD2) / 7 + 0.99)"
        .Range("AE2").AutoFill .Range("AE2").Resize(Lastline - 1)
        .Range("AE1") = "WeekNum"
        .Range("AE:AE").Value = .Range("AE:AE").Value
        
        'This part isn't working properly
        .Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        
        .Range("A1").Select
        If .AutoFilterMode = True Then Range("A1").AutoFilter
        If .FilterMode = True Then .ShowAllData
        
        .Range("$A$1:$AH$18").AutoFilter Field:=33, Criteria1:="No"
        LR = .Range("A2").End(xlDown).Row
        .Range("A2:A" & LR).EntireRow.Delete
        .Range("A1").AutoFilter
    End With
Next ws