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
    1. Sub Autofill()
    2. Sheets("Forecast").Select
    3. Cells.Find(What:=Range("PreviousMonth").Value, LookIn:=xlValues).Offset(1, 0).Range("A1").Select
    4. Range(Selection, Selection.End(xlDown)).Select
    5. Selection.AutoFill Destination:=ActiveCell.Range("A1:B101"), Type:=xlFillDefault
    6. End Sub
  • This is your problem line, you activecell cannot have a range . Do you mean ActiveSheet?


    Code
    1. 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
    1. Sub AutoFill()
    2. Dim ws3 As Worksheet: Set ws3 = Sheet12
    3. ws3.Select
    4. Cells.Find(What:=Range("PreviousMonth").Value, LookIn:=xlValues).Offset(1, 0).Range("A1").Select
    5. Range(Selection, Selection.End(xlDown)).Select
    6. Selection.AutoFill Destination:=Range(ws3.Cells(5, Selection.Column), ws3.Cells(123, Selection.Column + 1)), Type:=xlFillDefault
    7. End Sub
  • Because you are using an Excel Table then use it's features


    Code
    1. Sub AutoFill()
    2. Dim oTbl As ListObject
    3. Dim rCl As Range
    4. Sheets("Forecast AC").Select
    5. Set oTbl = ActiveSheet.ListObjects(1)
    6. Set rCl = oTbl.HeaderRowRange.Find(What:=Range("PreviousMonth").Value, LookIn:=xlValues)
    7. oTbl.ListColumns(rCl.Column).DataBodyRange.Select
    8. Selection.AutoFill Destination:=Range(rCl.Offset(1), oTbl.ListColumns(rCl.Column + 1).DataBodyRange), Type:=xlFillDefault
    9. 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