I am working on a small project which requires me to copy and paste certain columns if I detect "true" in the row. I am trying to paste these selected columns onto a different sheet and I want to paste only their values not the formulas.
This is what I have so far and I am getting an error with the paste special feature. Please help.
' CopyIfTrue()
Dim Col As Range, Cell As Excel.Range, RowCount As Integer
Dim nysheet As Worksheet
Set nysheet = Sheets.Add()
nysheet.Name = "T1"
Sheets("FemImplant").Select
RowCount = ActiveSheet.UsedRange.Rows.Count
Set Col = Range("I2:I" & RowCount) 'Substitute with the range which includes your True/False values
Dim i As Integer
i = 1
For Each Cell In Col
If Cell.Value = "True" Then
Cell.Copy
Sheets("T1").Select 'Substitute with your sheet
Range("b" & i).Select
ActiveSheet.Paste
'Get sibling cell
Sheets("FemImplant").Select
Dim thisRow As Integer
thisRow = Cell.Row
Dim siblingCell As Range
Set siblingCell = Cells(thisRow, 2)
siblingCell.Copy
Sheets("T1").Select 'Substitute with your sheet
Range("a" & i).Select
ActiveSheet.PasteSpecial Paste:=xlPasteValues
Sheets("FemImplant").Select
i = i + 1
End If
Next
PasteSpecial must be Range.PasteSpecial not ActiveSheet.PasteSpecial. They are different things and ActiveSheet.PasteSpecial does not know any parameter "Paste".
ActiveSheet.Range("a" & i).PasteSpecial Paste = xlPasteValues