Applying autofill macro that covers multiple sheets/columns

[bawpie]

I've already searched on autofill macros and found bits and pieces that help,
but my lack of understanding of the actual VBA is hampering my success with
this particular macro.

The macro in question is set up to copy and paste data to various
spreadsheets and then add formulas to each sheet depending on what I'm
looking for. I've managed to do this with the macro, but when it comes to
autofilling the formula columns on seperate sheets it's working, but only to
a point.

Using the following formula on sheet A, the macro works. It autofills to
the point in column A on Sheet A in the right column.

Sheets("Sheet A").Select

Range("O2").Select
ActiveCell.Formula = "=NETWORKDAYS(M2,H2,Lookup!$A$2:$A$82)-1"

Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("O2").AutoFill _
Destination:=.Range("O2:O" & LastRow)
End With

However, when the macro moves onto sheet 2, it is still using the reference
from sheet A (i.e. on sheet A formula is dragged down to row 371 which is
correct, but on sheet B it's still pulling to row 371 which is too long).

Sheets("Sheet B").Select

Range("O2").Select
Application.CutCopyMode = False
ActiveCell.Formula = "=VLOOKUP(A2,Initials!A:H,8,FALSE)"

Dim LastRow2 As Long
With ActiveSheet
LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("O2").AutoFill _
Destination:=.Range("O2:O" & LastRow)
End With

There's a third sheet that has the same behaviour as sheet B. I'm guessing
it's something to do with how I'm defining the 'Lastrow' but any help on the
matter would be much appreciated!

 

[Jacob Skaria]

A typo
Destination:=.Range("O2:O" & LastRow)
should be
Destination:=.Range("O2:O" & LastRow2)
as below


Sheets("Sheet B").Select

Range("O2").Select
Application.CutCopyMode = False
ActiveCell.Formula = "=VLOOKUP(A2,Initials!A:H,8,FALSE)"

Dim LastRow2 As Long
With ActiveSheet
LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("O2").AutoFill _
Destination:=.Range("O2:O" & LastRow2)
End With


If this post helps click Yes

 

[Jacob Skaria]

You dont need to select or activate the sheet to do this...Use the worksheet
object...as below..which will be more faster..especially when you have work
with more number of sheets

Sub Macro1()
Dim ws As Worksheet, LastRow As Long

Set ws = Sheets("Sheet A")
LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row

ws.Range("O2").Formula = "=NETWORKDAYS(M2,H2,Lookup!$A$2:$A$82)-1"
ws.Range("O2").AutoFill Destination:=ws.Range("O2:O" & LastRow)
End Sub

If this post helps click Yes

 

[Per Jessen]

Hi

Try to use Sheets("Sheet B").Activate not .Select,
or even better do not activate sheets at all, but use sheet references:

Sub aaa()
Dim LastRow As Long
Dim LastRow2 As Long
Dim shA As Worksheet
Dim shB As Worksheet

Set shA = Sheets("Sheet A")
shA.Range("O2").Formula = _
"=NETWORKDAYS(M2,H2,Lookup!$A$2:$A$82)-1"

With shA
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("O2").AutoFill _
Destination:=.Range("O2:O" & LastRow)
End With
Application.CutCopyMode = False

Set shB = Sheets("Sheet B")
shB.Range("O2").Formula = _
"=VLOOKUP(A2,Initials!A:H,8,FALSE)"
With shB
LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("O2").AutoFill _
Destination:=.Range("O2:O" & LastRow)
End With
End Sub

Regards,
Per

 

[bawpie]

Yes, I am that stupid. Thank you very much!

A typo
Destination:=.Range("O2:O" & LastRow)
should be
Destination:=.Range("O2:O" & LastRow2)
as below


Sheets("Sheet B").Select

Range("O2").Select
Application.CutCopyMode = False
ActiveCell.Formula = "=VLOOKUP(A2,Initials!A:H,8,FALSE)"

Dim LastRow2 As Long
With ActiveSheet
LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("O2").AutoFill _
Destination:=.Range("O2:O" & LastRow2)
End With


If this post helps click Yes