Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy Value Only and Paste (Excel 2000)

    Afternoon All,

    I would like to know if someone could help with the following:

    In Column D there MAY or MAY NOT be a value. I would like to know in vba, how to determine all Values in the column, copy it, and then paste it to another worksheet.

    For instance wksheet1:
    colD, row2=2
    colD, row3=
    colD, row4=4
    colD, row=

    How I perceive it to work is, the code would find the 2 and 4 only (no null values or blank rows (however I have the code to remove blanks if need be)), copy those values, and paste them to say wksheet2

    Hope that was clear and someone can offer suggestions.

    Thanks in advance.....
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Copy Value Only and Paste (Excel 2000)

    Roberta, are the values always constants, or formulas, or both? When you copy to the target worksheet, do you want to copy the non-data cells/rows to be empty cells, or do you want to remove the non-data cells/rows so that data become contiguous?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Value Only and Paste (Excel 2000)

    John,

    Thanks for the reply and yes the values are always constants and NO I don't want to copy non-data cell/rows ... I want the data to be contiguous.

    Roberta
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Copy Value Only and Paste (Excel 2000)

    This should do it; adjust the Copy source and the Paste target with whatever is appropriate:

    Sub Macro2()
    Dim rngValueCells As Range
    On Error Resume Next
    ' adjust copy source after next line
    Set rngValueCells = _
    ThisWorkbook.Worksheets("Sheet1").Range("D").Speci alCells(xlCellTypeConstants, 21)
    If rngValueCells Is Nothing Then
    MsgBox "Nothing Found", vbInformation
    Else
    rngValueCells.Copy
    ' adjust paste target in next line
    ThisWorkbook.Worksheets("Sheet2").Range("A1").Past eSpecial Paste:=xlValues
    Application.CutCopyMode = False
    End If
    Set rngValueCells = Nothing
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Value Only and Paste (Excel 2000)

    John,

    I just got back from lunch and pasted and edited your code.....it returns "nothing found"...as it should if there weren't any values in column "D"....however every cell in column "D" has a value in except for 5....(a total of 213 rows).....any suggestions?

    Thanks again
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Copy Value Only and Paste (Excel 2000)

    Oops, sorry, right away I see that I made an editing error which I thought I had corrected. Correction in red:

    Set rngValueCells = _
    ThisWorkbook.Worksheets("Sheet1").<font color=red>Columns</font color=red>("D").SpecialCells(xlCellTypeConstants, 21)
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Value Only and Paste (Excel 2000)

    John,

    Never Mind...I got it....duh... it's Friday....I need a drink..LOL

    Thanks for your help...have a Happy Easter
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Copy Value Only and Paste (Excel 2000)

    But you just had lunch! Here's mine:
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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