CODEWITHSUNDEEP
excelvba
Chris.S
Chris.S

Reputation: 37

Autofill error on Excel VBA code

trying to work the VBA autofill function (end of code block) but I am running into an error everytime I try the execute the code. I get "Autofill method of Range class failed". Can someone help me out here? Searched google but nothing works. Probably overlooking something small. Thanks in advance for the help. 

Sub UpdateLAB() '---> still need to work on this

'author: css

Dim SalesBook As Workbook
Dim ws2 As Worksheet
Dim wspath As String
Dim n As Integer
Dim FirstRow As Long
Dim LastRow As Long
Dim LastRow2 As Long
Dim sourceCol As Integer
Dim RefCellValue2 As String
Dim ps As String
Dim Platts As Workbook


Application.Calculation = xlCalculationAutomatic

Set SalesBook = Workbooks("ALamb.xlsm")
Set ws2 = SalesBook.Worksheets("US LAB Price")
wspath = "C:\Users\scullycs\Desktop\P&O\Platts Data\Platts 2016.xlsm"

FirstRow = ws2.Range("B4").Row
LastRow = ws2.Range("B4").End(xlDown).Row + 1
LastRow2 = ws2.Range("c4").End(xlDown).Row
sourceCol = 2 'refers to the column your data is in

    For n = FirstRow To LastRow
    RefCellValue2 = Cells(n, sourceCol).Value

        If IsEmpty(RefCellValue2) Or RefCellValue2 = "" Then
        Cells(n, sourceCol).Offset(0, -1).Copy
        SalesBook.Worksheets("Control Page").Range("C8").PasteSpecial     (xlPasteValues)

        Else

     End If

Next n

        ps = SalesBook.Worksheets("Control Page").Range("C9").Text
        Set Platts = Workbooks.Open(wspath)
        Platts.Worksheets(ps).Activate
        Range("A13").End(xlDown).Select
        Selection.Offset(0, 11).Select

    If Selection.Value = "" Then
        MsgBox ("Platts data does not exist")
        Platts.Close

        Else
        Selection.Copy
        Set SalesBook = Workbooks("ALamb.xlsm")
        SalesBook.Worksheets("US LAB Price").Range("b1").End(xlDown).Offset(1, 0).PasteSpecial (xlPasteValues)
'this is where I get the error
        SalesBook.Worksheets("US LAB Price").Range("c4").AutoFill Destination:=Range("C4:C" & LastRow2), Type:=xlFillDefault

        Platts.Close

    End If

End Sub

Upvotes: 0

Views: 307

Answers (1)

Mukul Varshney
Mukul Varshney

Reputation: 3141

Most probably your ranges are not overlapping OR range is too big. In case you want to refer, link.

  1. Check the value of LastRow2.
  2. Make sure the fill range is from same sheet to make them over lapping. To do so break your statement into simple steps. Later you can combine.

Will suggest to break down the statement into 

Set SourceRange = SalesBook.Worksheets("US LAB Price").Range("C4:C4")
Set fillRange = SalesBook.Worksheets("US LAB Price").Range("C4:C" & LastRow2)
SourceRange.AutoFill Destination:=fillRange, Type:=xlFillDefault

Upvotes: 1

Related Questions