Simple VBA : Why does not work?

[jbesclapez]

Hi THere,
I need a macro to run on all sheets and do an autofill if C2 and C3 are not empty...
This has to run on all the worksheets named below...
However it seems it does the first one and not the otehrs.
No idea why
Thanks for having a look!

Dim rs As Worksheet
Dim LastRow As Long



'All worksheet excep OneToOne as the structure of OneToOne is different
For Each rs In ThisWorkbook.Worksheets
   If rs.Name = "A_R" Or rs.Name = "A_C" Or rs.Name = "A_L" Or rs.Name = "A_" Then
        rs.Select
        rs.Range("J3:N10001").ClearContents
        LastRow = rs.Cells(1, 1).End(xlDown).Row
        rs.Range("J2:N2").Select
            If IsEmpty(rs.Range("C2").Value) = False And IsEmpty(rs.Range("C3").Value = False) Then
                Selection.AutoFill Destination:=rs.Range("J2:N" & LastRow), Type:=xlFillDefault
            End If
   End If
Next rs

 

[RoryA]

There's no need to select. You could use:

Dim rs As Worksheet
Dim LastRow As Long

'All worksheet excep OneToOne as the structure of OneToOne is different
For Each rs In ThisWorkbook.Worksheets
   If rs.Name = "A_R" Or rs.Name = "A_C" Or rs.Name = "A_L" Or rs.Name = "A_" Then
        rs.Range("J3:N10001").ClearContents
        LastRow = rs.Cells(1, rs.rows.count).End(xlUp).Row
            If IsEmpty(rs.Range("C2").Value) = False And IsEmpty(rs.Range("C3").Value = False) Then
                rs.Range("J2:N2").AutoFill Destination:=rs.Range("J2:N" & LastRow), Type:=xlFillDefault
            End If
   End If
Next rs

 

[jbesclapez]

Thanks for your answer Rory!

I simply run your VB ang get an error on

LastRow = rs.Cells(1, rs.Rows.Count).End(xlUp).Row

so I revert it to

 LastRow = rs.Cells(1, 1).End(xlUp).Row

But still it is missing one of my Sheet!!

 

[jbesclapez]

And by the way, the lastrow is different on each sheet...

 

[RoryA]

Sorry, that should have been:

LastRow = rs.Cells(rs.Rows.Count, "A").End(xlUp).Row

Which sheet is it missing?

 

[jbesclapez]

Rory, Thanks for pointing at me to the right direction. I found my mistake here is my result. I changed around the lastRow

Dim rs As Worksheet
Dim LastRow As Long

'All worksheets except OneToOne as the structure of OneToOne is different
For Each rs In ThisWorkbook.Worksheets
   If rs.Name = "A_C" Or rs.Name = "A_R" Or rs.Name = "A_L" Or rs.Name = "A_M" Then
        rs.Range("J3:N10001").ClearContents
        'LastRow = rs.Cells(1, 1).End(xlUp).Row
        LastRow = rs.Cells(1, 1).End(xlDown).Row
        MsgBox (LastRow)
            If LastRow > 3 And LastRow <> 10002 Then
                rs.Range("J2:N2").AutoFill Destination:=rs.Range("J2:N" & LastRow), Type:=xlFillDefault
            End If
   End If
Next rs