get range and copy part of range

[smiler44]

I want to filter a spread sheet. There may be just one row plus the header of more rows plus the header.

I want to copy column A and first visible cell after the header to column C and last used visible cell.

so I filter and there are two entries, row 10 and row 99.
I want to copy A10 to C99 visible cells only. I don't cant anything from column D onwards.

next filter and it is row 33 and 45 I want to copy A33 to C45.


how?......please?

everyone else gone home and I'm still stuck at my desk

thank you

smiler44

 

[smiler44]

more searches via Google and getting different bits of code I have come up with this. It filters, copies just the cells I want from the visible cells and pastes to another sheet.
I will add a do until loop as I need to go back to sheet1, filter for my next word, copy the data and paste but this is a major milestone for the night.
code may not be pretty, I may not even understand it but at this time of night, it works and that will do for tonight. Tomorrow I may even understand it.

smiler44

Sub Macro2()
Dim a1141 As String
Dim fcrow As String 'first current row
Dim lcrow As String 'last current row

    Windows("MISSING a1141 CODES.xlsm").Activate
    Sheets("sheet1").Select
    Range("A2").Activate
    a1141 = ActiveCell.Value
    
find1141:
   ActiveSheet.Range("$A$1:$BO$5403").AutoFilter Field:=1, Criteria1:=a1141
Range("a2", Cells(Rows.Count, "a").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Activate
    fcrow = ActiveCell.Row
 
 ActiveSheet.Range("$a$2:$a$7000").AutoFilter Field:=1, Criteria1:=a1141
    ActiveSheet.Range("$a$2:$a$7000").AutoFilter Field:=1, Criteria1:=a1141
Rng = ("A" & crow) & ":" & ("B" & lcrow)
    Sheet1.Range(Rng).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("Sheet2").Range("A1").Offset(ColumnOffset:=1)