Simple VBA : Why does not work?

jbesclapez

Board Regular
Joined
Feb 6, 2010
Messages
235
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!


VBA Code:
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,460
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
There's no need to select. You could use:

Code:
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

Board Regular
Joined
Feb 6, 2010
Messages
235
Thanks for your answer Rory!

I simply run your VB ang get an error on
VBA Code:
LastRow = rs.Cells(1, rs.Rows.Count).End(xlUp).Row
so I revert it to
VBA Code:
 LastRow = rs.Cells(1, 1).End(xlUp).Row
But still it is missing one of my Sheet!!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,460
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Sorry, that should have been:

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

Which sheet is it missing?
 

jbesclapez

Board Regular
Joined
Feb 6, 2010
Messages
235
Rory, Thanks for pointing at me to the right direction. I found my mistake here is my result. I changed around the lastRow


VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,355
Messages
5,624,207
Members
416,017
Latest member
moritz210

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top