AutoFill Formula To The Last Row With Data
Hello,
I am trying to write a macro which filters columns A and B from ExcelWorkbook1 and copies them into columns A and B in ExcelWorkbook2.
Then, in the ExcelWorkbook2 a new column C is added. C1 = name of the column, C2 = the formula "=LEFT" extracts some text from B2. I want this formula to be autofilled in the whole column C (up to the same row as the column B) but I fail foolishly. I have tried 3 different version of Selection.Autofill but none worked.
The error message is: Run-time error '1004'; Autofill method of Range has failed.
When I degub, the .AutoFill Destination:=Range("C2:C" & Lastrow&) is marked but I can't see any reason for failing.
I would be very glad if you can let me know what I made wrong in my code.
Thank you very much,
SaburaZera
The code:
Code:
Private Sub CommandButton2_Click()
Set NewBook = Workbooks.Add
Application.DisplayAlerts = False
With NewBook
.Title = "Title"
.Subject = "Subject"
.SaveAs Filename:="ExcelWorkbook2" & ".xlsx"
End With
Application.DisplayAlerts = True
ActiveSheet.Select
ActiveSheet.Name = "Sheet2"
'Copy the rows with empty cells in C from ExcelWorkbook1 into ExcekWorkbook2
Workbooks("ExcelWorkbook1.xlsm").Activate
Sheets("Sheet1").Range("A3:D3").Select
Selection.AutoFilter Field:=3, Criteria1:="="
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks("ExcelWorkbook2.xlsx").Sheets("Sheet1").Activate
Sheets("Sheet2").Range("A1").Select
ActiveSheet.Paste
'Delete columns C and D
Sheets("Sheet2").Columns("C:D").Select
Selection.ClearContents
Sheets("Sheet2").Columns("B").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Dim Lastrow As Long
Sheets("Sheet2").Range("C1").Select
ActiveCell.FormulaR1C1 = "Received on"
Selection.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],10)"
ActiveCell.Copy
Lastrow = Worksheets("Sheet2").Range("B2").End(xlDown).Row
With Worksheets("Sheet2").Range("C2")
.AutoFill Destination:=Range("C2:C" & Lastrow&)
End With
End Sub
