vba excel copy only visible cells on key press ctr

Question

i have an excel with 75 columns and some thousands of rows of data. Out of 75 columns I am using 5 columns for my vba coding purpose. These 5 columns hold flags (either 0 or 1) based on which I am locking the cells in the corresponding row (Flags are coming from Database). As user doesn't want these flag columns I just hid those columns but when ever user tries to copy data from my workbook to another workbook user is able to copy the hidden columns which client doesn't want.

So is there anyway to restrict them not to copy the hidden columns through VBA or with any setting? Actually for this issue what I thought is like on key press of Ctrl + C, I tried to change the Selection.Copy as Selection.Range.SpecialCells(xlCellTypeVisible). But I am getting some error like wrong number of arguments or invalid property assignment.

The lines of code is

Private Sub Workbook_Open()
     Application.OnKey "^c", "Copy"
End Sub

Sub Copy()
    If Selection Is Nothing Then
    Else
        Selection.Copy = Selection.Range.SpecialCells(xlCellTypeVisible)
    End If
End Sub

Any ideas to restrict users not to copy the hidden columns. Any help would be appreciated greatly.

Answer 1

Try this

Sub Copy()
    Dim rng As Range

    On Error GoTo Whoa

    If Not Selection Is Nothing Then
        Set rng = Selection.Cells.SpecialCells(xlCellTypeVisible)
        rng.Copy
    End If

LetsContinue:
    Exit Sub
Whoa:
    MsgBox Err.Description, vbCritical, "Error Number : " & Err.Number
    Resume LetsContinue
End Sub

Note: I have used Error Handling which is a must because the user might select non contiguous ranges and the code will break if the error handling is not done :) See Screenshot below