Hi can anyone help with the following macro?
I am filtering Data on Sheet1 "A:Z" with headers on row 1 and then copying the visible filtered rows to the next empty rows on sheet2
This macro works but I don't want to use copy and paste and wondered if it is possible to change the lines in red to something else? where sheet2 could just equal the filtered rows from sheet1 to the next empty rows.
ie I have tried re-arranging the copy and paste lines to -
'target.Range("A" & lRow & ":Z" & lRow) = copyRng.SpecialCells(xlCellTypeVisible).Value'
Here is the code I have, any help would be much appreciated
regards
pwill
I am filtering Data on Sheet1 "A:Z" with headers on row 1 and then copying the visible filtered rows to the next empty rows on sheet2
"A:Z"
This macro works but I don't want to use copy and paste and wondered if it is possible to change the lines in red to something else? where sheet2 could just equal the filtered rows from sheet1 to the next empty rows.
ie I have tried re-arranging the copy and paste lines to -
'target.Range("A" & lRow & ":Z" & lRow) = copyRng.SpecialCells(xlCellTypeVisible).Value'
Here is the code I have, any help would be much appreciated
Code:
[FONT=Verdana]Sub Filter()
[/FONT]
[FONT=Verdana]Dim source As Worksheet
Dim target As Worksheet
Dim filtrRng As Range
Dim copyRng As Range
Dim lRow As Long
[/FONT]
[FONT=Verdana] Set source = Sheet1
Set target = Sheet2
source.AutoFilterMode = False
[/FONT]
[FONT=Verdana] lRow = source.Cells(Rows.Count, "A").End(xlUp).Row
Set filtrRng = source.Range("A1:Z" & lRow)
Set copyRng = source.Range("A2:Z" & lRow)
filtrRng.AutoFilter field:=2, Criteria1:="3"
[COLOR=#ff0000]copyRng.SpecialCells(xlCellTypeVisible).Copy[/COLOR]
lRow = target.Range("A" & Rows.Count).End(xlUp).Row + 1
[COLOR=#ff0000] target.Range("A" & lRow & ":Z" & lRow).PasteSpecial xlPasteValues[/COLOR]
[COLOR=#008000]'target.Range("A" & lRow & ":Z" & lRow) = copyRng.SpecialCells(xlCellTypeVisible).Value[/COLOR]
[/FONT]
[FONT=Verdana]End Sub[/FONT]regards
pwill
Last edited:
it was giving me a headache anyway lol
