Re: Adding a column that has format

From: Steve L. (ssl_at_spamnosbcglobal.net)
Date: 03/13/04

  • Next message: Frank Kabel: "Re: Sorting PivotTable" 
    Date: Sat, 13 Mar 2004 21:36:00 GMT

    Ok. For the benefit of Googlers what I want to do is use a button assigned
    to a macro to insert a column before the last column with data to match the
    properties of the column that is before the last column currently. The data
    in the new column should also be part of the sum in C6:C70. This column
    will always be in that range (absolute). The Numbered heading (A/W #1, A/W
    #2, etc.) of the new columns should increment to the next available number.
    The title above the heading (wrong I know) can be blank.

    Q and A inline. Check notes for errors recieved.

    "Dave Peterson" <ec35720@msn.com> wrote in message
    news:40533FFA.42720C09@msn.com...
    >
    > I don't see how the numbered heading and adding 1 fits in.

    The column with the label A/W #1 is where I want to start adding columns
    with subsequent labels ie: A/W #2, A/W #3 etc. as needed.
    Some jobs only have few A/Ws (Additional Work) and some have as many as 80
    or so. So it would ideal to have this work dynamically.

    Check below for errors

    >
    > But this might be closer to what you want.
    >
    > Option Explicit
    > Sub testme01()
    >
    > Dim myCol As Long
    >
    > With Active***
    > myCol = .Cells(4, .Columns.Count).End(xlToLeft).Column - 1
    >
    > If myCol = 1 Then
    > MsgBox "I can't copy from the left of this column!"
    > Exit Sub
    > End If
    >
    > .Columns(myCol + 1).Insert
    > .Columns(myCol).Copy 'I get an error here "Run time
    error 1004 Application defined or object defined error"

    > .Columns(myCol + 1).PasteSpecial Paste:=xlPasteFormats, _
    > Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    >
    > .Cells(1, myCol + 1).Select
    > End With
    >
    > Application.CutCopyMode = False
    >
    > End Sub

    New columns are being added to the left of the first column instead before
    the last column.

    >
    > Notice that the myCol (I renamed it from LastCol to match the existing
    code) now
    > uses:
    > .cells(4,.columns.count).end(xltoleft).column -1
    >
    > That 4 means row 4.
    > the .columns.count means 256, the .end(xltoleft) is like going to IV4 and
    > hitting End, then left arrow.
    >
    > It'll take you to the last used cell in that row. Then I subtract to go
    back
    > one more.
    >
    > I dropped the stuff about adding 1 since I didn't see that in your page.
    >
    > If this is wrong, please take the time to describe what you want in plain
    text.
    >
    > (I don't like breaking my rules <vbg>.)
    >
    > "Steve L." wrote:
    > >
    > > Dave Peterson <ec35720@msn.com> wrote in news:40530054.648A3483@msn.com:
    > >
    > > > I don't open attachments.
    > > >
    > >
    > > OK, so I made it a regular .html page
    > > Have a look?
    > >
    > > http://www.kraftworksremodel.com/Estimate%20vs%20actual.htm
    > >
    > > Thanks Dave.
    > >
    > > Someday the light bulb will power up above my head.
    >
    > --
    >
    > Dave Peterson
    > ec35720@msn.com

  • Next message: Frank Kabel: "Re: Sorting PivotTable"
    Loading