paste special in vba

cgriff

Board Regular
Joined
Aug 26, 2002
Messages
201
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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?
 
Upvote 0
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
 
Upvote 0
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:=""
 
Upvote 0
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 ?
 
Upvote 0
Did you do this:

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

This should fix it. Let me know.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top