MS-Excel VBA | Copy and Paste Option Greyed Out

SoniboiTM

New Member
Joined
Jul 25, 2020
Messages
38
Office Version
  1. 2019
Platform
  1. Windows
  2. Web
Hi!
I need help.
I have to copy filtered data from Registry Log to User Log as a ListBox1 items but unable to copy the data from one sheet to another.
Note that there's no problem copying data and paste it within the same sheet.

Manual operation or operation thru VBA = same problem.

File extension: [My File.xlsm] (problem only in this workbook)

Code:

VBA Code:
Private Sub cboHmDept_Change()
    Sheets("Registry Log").Activate
    ActiveSheet.ShowAllData
    ActiveSheet.Range("$B$2:$AC$100").AutoFilter Field:=27, Criteria1:=cboHmDept.Value

    Application.ScreenUpdating = False
    Sheets("Registry Log").Select
    Range("C4:D4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("User Log").Select
    Range("B4").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    
    Columns("A:A").ColumnWidth = 1.5
    Rows("1:1").Select: Selection.RowHeight = 1.5
    Rows("3:3").Select: Selection.RowHeight = 1.5
    Columns("B:H").EntireColumn.AutoFit
    Range("B4").Select
End Sub

Thank you in advance.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

SoniboiTM

New Member
Joined
Jul 25, 2020
Messages
38
Office Version
  1. 2019
Platform
  1. Windows
  2. Web
Notes (manually):
- No problem when copying text from an internet browser and other apps like notepad.
- Problem occurs when copying data from other worksheets or other workbooks.
- It's okay to copy text/data within the same sheet (e.g., copy from A4 to D4).

Not working: "Selection.PasteSpecial Paste:=xlPasteValues"
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,978
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
First of all turn any protection that you have on both sheets off and then let me know what happens with the code below (including any error message that you get).

VBA Code:
Private Sub cboHmDept_Change()
    Application.ScreenUpdating = False
  
    With Sheets("Registry Log")
        .Range("B2:AC100").AutoFilter 27, cboHmDept.Value
        .Range("C4:D" & .Columns("C:D").Find("*", , xlValues, , xlByRows, xlPrevious).Row).Copy
    End With
    
    With Sheets("User Log")
        .Range("B4").PasteSpecial xlValues
        .Columns("A:A").ColumnWidth = 1.5
        .Range("1:1,3:3").RowHeight = 1.5
        .Columns("B:H").EntireColumn.AutoFit
        Application.Goto .Range("B4"), True
    End With
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With 

End Sub
 
Solution

SoniboiTM

New Member
Joined
Jul 25, 2020
Messages
38
Office Version
  1. 2019
Platform
  1. Windows
  2. Web
First of all turn any protection that you have on both sheets off and then let me know what happens with the code below (including any error message that you get).

VBA Code:
Private Sub cboHmDept_Change()
    Application.ScreenUpdating = False
 
    With Sheets("Registry Log")
        .Range("B2:AC100").AutoFilter 27, cboHmDept.Value
        .Range("C4:D" & .Columns("C:D").Find("*", , xlValues, , xlByRows, xlPrevious).Row).Copy
    End With
   
    With Sheets("User Log")
        .Range("B4").PasteSpecial xlValues
        .Columns("A:A").ColumnWidth = 1.5
        .Range("1:1,3:3").RowHeight = 1.5
        .Columns("B:H").EntireColumn.AutoFit
        Application.Goto .Range("B4"), True
    End With
   
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With

End Sub

Thanks a lot.

Problem (greyed paste special) not yet resolved even after removing the protection.

I even recorded a macro and call for it in my sub-routine procedure:

VBA Code:
Function testMac()
    Sheets("Dashboard").Select
    Cells.Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("A1").Select
    Sheets("Registry Log").Select
    Cells.Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("A1").Select
    Sheets("User Log").Select
    Cells.Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("A1").Select
End Function

And:

Code:
    Call testMac
    txtRegSrchID.Locked = False
    
    'temporary - for testing purpose only
    pubPrivLock = False     'remove later
    
    txtRegLName.Locked = pubPrivLock
    txtRegFName.Locked = pubPrivLock
    txtRegMName.Locked = pubPrivLock
    txtRegSuffix.Locked = pubPrivLock
    txtRegNName.Locked = pubPrivLock
    cboRegGender.Locked = pubPrivLock
    cboRegMaritalStatus.Locked = pubPrivLock
    txtRegReligion.Locked = pubPrivLock
    txtRegDOB.Locked = pubPrivLock
    txtRegPOB.Locked = pubPrivLock
    txtRegContactNum.Locked = pubPrivLock
    txtRegEmailAddr.Locked = pubPrivLock
    txtRegAddr1.Locked = pubPrivLock
    txtRegAddr2.Locked = pubPrivLock
    txtRegTown.Locked = pubPrivLock
    txtRegProvince.Locked = pubPrivLock
    txtRegZIP.Locked = pubPrivLock
    txtRegEmpID.Locked = pubPrivLock
    txtRegJobTitle.Locked = pubPrivLock
    cboDeptSection.Locked = pubPrivLock
    txtRegEmpStart.Locked = pubPrivLock
    txtRegEmpEnd.Locked = pubPrivLock
    txtRegPW.Locked = pubPrivLock
    rdoRegAdminRights.Locked = pubPrivLock
    rdoRegUserRights.Locked = pubPrivLock
    rdoRegNARights.Locked = pubPrivLock
    cboDeptSection.Locked = pubPrivLock
    Call testMac

I added two new sheets also to test (a clean sheet, no formatting), named them Sheet A and Sheet B, type texts in Sheet A then manually tried to copy and pasted them to Sheet B but got the same result.

Still looking for a solution.

BTW, thanks a lot for the codes above. It works great and I have to re-code my program again and apply yours.

You don't have an idea how long my codes are but you make the codes simple, easy to understand, and concise. That's great!
 

Attachments

  • Paste Special - Grey.jpg
    Paste Special - Grey.jpg
    84.4 KB · Views: 2

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,978
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

You are unlocking the cells in the codes but not turning off the protection, did you turn it off manually?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,978
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

No the protection, where it says Protect Sheet, it will say Unprotect Sheet if protected

1608984851903.png
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,978
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Is there any protection on the workbook? do you have merged cells?
 

SoniboiTM

New Member
Joined
Jul 25, 2020
Messages
38
Office Version
  1. 2019
Platform
  1. Windows
  2. Web
Is there any protection on the workbook? do you have merged cells?
Sir,

There's no protection on the workbook and no merged cells.

Hope you will help me more by modifying the codes. Here's the sample registry and user logs data (see the attached screenshots):

Criteria 1: Department (e.g., Department 2)
Criteria 2: Restriction (Not N/A)

Rich (BB code):
Private Sub cboHmDept_Change()
    Application.ScreenUpdating = False
 
    Sheets("User Log").Range("B4:H100").ClearContents
     
    With Sheets("Registry Log")
        .Range("B2:AC100").AutoFilter 27, cboHmDept.Value
        .Range("C4:D" & .Columns("C:D").Find("*", , xlValues, , xlByRows, xlPrevious).Row).Copy
    End With
   
    With Sheets("User Log")
        .Range("C4").PasteSpecial xlValues
        .Columns("A:A").ColumnWidth = 1.5
        .Range("1:1,3:3").RowHeight = 1.5
        .Columns("B:H").EntireColumn.AutoFit
        Application.Goto .Range("B4"), True
    End With
   
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
    Call RegistryData
End Sub

Thank you so much.
 

Attachments

  • Registry & User Log.jpg
    Registry & User Log.jpg
    139.8 KB · Views: 4

Watch MrExcel Video

Forum statistics

Threads
1,127,589
Messages
5,625,671
Members
416,125
Latest member
NeedExcelHelp2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top