Excel Lastrow autofill issue with vba

Question

I'm having an issue with my code to autofill some using VBA in Excel. I keep getting the error "AutoFill method of range class failed" but I'm not sure what is wrong with the code. it looks okay to me but maybe I'm missing something? The code is as follows:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
    Cancel As Boolean)


A = MsgBox("Do you really want to save the workbook?", vbYesNo)
If A = vbNo Then Cancel = True

Dim lrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A" & lastrow)
Range("A1:A" & lastrow).Select

'Range("D1").Select
'Selection.AutoFill Destination:=Range("D1:D" & lastrow)
'Range("D1:D" & lastrow).Select
'Range("H1").Select
'Selection.AutoFill Destination:=Range("H1:H" & lastrow)
'Range("H1:H" & lastrow).Select
'Range("L1").Select
'Selection.AutoFill Destination:=Range("L1:L" & lastrow)
'Range("L1:L" & lastrow).Select
End Sub

Any help in the right direction would be greatly appreciated. (Note I am just trying to fix Column A I figure if I can get that working the rest will fall into line). Thanks!!! -D

what do you intend to do here? Your code seems ok, but it will work if the sheet the user is on, is the one you need. There are better ways to do it but we need more info. — Damian, Oct 02 '18 at 15:21
This is supposed to be a simple code that basically autofills the columns shown (A, D, H and L) when the spreadsheet is saved. — Deke, Oct 02 '18 at 15:30
`lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row` How do you know the active sheet is the sheet you think it is?. See [THIS](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) on how to find the last row. — Siddharth Rout, Oct 02 '18 at 15:41
Also what does Col A have? If is a formula then do not use Autofill. You can directly fill the fromula in one go from`A1:A & Lastrow` For example `Sheets("Sheet1").Range("A1:A" & lastrow).Formula = Sheets("Sheet1").Range("A1").Formula` — Siddharth Rout, Oct 02 '18 at 15:43
Use `Option Explicit`. You may want to see [To ‘Err’ is Human](http://www.siddharthrout.com/index.php/2017/12/15/to-err-is-human/) — Siddharth Rout, Oct 02 '18 at 15:46
@SiddharthRout - thanks but not the cause of the problem as OP is not using option explicit tut tut. — SJR, Oct 02 '18 at 15:47
Siddharth, there is only one sheet in this workbook. There are no other sheets so I'm pretty sure it's the only active sheet. — Deke, Oct 02 '18 at 15:48
SJR, there is nothing below A1 that is what I'm trying to fill to the end, A1 itself has a formula. I'll make the change to `dim lastrow as long` I should have seen that. but that's why I ask! Thanks for the help. I'll let you know if that fixes the issue. — Deke, Oct 02 '18 at 15:51
You can't use autofill on only value . `lastrow` will be 1 so you need to base the last row on another column with something in it. — SJR, Oct 02 '18 at 15:54
`dim lastrow as Long` didn't fix the issue. still same error. The column A1 has a simple formula =B1&C1. — Deke, Oct 02 '18 at 15:54
I have posted an answer, You may have to refresh the page to see it — Siddharth Rout, Oct 02 '18 at 15:55
SJR so I should use something like ` selection.autofill Destination:=Range("A1:B" & lastrow)` I did try that but it didn't seem to work it would overwrite what was in cells A1 and B1 and select them but nothing else. — Deke, Oct 02 '18 at 15:56
But how do you know how far to copy down A1? That's the vital bit of info we're missing. — SJR, Oct 02 '18 at 15:58
Siddharth, I plugged in your code. Doesn't seem to do anything. Not erroring out though so that's better progress than I've made. SJR, currently it goes to line/row 4209 but this can change depending on what we plug in which is why I was trying to use lastrow. — Deke, Oct 02 '18 at 16:00
Then replace `lrow = .Range("A" & .Rows.Count).End(xlUp).Row` with `lrow = .Range("B" & .Rows.Count).End(xlUp).Row` — Siddharth Rout, Oct 02 '18 at 16:02
That did it! works perfectly! Thanks so much for everyone's help!!!! — Deke, Oct 02 '18 at 16:03

Siddharth Rout · Accepted Answer · 2018-10-02T16:09:58.563

Is this what you are trying?

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim Ret As Variant

    Ret = MsgBox("Do you really want to save the workbook?", vbYesNo)

    If Ret = vbNo Then
        Cancel = True
    Else
        '~~> Make these changes only if user wants to save the workbook
        Dim lrow As Long
        Dim ws As Worksheet

        Set ws = ThisWorkbook.Sheets(1)

        With ws
            '~~> If Col B has data then find the last row in Col B
            lrow = .Range("B" & .Rows.Count).End(xlUp).Row

            .Range("A1:A" & lrow).Formula = .Range("A1").Formula
        End With
    End If
End Sub

Excel Lastrow autofill issue with vba

1 Answers1