Copy Unknown range After autofilter

[mattd4385]

I want to copy and Unknown range After autofilter.

the data is only in col A:F
row A is a header that was used to filter but I Do Not want to copy it.

 

[Trevor G]

Try this record a macro to select the first row you want and then use Ctrl + Shift + Down arrow this will give you the code that will copy anything after you filter then you can do what you want with it. Sample code below

Rows("5:5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

 

[mattd4385]

But what if the filter was empty then it will go to the last row of the sheet and copy the whole thing

 

[Trevor G]

You didn't ask for this.

Why would your filter be empty? are you using code to Filter?

If yes what is the code!

 

[mattd4385]

I am sorry i tried to take a sample of and then apply it to mine to make it easier to ask ?'s and get ans. this is the code i am trying to work with.

Dim WB(1 To 5) As String
WB(1) = "GeneratorOutages 1 .xls"
WB(2) = "BreakerOutages[1].xls"
WB(3) = "TransformerOutages 1 .xls"
WB(4) = "TransmissionOutages 1 .xls"
WB(5) = "NOPOutages[1].xls"


For i = 1 To 5
Windows(WB(i)).Activate

With ActiveSheet
'Add filter
.AutoFilterMode = False
'Filter and copy data by BFN-500
With Range("F1", Range("F" & Rows.Count).End(xlUp))
.AutoFilter 1, "*BFN-500*"
On Error Resume Next

'----SELECT THE RIGHT DATA to copy---------



Windows("NOP_Data.xls").Activate
Sheets("BFN-500").Select
'find empty the next cell in cal A and paste data
Set FirstBlankCell = Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
FirstBlankCell.Activate
ActiveSheet.Paste
End With

.AutoFilterMode = False
End With
Next

 

[mattd4385]

i am filtering outages
so their may or maynot be outages that day so the filter could range from 0 to 100's

 

[Trevor G]

Not tested but try something like this

Sub som()
Dim WB(1 To 5) As String
WB(1) = "GeneratorOutages 1 .xls"
WB(2) = "BreakerOutages[1].xls"
WB(3) = "TransformerOutages 1 .xls"
WB(4) = "TransmissionOutages 1 .xls"
WB(5) = "NOPOutages[1].xls"

For i = 1 To 5
Windows(WB(i)).Activate
With ActiveSheet
'Add filter
.AutoFilterMode = False
'Filter and copy data by BFN-500
With Range("F1", Range("F" & Rows.Count).End(xlUp))
.AutoFilter 1, "*BFN-500*"
If Range("A2") = "" Then
Next i
Else
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
On Error Resume Next
'----SELECT THE RIGHT DATA to copy---------

Windows("NOP_Data.xls").Activate
Sheets("BFN-500").Select
'find empty the next cell in cal A and paste data
Set FirstBlankCell = Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
FirstBlankCell.Activate
ActiveSheet.Paste
End With
.AutoFilterMode = False
End With
End If
Next
End Sub

 

[mattd4385]

Sorry i didn't want to be mean, the code did not work.
You used
If Range("A2") = "" Then
Next i
but the i is for the sheet name is has nothing to do with select the data

 

[Trevor G]

Isn't it refering to the workbook name based on the top part of the code?

Don't worry about it if I get it wrong as I stated I hadn't tested it.

I will work on this and post back.

 

[mattd4385]

This may be the best way to go

For i = 1 To 5
Windows(WB(i)).Activate

LR = ActiveSheet.UsedRange.Rows.Count

For n = 2 To LR
Range("F" + n).Select
If cell.Value <> "BFN-500" Then
ActiveCell.EntireRow.Copy
Windows("NOP_Data.xls").Activate
Sheets("BFN-500").Select
'find empty the next cell in cal A and paste data
Set FirstBlankCell = Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
FirstBlankCell.Activate
ActiveSheet.Paste
Windows(WB(i)).Activate
End If

Next n

Next