VBA-Help Filter data and then copy to destiantion sheet

[Isabella]

Hi, i am after a code that will

1. Clear (formats) in the destination sheet then

2. Apply filter in sheet Source Row 5 and filter for "Y" then

3. Copy filter data to Sheet Dest

So from this

Excel Workbook
	A	B	C	D	E
5	Bank	Amount	True Adj	Y/N	Comments
6	CITI	2.14		Y
7	JP	0.01		Y
8	RBS	12.36		Y
9	BONY	0.08		N
10	NCS	0.02		Y
11	STATE	20.36		Y
12	CITI	0.39		Y
13
14
Source

To this

Excel Workbook
	A	B	C	D
5	Bank	Amount	True Adj	Y/N
6	CITI	2.14		Y
7	JP	0.01		Y
8	RBS	12.36		Y
9	NCS	0.02		Y
10	STATE	20.36		Y
11	CITI	0.39		Y
12
Dest

 

[Tom Urtis]

Sub Test1()
Application.ScreenUpdating = False
With Worksheets("Dest")
Dim rng As Range
Set rng = Range("D6:D" & Cells(Rows.Count, 4).End(xlUp).Row)
.Cells.ClearFormats
Rows(5).Copy .Rows(5)
rng.Replace What:="N", Replacement:="", LookAt:=xlWhole, MatchCase:=False
On Error Resume Next
rng.SpecialCells(4).EntireRow.Hidden = True
rng.SpecialCells(12).EntireRow.Copy .Range("A6")
Err.Clear
Cells.EntireRow.Hidden = False
rng.Replace What:="", Replacement:="N"
End With
Set rng = Nothing
Application.ScreenUpdating = True
End Sub

 

[Isabella]

I cant see in the code Sheet Source? How is the macro copying the data to Sheet Dest?

Sub Test1()
Application.ScreenUpdating = False
With Worksheets("Dest")
Dim rng As Range
Set rng = Range("D6:D" & Cells(Rows.Count, 4).End(xlUp).Row)
.Cells.ClearFormats
Rows(5).Copy .Rows(5)
rng.Replace What:="N", Replacement:="", LookAt:=xlWhole, MatchCase:=False
On Error Resume Next
rng.SpecialCells(4).EntireRow.Hidden = True
rng.SpecialCells(12).EntireRow.Copy .Range("A6")
Err.Clear
Cells.EntireRow.Hidden = False
rng.Replace What:="", Replacement:="N"
End With
Set rng = Nothing
Application.ScreenUpdating = True
End Sub

 

[Tom Urtis]

I assumed you had the Source sheet active at the time of macro execution.

This will cover you as long as the workbook is the active workbook, and assuming your source sheet is named Source, and your destination sheet is named Dest, as you depicted in your first post.

Sub Test2()
Application.ScreenUpdating = False
Dim rng As Range, srcSheet As Worksheet
Set srcSheet = Sheets("Source")
With Worksheets("Dest")
Set rng = srcSheet.Range("D6:D" & srcSheet.Cells(Rows.Count, 4).End(xlUp).Row)
.Cells.ClearFormats
srcSheet.Rows(5).Copy .Rows(5)
rng.Replace What:="N", Replacement:="", LookAt:=xlWhole, MatchCase:=False
On Error Resume Next
rng.SpecialCells(4).EntireRow.Hidden = True
rng.SpecialCells(12).EntireRow.Copy .Range("A6")
Err.Clear
srcSheet.Cells.EntireRow.Hidden = False
rng.Replace What:="", Replacement:="N"
End With
Set rng = Nothing: Set srcSheet = Nothing
Application.ScreenUpdating = True
End Sub