Page 1 of 2 12 LastLast
Results 1 to 10 of 13

paste special in vba

This is a discussion on paste special in vba within the Excel Questions forums, part of the Question Forums category; Hi all What I have is Selection.SpecialCells(xlCellTypeVisible).Select Selection.Copy I would also like to paste values only is this possible the ...

  1. #1
    Board Regular
    Join Date
    Aug 2002
    Posts
    197

    Default paste special in vba

    Hi all

    What I have is Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    I would also like to paste values only is this possible the full code is below

    Many thanks in advance





    Sub CopySheet2ToSheet5555()
    Dim wbBook As Workbook
    Dim wsSheet2 As Worksheet, wsSheet5 As Worksheet
    Dim rnSource As Range, rnTarget As Range
    Dim r As Long, x As Long

    Set wbBook = ThisWorkbook
    Set wsSheet2 = wbBook.Worksheets("parts dimensions")
    Set wsSheet5 = wbBook.Worksheets("data collection1")
    Sheets("Parts dimensions").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Set rnSource = wsSheet2.Range("2:500")
    r = rnSource.Rows.Count

    For x = 1 To r
    If Application.CountA(rnSource.Rows(x)) <> 0 Then
    Set rnTarget = wsSheet5.Range("a65536").End(xlUp).Offset(1, 0)
    rnSource.Rows(x).Copy rnTarget

    Sheets("Cabinets").Select


    End If
    Next
    End Sub

  2. #2
    Board Regular Todd Bardoni's Avatar
    Join Date
    Aug 2002
    Location
    Rochester, MI USA
    Posts
    3,044

    Default Re: paste special in vba

    Run the macro recorder, copy something, pastespecial values, then stop the recorder and look at your code. This will give you the syntax.

    Edit: Oh, wait. That may not work. Its multiple selections, right?
    Todd


    "I'm a Data Anlyst(intermediate) and have no idea what it's all about."

  3. #3
    Board Regular Todd Bardoni's Avatar
    Join Date
    Aug 2002
    Location
    Rochester, MI USA
    Posts
    3,044

    Default Re: paste special in vba

    How about:

    Selection.Formula = Selection.Value
    Todd


    "I'm a Data Anlyst(intermediate) and have no idea what it's all about."

  4. #4
    Board Regular
    Join Date
    Aug 2002
    Posts
    197

    Default Re: paste special in vba

    Hi tbardoni

    Thanks for your reply I tried it works but fills the rest of the sheet
    with

    #N/A #N/A #N/A #N/A
    #N/A #N/A #N/A #N/A
    #N/A #N/A #N/A #N/A
    #N/A #N/A #N/A #N/A

  5. #5
    Board Regular Todd Bardoni's Avatar
    Join Date
    Aug 2002
    Location
    Rochester, MI USA
    Posts
    3,044

    Default Re: paste special in vba

    I'm not quite sure where the offending piece of code that's doing this is but, here's a temporary solution:

    Cells.Replace What:="#N/A", Replacement:=""
    Todd


    "I'm a Data Anlyst(intermediate) and have no idea what it's all about."

  6. #6
    Board Regular
    Join Date
    Aug 2002
    Posts
    197

    Default Re: paste special in vba

    Hi tbardoni

    I have tried this

    Sub CopySheet2ToSheet5555()
    Dim wbBook As Workbook
    Dim wsSheet2 As Worksheet, wsSheet5 As Worksheet
    Dim rnSource As Range, rnTarget As Range
    Dim r As Long, x As Long

    Set wbBook = ThisWorkbook
    Set wsSheet2 = wbBook.Worksheets("parts dimensions")
    Set wsSheet5 = wbBook.Worksheets("data collection1")
    Sheets("Parts dimensions").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.SpecialCells(xlValue).Select
    Selection.Copy
    Set rnSource = wsSheet2.Range("2:500")
    r = rnSource.Rows.Count

    For x = 1 To r
    If Application.CountA(rnSource.Rows(x)) <> 0 Then
    Set rnTarget = wsSheet5.Range("a65536").End(xlUp).Offset(1, 0)
    rnSource.Rows(x).Copy rnTarget

    Sheets("Cabinets").Select


    End If
    Next
    End Sub


    It runs to Selection.Copy

    The error is 1004 That command cannot be used in multiple selections ?

  7. #7
    Board Regular Todd Bardoni's Avatar
    Join Date
    Aug 2002
    Location
    Rochester, MI USA
    Posts
    3,044

    Default Re: paste special in vba

    Replace Selection.Copy with this:

    Selection.Formula = Selection.Value
    Todd


    "I'm a Data Anlyst(intermediate) and have no idea what it's all about."

  8. #8
    Board Regular
    Join Date
    Aug 2002
    Posts
    197

    Default Re: paste special in vba

    Hi tbardoni

    Still returning them #n/a

    Many thanks for your efforts

  9. #9
    Board Regular Todd Bardoni's Avatar
    Join Date
    Aug 2002
    Location
    Rochester, MI USA
    Posts
    3,044

    Default Re: paste special in vba

    Did you do this:

    Selection.Formula = Selection.Value
    Cells.Replace What:="#N/A", Replacement:=""

    This should fix it. Let me know.
    Todd


    "I'm a Data Anlyst(intermediate) and have no idea what it's all about."

  10. #10
    Board Regular
    Join Date
    Aug 2002
    Posts
    197

    Default Re: paste special in vba

    Hi tbardoni

    This is what I run The hour glass stays on and I had to end the program


    Sub CopySheet2ToSheet5555()
    Dim wbBook As Workbook
    Dim wsSheet2 As Worksheet, wsSheet5 As Worksheet
    Dim rnSource As Range, rnTarget As Range
    Dim r As Long, x As Long

    Set wbBook = ThisWorkbook
    Set wsSheet2 = wbBook.Worksheets("parts dimensions")
    Set wsSheet5 = wbBook.Worksheets("data collection1")
    Sheets("Parts dimensions").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Formula = Selection.Value
    Cells.Replace What:="#N/A", Replacement:=""

    Set rnSource = wsSheet2.Range("2:500")
    r = rnSource.Rows.Count

    For x = 1 To r
    If Application.CountA(rnSource.Rows(x)) <> 0 Then
    Set rnTarget = wsSheet5.Range("a65536").End(xlUp).Offset(1, 0)
    rnSource.Rows(x).Copy rnTarget

    Sheets("Cabinets").Select


    End If
    Next
    End Sub

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com