VBA : Modifying autofill destination in code

bearcub · Feb 11th 2004

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

Anonymous · Feb 11th 2004

Dim s as worksheet
Set s=Activesheet

bearcub · Feb 11th 2004

Thanks

I forgot that Activesheet is an object and that Set keyword.

bearcub · Feb 11th 2004

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

bearcub · Feb 11th 2004

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

Anonymous · Feb 12th 2004

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

Anonymous · Feb 12th 2004

In a U2U bearcub asked if I would explain the code, so here is an annotated version.

Code

Sub MoveColumnstoLeftTwo()
    Dim n As Long
    'n will be the last row number used in the range starting with D3
    'before finding a blank, assuming D3 and D4 both have entries
    'the :E3 in the original was superflous
    n = Range("D3").End(xlDown).Row
    'Moves the range two columns to the right for unknown reasons
    'the value for n found above limits the end of the data being moved
    'if they were not moved, then Range("G3:G" & n) below would be Range("E3:E" & n)
    Range("D3:E" & n).Insert Shift:=xlToRight
    'The formula in the Dec CMA Bookings sheet in cell G3 is copied
    'into each cell in the activesheet, starting with g3 and down through row n
    'any relative references are adjusted in the usual way
    Sheets("Dec CMA Bookings").Range("G3").Copy Range("G3:G" & n)
    'Turns off the "marching ants" around the copied cell and clears the clipboard
    Application.CutCopyMode = False
End Sub

Display More

VBA : Modifying autofill destination in code

Participate now!

Share