Copy filtered data and paste values

Solved/Closed
jan - Apr 7, 2010 at 08:29 PM
 jan - Apr 7, 2010 at 11:11 PM
Hi, I found this code in this forum.
It selects data based on the filtered rows of data and then it pastes it in another sheet based under the name of the filtered data that was selected. I need to only copy the values though because some of the areas are highlighted with different colors and formats and I want them to remain the same. I am not very good at this but I have tried everything and I can't seem to make just simple code like "ActiveSheet.PasteSpecial xlValues" work or else I am putting it in the wrong place.



Sub pasterows()
Dim NewRow As Integer
NewRow = Worksheets("LISTS").Range("Z3").Value + 1

Dim strsearch As String, lastline As Integer, tocopy As Integer

'strsearch = CStr(InputBox("enter the string to search for"))
strsearch = CStr(UserForm2.CatName.Value)
lastline = Range("L65536").End(xlUp).Row

'NewRow = 1

For i = 1 To lastline
For Each c In Range("B" & i & ":Z" & i)
If c.Text = strsearch Then
tocopy = 1
End If
Next c
If tocopy = 1 Then

Rows(i).Copy Destination:=Worksheets("Itemized Costs").Cells(NewRow, 1)
NewRow = NewRow + 1


End If
tocopy = 0
Next i
MsgBox "The data has been successfully copied."

End Sub

1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 7, 2010 at 09:43 PM
see if this helps

Change this line
Rows(i).Copy Destination:=Worksheets("Itemized Costs").Cells(NewRow, 1)

to

Sheets("Itemized Costs").Range(NewRow & ":" & NewRow) = Range(i & ":" & i).Value
0
Thank you so much! I would have never gotten that. It works great. I've been working on this for a week and you solved it in minutes!
0