Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert Column (Office XP)

    Hi Hans and all,

    I would like to insert a first column with heading call Area Office for 20 worksheets at one go. Pls advise how can I do it?

    thanks always, kun

  2. #2
    Star Lounger
    Join Date
    Apr 2003
    Location
    Morgantown, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Column (Office XP)

    Select all the worksheet tabs.

    Right-click on Column A.

    Insert a column.

    Type in your column heading, which will go on all 20 worksheets because they all are still selected.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert Column (Office XP)

    Hi Kun,
    Bob is correct...just right click on the first worksheet tab and choose Select All Sheets. This groups ALL the sheets...and what you do on one sheet drills down for all other sheets!
    Regards,
    Rudi

  4. #4
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Column (Office XP)

    Hi Rudi and Bob,

    thank for the fast response. this definitely will insert the column all at once. However, since I don't want to interupt the program running its functions to insert these columns, I would prefer to have code write into it as I can incoporate this into the program that I am writing.

    Appreciate if you can advise.

    thanks, kun

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert Column (Office XP)

    You can use code (within a macro, procedure or function) such as

    Dim wsh As Worksheet
    For Each wsh In ActiveWorkbook.Worksheets
    wsh.Columns(1).Insert
    wsh.Range("A1") = "Area Office"
    Next wsh

  6. #6
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Column (Office XP)

    Hi, Kun,
    If you do go with Bob & Rudi's approach, don't forget to ungroup the sheets or anuyhting else you put in that column will also be replicated!
    I speak ruefully...

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Column (Office XP)

    How do you control where the column is inserted? at the beginning, middle, or end of the worksheet?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert Column (Office XP)

    The Insert method, when applied to a range consisting of entire columns, pushes that range to the right. For example, to insert a new column between C and D:

    Range("D").Insert

    The new column becomes column D, the original column D becomes column E etc.

    To insert 3 columns between E and F:

    Range("F:H").Insert

    The new columns become column F, G and H. The original column F becomes column I etc.

  9. #9
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Column (Office XP)

    Does that mean, then, that one cannot insert a new column at the far left? (to the left of A). I should have formed my question better as my real need is inserting a new column to the far left.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Column (Office XP)

    No, the following will insert a column to the left of A.

    <code>
    Range("A:A").Insert
    </code>

    You can not insert a column to the right of the rightmost column (Column IV).
    Legare Coleman

  11. #11
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Column (Office XP)

    Aaaargh! Thank you. That is precisely what Hans wrote yet I understood the response to mean the column was inserted to the right.

    Thank you for clearing it up (or restating what was already clear to any able minded person).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •