Autofill formula one table column to the right

    Hey there


    I think i have a really simple problem, but i suck at VBA, and have tried to find someone with a similar problem without luck.

    So i use "Find" to find my source range (a column within a table -a new one every month, hence the 'find' part), and then i want to autofill this range one column to the left. But the following code gives me an error when i run it.

    Could someone tell me what i do wrong with autofill?



    Code
    Sub Autofill()


Sheets("Forecast").Select
Cells.Find(What:=Range("PreviousMonth").Value, LookIn:=xlValues).Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:B101"), Type:=xlFillDefault


End Sub

    This is your problem line, you activecell cannot have a range . Do you mean ActiveSheet?


    Code
    Selection.AutoFill Destination:=ActiveCell.Range("A1:B101"), Type:=xlFillDefault

    Which sheet are you trying to autofill on?


    Explain step by step what you are trying to do.

    Oh sorry!
    It's on the Forecast AC sheet. but the macro will be assigned to a button the SAPImport sheet hence: 'Sheets("Forecast").Select'
    Then i search for the month givin by the named cell "PreviousMonth" that is
    =TEXT(DATE(2020;IF(MONTH(TODAY())-2>0;MONTH(TODAY())-2;12);1);"[$-409]MMMM")

    This will find the header cell and then i Offset and select the whole table column:


    Cells.Find(What:=Range("PreviousMonth").Value, LookIn:=xlValues).Offset(1, 0).Range("A1").Select


    Then i what the formula in this table colunm to be draged / autofilled to the next month (the adjacent right table column)


    Selection.AutoFill Destination:=ActiveCell.Range("A1:B101"), Type:=xlFillDefault

    It was the only way that i could come up with, that made my VBA 'dymanic'.I know that there's probably a smarter way to work around this, but it was what i could come up with with my VBA skill-set.

    Hello DE,


    Try this:


    Code
    Sub AutoFill()

Dim ws3 As Worksheet: Set ws3 = Sheet12

    ws3.Select
    Cells.Find(What:=Range("PreviousMonth").Value, LookIn:=xlValues).Offset(1, 0).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFill Destination:=Range(ws3.Cells(5, Selection.Column), ws3.Cells(123, Selection.Column + 1)), Type:=xlFillDefault
    
End Sub

    Because you are using an Excel Table then use it's features


    Code
    Sub AutoFill()
Dim oTbl As ListObject
Dim rCl As Range
Sheets("Forecast AC").Select
Set oTbl = ActiveSheet.ListObjects(1)
Set rCl = oTbl.HeaderRowRange.Find(What:=Range("PreviousMonth").Value, LookIn:=xlValues)
oTbl.ListColumns(rCl.Column).DataBodyRange.Select
Selection.AutoFill Destination:=Range(rCl.Offset(1), oTbl.ListColumns(rCl.Column + 1).DataBodyRange), Type:=xlFillDefault
End Sub

    A huge thanks to both of you!

    I'm sorry to bother you with basic (mostly recorded) macro like this, but for what it's worth it is really helping me improving my VBA skills seeing your solutions. Especially the point about using my Table features!

    Thanks again!

    No Problem,

    Note that the destination range for the autofill needs to start at th e column you wish to fill from, and end at the final column you wish to fill, so in the case you posted it will start in the May column (from row 5 to 123) then copy to the June column in the same row numbers.


    cheers


    Justin

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login