Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Oct 2001
    Location
    Northern VA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Copy/Paste Question (2000)

    I have a spreadsheet containing company names, stage within a lifecycle, and a $ amount for that particular stage. I've subtotaled the detail by company name and stage and have copied just this data into a new workbook so I can work only with the totals and not all the detail.

    The problem I have is that the subtotals by stage don't have the comany name associated with it. For example, since I've done subtotals within subtotals it shows up like the example in the attached file (it wouldn't come out right here).

    I want to be able to copy/paste the company name from the company total cell to the empty cells above it so I can do further analysis. I can easily get rid of the " Total" but how do I copy/paste the remaining company name to cells that are blank above it?

    I have about 1400 rows, so doing it by hand is not the way to go, esp. since I'll be doing this on a monthly basis. I am new to VBA and I can tell Excel to find the last cell in the region, but I don't know how to tell it to do the rest (I have some ideas, but don't know how to put it in VBA).

    Any suggestions/help would be greatly appreciated!!

    Thanks!
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Copy/Paste Question (2000)

    I have to do this as an example.

    Company names and blank cells located in range A2 to A10.
    Select range from A2 to A10
    Press F5 (goto)
    Click on SPECIAL...
    Select BLANKS - (this will select all of your blank cells)
    Press the DOWN Arrow once
    Press CTRL & ENTER (together).

    This will fill your range (hopefully) - you might want to experiment

    Someone will have some VBA for this answer!!

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Copy/Paste Question (2000)

    this is just something to try out. there are much better ways to do this, but this can get you started.

    <pre>Sub trythis()
    Dim sHeading As String
    Dim newHeading As String

    sHeading = Range("A5").Value
    newHeading = Left(sHeading, 9)
    Range("A3:A4").Value = newHeading

    sHeading = Range("A8").Value
    newHeading = Left(sHeading, 9)
    Range("A6:A7").Value = newHeading
    End Sub
    </pre>


  4. #4
    New Lounger
    Join Date
    Oct 2001
    Location
    Northern VA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Copy/Paste Question (2000)

    I was able to figure this out (you forgot to mention the equals sign). Unfortunately it doesn't work going down as well, however I was able to redo my subtotals so they total on the top instead of the bottom (there's a check box for that) and then using this on my new data. Piece of cake!

    Thanks very much!

Posting Permissions

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