Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 14th, 2008, 02:37 PM
Registered User
 
Join Date: Mar 2008
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Macro to Autofill headings

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



 
Old March 14th, 2008, 09:28 PM
Authorized User
 
Join Date: Nov 2007
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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.
 
Old March 18th, 2008, 08:35 AM
Registered User
 
Join Date: Mar 2008
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old March 18th, 2008, 02:42 PM
Authorized User
 
Join Date: Nov 2007
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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.
 
Old March 18th, 2008, 03:35 PM
Registered User
 
Join Date: Mar 2008
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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







Similar Threads
Thread Thread Starter Forum Replies Last Post
calling to xlam macro from macro inside xlsb SteveB Excel VBA 0 June 30th, 2008 06:43 PM
Macro autofill EricB123 Excel VBA 3 February 23rd, 2007 03:55 PM
Autofill text from an Email Link zanna5910 Access 1 September 28th, 2005 07:13 PM
Autofill in unknown number of rows ashu_gupta75 Excel VBA 2 August 9th, 2004 06:09 AM
Combobox Autofill jaucourt VB.NET 2002/2003 Basics 1 March 15th, 2004 12:29 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.