Copy filtered data and paste values

[Solved/Closed]
Report
-
 jan -
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 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
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!