Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA for copy and paste (Excel 2000)

    I am trying to copy and paste down the spreadsheet. The customer name is only on every third row or so. I need the VBA code that will copy data from the first occupied cell in Column A and paste down to the next occupied cell. I need this copy command repeated for every new customer that occupies Column A. Help! Thank you!

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA for copy and paste (Excel 2000)

    Can you post a simplified example of what you are trying to do?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA for copy and paste (Excel 2000)

    If I understand correctly, I think this will do what you want. Copy code to VB.
    Select the range of interest and run the macro
    It will fill the blank areas of the region with the value of the cell above it

    So if you have
    a
    (blank)
    b
    (Blank)
    (Blank)
    c
    (Blank)
    (Blank)
    d

    You will get
    a
    a
    b
    b
    b
    c
    c
    c
    d

    Steve

    Sub FillBlanksFromAbove()
    Dim temp As String
    rng = Selection.Address

    ActiveCell.Range("A2").FormulaR1C1 = "=+R[-1]C"
    ActiveCell.Range("a2").Copy
    Range(rng).SpecialCells(xlCellTypeBlanks).Select
    ActiveSheet.Paste
    Range(rng).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues
    End Sub

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA for copy and paste (Excel 2000)

    Hi Cindy,

    As I mentioned in my previous post, would like to see a stripped down version of your workbook because...

    ...invariably, cells to the right of column A probably contain information relating to the customer names in column A
    ...you mention "new customers" in your original post...are these unique customers listed already in column A, or are they "new" customers related to the information in columns B, C, D, etc i.e. a new customer for the next order, etc?
    ...your statement "referring to every three rows or so" indicates that there is data to the right of column A, therefore what happens to the data when you paste a "new" or existing customer into column A?

    Without a stripped down example with more specifics, I am not willing to make code based on assumptions.

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

    Re: VBA for copy and paste (Excel 2000)

    See if the routine below will do what you want:

    <pre>Sub FillBlanksFromAbove()
    Dim I As Long
    Dim oLast As Range, oBlank As Range
    Set oLast = ActiveSheet.Range("A65536").End(xlUp)
    For I = 0 To oLast.Row - 1
    If ActiveSheet.Range("A1").Offset(I, 0).Value <> "" Then Exit For
    Next I
    Set oBlank = ActiveSheet.Range("A" & I + 2 & ":" & oLast.Address).SpecialCells(xlCellTypeBlanks)
    oBlank.FormulaR1C1 = "=+R[-1]C"
    End Sub
    </pre>

    Legare Coleman

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

    Re: VBA for copy and paste (Excel 2000)

    Yes, now that I see your workbook I see where a couple of changes are necessary. Try the code below:

    <pre>Sub FillBlanksFromAbove()
    Dim I As Long, oLast As Long
    Dim oBlank As Range
    oLast = ActiveSheet.Range("H65536").End(xlUp).Row
    For I = 1 To oLast - 1
    If ActiveSheet.Range("A1").Offset(I, 0).Value <> "" Then Exit For
    Next I
    Set oBlank = ActiveSheet.Range("A" & I + 2 & ":A" & oLast).SpecialCells(xlCellTypeBlanks)
    oBlank.FormulaR1C1 = "=+R[-1]C"
    End Sub
    </pre>

    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA for copy and paste (Excel 2000)

    Post deleted by cindypositano

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

    Re: VBA for copy and paste (Excel 2000)

    What was the error?
    Legare Coleman

  9. #9
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA for copy and paste (Excel 2000)

    Post deleted by cindypositano

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

    Re: VBA for copy and paste (Excel 2000)

    I copied that line of code from your message and pasted it back into the macro that I tested, and I did not get an error. It is possible that some other line is causing the problem. Could you upload the workbook with the code?
    Legare Coleman

  11. #11
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA for copy and paste (Excel 2000)

    Post deleted by cindypositano

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

    Re: VBA for copy and paste (Excel 2000)

    It looks like the Less Than sign in that line was converted to the HTML pseudo code when you copied the code from the message. I have attached the workbook you sent with that corrected.
    Attached Files Attached Files
    Legare Coleman

  13. #13
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA for copy and paste (Excel 2000)

    That was it! I need new glasses! Thank you. This project is now complete and ready for the next phase. I really have appreciated your help. Thanks to you I can keep my job!

  14. #14
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA for copy and paste (Excel 2000)

    Mike, I made my deadline for today on this phase of the project thanks to you. I appreciated your help.

  15. #15
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA for copy and paste (Excel 2000)

    Glad I could be of help.

Page 1 of 2 12 LastLast

Posting Permissions

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