VBA : Modifying autofill destination in code

  • I need to create a variable for the Activesheet.


    How do I do this?


    I want to be able to go back to the original active sheet later in code. I am copying information from a second sheet onto the first sheet.


    Thanks

  • I have the following code for the exercise:


    Sub MoveColumnstoLeftTwo()


    Dim act As Worksheet
    Set act = ActiveSheet
    act.Select
    Range("D3:E3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Insert Shift:=xlToRight
    Sheets("Dec CMA Bookings").Select
    Range("G3").Copy
    act.Select
    Range("G3").Select
    act.Paste
    Selection.AutoFill Destination:=Range(xlDown)
    Application.CutCopyMode = False


    End Sub



    Where I am having a problem is in the last AutoFill Destination line. I don't know what to do so that it will just copy the follow to the blank cells in the current region. I received an error message the last time. I had the code "Range(Selection, Selection.End(xlDown))" but it continued too far.


    How do I modify the code?


    Thanks

  • I have the following code for the exercise:


    Sub MoveColumnstoLeftTwo()


    Dim act As Worksheet
    Set act = ActiveSheet
    act.Select
    Range("D3:E3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Insert Shift:=xlToRight
    Sheets("Dec CMA Bookings").Select
    Range("G3").Copy
    act.Select
    Range("G3").Select
    act.Paste
    Selection.AutoFill Destination:=Range(xlDown)
    Application.CutCopyMode = False


    End Sub



    Where I am having a problem is in the last AutoFill Destination line. I don't know what to do so that it will just copy the follow to the blank cells in the current region. I received an error message the last time. I had the code "Range(Selection, Selection.End(xlDown))" but it continued too far.


    How do I modify the code?


    Thanks

  • Try the following. Notice that I eliminated the unnecessary selects.

    Code
    Sub MoveColumnstoLeftTwo()
        Dim n As Long
        n = Range("D3:E3").End(xlDown).Row
        Range("D3:E" & n).Insert Shift:=xlToRight
        Sheets("Dec CMA Bookings").Range("G3").Copy Range("G3:G" & n)
        Application.CutCopyMode = False
    End Sub
  • In a U2U bearcub asked if I would explain the code, so here is an annotated version.

Participate now!

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