Hi everyone!!

I hope you can help. I have a spreadsheet with column headings in Row 6 and a named range for the totals column named: Total.

I created a macro to insert a column before my Total column. I want it to select the headings in Row 6 and autofill them to the new column I inserted before Totals.

It inserts the column correctly and selects the headings that I want it to, but it stops on the autofill line.


I am receiving the error: Run Time error '424' Object required

Has anyone seen this before?

Thanks,
Sarah

Sub InsertColumn()

'Selects the Total column and inserts a column to the left
Range("Total").Select
Selection.Insert Shift:=xlToRight

Dim ColumnNum As Integer

ColumnNum = ActiveSheet.Range("Total").Column

Range(Cells(6, ColumnNum - 7), Cells(6, ColumnNum - 2)).Select
The following is where the error is occurring
Selection.AutoFill Destination:=CurrentSheet.Range(Cells(6, ColumnNum - 1)), Type:=xlFillDefault
End Sub

sarahb,

If "Total" is in cell K6, and I run your macro, it inserts a column at K.

It then selects the title cells E6:J6.

What do you want to do with this range of selected cells?


Have a great day,
Stan

stanleydgromjr

Windows Vista Business and Excel 2003, 2007.

I am trying to autofill the highlighted labels to the new empty column.

sarahb,

Here you go.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

[code]

Option Explicit
Sub InsertColumn()
    Dim lngColumnNum As Long
    Range("Total").EntireColumn.Insert
    lngColumnNum = ActiveSheet.Range("Total").Column
    Range(Cells(6, lngColumnNum - 7), Cells(6, lngColumnNum - 2)).Copy
    With Range("K7")
        .PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    End With
    Range("K6").Select
    Application.CutCopyMode = False
End Sub

[/code

Then run the "InsertColumn" macro.


Have a great day,
Stan

stanleydgromjr

Windows Vista Business and Excel 2003, 2007.

Hi Stan,
Thanks so much for your input. It didn't quite do what I wanted to, but your code was a good start.

I recorded a macro using Fill from the Edit Menu and it gave me just the information I needed to get it to fill correctly. I changed the cell references so that it is not pointing at a specific cell.

Here is the finished code:
Sub InsertColumn()
'This macro highlights the named range: "Total", inserts a column before it, and fills the column headings over to the new
'column
    Dim lngColumnNum As Long 'The column index number of the named range: "Total"

    Range("Total").EntireColumn.Insert
    lngColumnNum = ActiveSheet.Range("Total").Column
    Range(Cells(6, lngColumnNum - 2), Cells(6, lngColumnNum - 1)).Select
    Selection.DataSeries Rowcol:=xlRows, Type:=xlChronological, Date:=xlMonth, Step:=1, Trend:=False
    ActiveSheet.Range(Cells(6, lngColumnNum - 1), Cells(6, lngColumnNum - 1)).Select

End Sub

Thanks again,
Sarah :D