VBA Select a Variable Range of Cells

[Puppies72]

Hi all,

I use a basic bit of code to set certain autofilter values in a worksheet.
Once that is done I generally end up with between 20 and 200 rows of visible data out of a total of 10,000 rows - how can I set VBA to copy and paste the values from the visible cells and insert them between existing data in different worksheet?

thanks in advance

 

[radiant0619]

attach the excel with sample data

Regards
Radiant

 

[Rick Rothstein]

Insert them between existing data?

I think you are going to have to provide a more detailed description of what you have datawise and exactly where you are attempting to place the filtered results?

 

[villy]

I think you can use the Hidden Property and play with it but if you can provide with a detailed explanation it is better.
Thanks

 

[Puppies72]

Hi all,

What I was trying to do was avoid excel searching through 10,000 cells for data matches by autofiltering and then copying out the data I needed. I can't get that to work so went back to the drawing board and now have this cobbled together from some other macros

Sub macro8()
Dim LR As Long, i As Long
With Sheets("Terrain")
    LR = .Range("K" & Rows.Count).End(xlUp).Row
    For i = 4 To LR
        With .Range("K" & i)
            If .Value = Sheets("Ships").Range("A1").Value And (.Offset(, 4) = "Major" Or .Offset(, 4) = "Minor") Then
            [B].Offset(, -10).Copy Destination:=Sheets("Report").Range("A72" & Rows.Count).End(xlUp).Offset(1)[/B]
            End If
        End With
    Next i
End With
End Sub

The only problem I now have is that I am getting a 1004 error on the bold line - anyone have any idea what is wrong with it?

 

[mikerickson]

Perhaps

With Sheets("Terrain").Range("K:K")
    With Range(.Cells(1, 4), .Cells(Rows.Count, 1).End(xlUp))
        .AutoFilter Field:=1, Criteria1:="=" & Sheets("Ships").Range("A1").Value
        .AutoFilter Field:=4, Criteria1:="=off", Operator:=xlOr, Criteria2:="=on"
        .Copy Destination:=Sheets("Report").Cells(.Rows.Count, 1).End(xlUp)
    End With
End With