Copy filtered data to another sheet including visible and invisible data in excel VBA

[Indie126]

Hello everyone,

I've been trying for days to copy filtered data from one sheet to another. My code works fine but I am only getting the visible rows copied to the new sheet. The hidden columns contain information I need n the ew sheet.

Can anyone help me?

Here is my code:

Sub RequestApproval()
Dim lr As Long, lr2 As Long, lr3 As Long, lr4 As Long, lr5 As Long, r As Long
lr = Sheets("Intake Plan").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("Brian").Cells(Rows.Count, "A").End(xlUp).Row
lr3 = Sheets("Wes").Cells(Rows.Count, "A").End(xlUp).Row
lr4 = Sheets("Dustin").Cells(Rows.Count, "A").End(xlUp).Row
lr5 = Sheets("Brandon").Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
Select Case Range("AJ" & r).Value
Case Is = "Yes-Brian"
Rows(r).Copy Destination:=Sheets("Brian").Range("A" & lr2 + 1)
lr2 = Sheets("Brian").Cells(Rows.Count, "A").End(xlUp).Row
Rows(r).Delete
Case Is = "Yes-Wes"
Rows(r).Copy Destination:=Sheets("Wes").Range("A" & lr3 + 1)
lr3 = Sheets("Wes").Cells(Rows.Count, "A").End(xlUp).Row
Rows(r).Delete
Case Is = "Yes-Dustin"
Rows(r).Copy Destination:=Sheets("Dustin").Range("A" & lr4 + 1)
lr4 = Sheets("Dustin").Cells(Rows.Count, "A").End(xlUp).Row
Rows(r).Delete
Case Is = "Yes-Brandon"
Rows(r).Copy Destination:=Sheets("Brandon").Range("A" & lr5 + 1)
lr5 = Sheets("Brandon").Cells(Rows.Count, "A").End(xlUp).Row
Rows(r).Delete
End Select
Next r
End Sub

 

[jondavis1987]

Put this in at the beginning in the row after you you dim. Change the range to the rows you need

    Rows("14:14").Select
    Selection.EntireRow.Hidden = False

Put this in before end sub

    Rows("14:14").Select
    Selection.EntireRow.Hidden = True

 

[jondavis1987]

[COLOR=#333333]Sub RequestApproval()[/COLOR]
[COLOR=#333333]Dim lr As Long, lr2 As Long, lr3 As Long, lr4 As Long, lr5 As Long, r As Long[/COLOR]
Rows("14:14").Select  ****CHANGE THIS RANGE****
Selection.EntireRow.Hidden = False
[COLOR=#333333]lr = Sheets("Intake Plan").Cells(Rows.Count, "A").End(xlUp).Row[/COLOR]
[COLOR=#333333]lr2 = Sheets("Brian").Cells(Rows.Count, "A").End(xlUp).Row[/COLOR]
[COLOR=#333333]lr3 = Sheets("Wes").Cells(Rows.Count, "A").End(xlUp).Row[/COLOR]
[COLOR=#333333]lr4 = Sheets("Dustin").Cells(Rows.Count, "A").End(xlUp).Row[/COLOR]
[COLOR=#333333]lr5 = Sheets("Brandon").Cells(Rows.Count, "A").End(xlUp).Row[/COLOR]
[COLOR=#333333]For r = lr To 2 Step -1[/COLOR]
[COLOR=#333333]Select Case Range("AJ" & r).Value[/COLOR]
[COLOR=#333333]Case Is = "Yes-Brian"[/COLOR]
[COLOR=#333333]Rows(r).Copy Destination:=Sheets("Brian").Range("A" & lr2 + 1)[/COLOR]
[COLOR=#333333]lr2 = Sheets("Brian").Cells(Rows.Count, "A").End(xlUp).Row[/COLOR]
[COLOR=#333333]Rows(r).Delete[/COLOR]
[COLOR=#333333]Case Is = "Yes-Wes"[/COLOR]
[COLOR=#333333]Rows(r).Copy Destination:=Sheets("Wes").Range("A" & lr3 + 1)[/COLOR]
[COLOR=#333333]lr3 = Sheets("Wes").Cells(Rows.Count, "A").End(xlUp).Row[/COLOR]
[COLOR=#333333]Rows(r).Delete[/COLOR]
[COLOR=#333333]Case Is = "Yes-Dustin"[/COLOR]
[COLOR=#333333]Rows(r).Copy Destination:=Sheets("Dustin").Range("A" & lr4 + 1)[/COLOR]
[COLOR=#333333]lr4 = Sheets("Dustin").Cells(Rows.Count, "A").End(xlUp).Row[/COLOR]
[COLOR=#333333]Rows(r).Delete[/COLOR]
[COLOR=#333333]Case Is = "Yes-Brandon"[/COLOR]
[COLOR=#333333]Rows(r).Copy Destination:=Sheets("Brandon").Range("A" & lr5 + 1)[/COLOR]
[COLOR=#333333]lr5 = Sheets("Brandon").Cells(Rows.Count, "A").End(xlUp).Row[/COLOR]
[COLOR=#333333]Rows(r).Delete[/COLOR]
[COLOR=#333333]End Select[/COLOR]
[COLOR=#333333]Next r[/COLOR]
Rows("14:14").Select  *****CHANGE THIS RANGE*****
    Selection.EntireRow.Hidden = True
[COLOR=#333333]End Sub[/COLOR]