Selecting visible cells in a table column after manually filtering

[PCrawf]

Hi, I would like help with a macro to select visible cells in specific columns of a table.

My aim is to manually filter my table, then run this macro which will select data from only the visible cells in three columns. Each column data will be copied to another sheet.
I already know how to manually filter my tables, how to write a macro to select various or all cells in columns, even down to the last row, and how to copy, then paste to other sheets. What I do not know, and am struggling with, is how to select only the visible cells of these columns.

I thought it may be selecting the first visible cell below the header, then to the last row, then filtering only visible cells (Alt ; on the keyboard), but i cant find the right code to make it work.

Basically, I only need help to write the code that will select visible cells in a predetermined column.

I do not wish to appear rude, it is just that I have been accused on another forum of not been clear enough. I do appreciate the time people have towards helping others.
Please, no rude or hurtful comments. Thank you.

FYI:
The table header is in row 10 (the columns of concern are E, I & O).
Sheet Name: MedComp
Sheet Number: Sheet30
Table Name: TBL_MedComp

 

[Zot]

The command is something like this

Sheets("Sheet1").Range("B2:B200").SpecialCells(xlCellTypeVisible).Select

 

[denzo36]

Once the column has been filtered this should work to select visible cells

Selection.CurrentRegion.Select
Selection.Copy

This is the equivalent to Ctrl+* command in Excel. You can then paste it where ever you need it.

 

[veyselemre]

Sub copyData()
    Dim rng As Range
    With Sheets("Med Comp")
        If .AutoFilterMode = True Then
            Set rng = .AutoFilter.Range.Offset(1).Resize(.AutoFilter.Range.Rows.Count - 1)
            Set rng = Intersect(rng, .Range("E:E,I:I,O:O"))
            rng.Copy Sheets("Sheet2").Range("A1")
        End If
    End With
End Sub

 

[Fluff]

Another option

Sub PCrawf()
   With Sheets("MedComp").ListObjects("TBL_MedComp")
      Intersect(.DataBodyRange.SpecialCells(xlVisible), .Parent.Range("E:E,I:I,O:O")).Select
   End With
End Sub

Although there is very rarely any need to select anything.

 

[PCrawf]

The command is something like this

Sheets("Sheet1").Range("B2:B200").SpecialCells(xlCellTypeVisible).Select

Thank you but this will select all visible cells in the entire column including table header and data outside the table. I would like to focus on only data in the table. I appreciate your help.

 

[PCrawf]

Once the column has been filtered this should work to select visible cells

Selection.CurrentRegion.Select
Selection.Copy

This is the equivalent to Ctrl+* command in Excel. You can then paste it where ever you need it.

Thank you but I believe this implies I have manually selected the data already. I am trying to generate a macro to handle the manual task of selecting the data for me as it will be applied to multiple columns. Thank you for your input.

 

[PCrawf]

Sub copyData()
    Dim rng As Range
    With Sheets("Med Comp")
        If .AutoFilterMode = True Then
            Set rng = .AutoFilter.Range.Offset(1).Resize(.AutoFilter.Range.Rows.Count - 1)
            Set rng = Intersect(rng, .Range("E:E,I:I,O:O"))
            rng.Copy Sheets("Sheet2").Range("A1")
        End If
    End With
End Sub

Thank you, but this code didn't select or copy the data to the destination sheet. I'm not sure how to troubleshoot it as it did not give an error either. Thank you though. The post by Fluff has worked though.

 

[PCrawf]

Another option

Sub PCrawf()
   With Sheets("MedComp").ListObjects("TBL_MedComp")
      Intersect(.DataBodyRange.SpecialCells(xlVisible), .Parent.Range("E:E,I:I,O:O")).Select
   End With
End Sub

Although there is very rarely any need to select anything.

Fluff, thank you this worked as I wanted. My next step is to adapt this and rather than leaving as a Selection, I'll copy it to my destination. I'll play with a few ways to do this as I am still learning the various ways. I appreciate your time.

 

[Fluff]

Glad we could help & thanks for the feedback.