paste special in vba

[cgriff]

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

 

[LTunnicliffe]

Do you simply want to copy the contents of one sheet and paste it on the other sheet? Do you want to do this on if a condition exists? What is the condition in words?

LT

 

[cgriff]

Hi LTunnicliffe


What I have is multiple selections

I want special cells, xltype hidden, and also values only

is this possible

Many thanks

 

[LTunnicliffe]

I'm not sure what "special cells" are but you can copy all of the formats and only values by doing something like this:

Sub CopyValuesFormats()
  Worksheets("Sheet1").Cells.Copy
  Worksheets("Sheet2").Cells.PasteSpecial xlPasteValues
  Worksheets("Sheet2").Cells.PasteSpecial xlPasteFormats
End Sub

If you want to want to copy only a certain range off of sheet1 change "Cells" to what you want. If you want to paste it in a different place then again change "Cells" to where you want the copied data to go.

Hope this helps!