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.
 
Hope you will help me more by modifying the codes.
No point me taking on another problem modifying the codes when we don't have this problem sorted.

Do you have any worksheet change events that are triggering a calculation?
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
No point me taking on another problem modifying the codes when we don't have this problem sorted.

Do you have any worksheet change events that are triggering a calculation?
This Workbook:
Rich (BB code):
Private Sub Workbook_Activate()
    'Range("A1").Select
End Sub

Private Sub Workbook_Open()
    Call ClearWrkBooks
    Worksheets("Dashboard").Activate
    Range("A1").Select
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call ClearWrkBooks
    Worksheets("Dashboard").Activate
End Sub

Private Sub Workbook_SheetActivate(ByVal wrkSheet As Object)
    If wrkSheet.Name = "Dashboard" Or wrkSheet.Name = "Home" Then
        ActiveWindow.DisplayHeadings = False
        Application.DisplayFormulaBar = False
        ActiveWindow.DisplayGridlines = False
    Else
        ActiveWindow.DisplayHeadings = True
        Application.DisplayFormulaBar = True
        ActiveWindow.DisplayGridlines = True
    End If
End Sub

Function ClearWrkBooks()
    For Each pubWrkSheet In ThisWorkbook.Worksheets
        If pubWrkSheet.Name = "Dashboard" Or pubWrkSheet.Name = "Home" Then
            Application.DisplayFullScreen = True
            Application.DisplayFormulaBar = False
            ActiveWindow.DisplayHeadings = False
            ActiveWindow.DisplayGridlines = False
        Else
            pubWrkSheet.Visible = xlSheetVeryHidden
        End If
    Next

    '----------Delete all ranged names----------
    Dim rName As Name
    For Each rName In Application.ActiveWorkbook.Names
        rName.Delete
    Next
End Function

Dashboard:
Rich (BB code):
Private Sub cmdUserForm_Click()
    A1_MainForm.Show
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    MsgBox ("Sorry! Right Click is disabled for this Worksheet.")
End Sub

Function HideWorksheets()
    Dim hideOthWrkSheet
    
    ScreenUpdating = False
    For Each wkSheet In ActiveWorkbook.Worksheets
        If (Not wkSheet.Name = "Dashboard") Or (Not wkSheet.Name = "Home") Then
           wkSheet.Visible = False
        End If
    Next wkSheet
End Function

Registry Log, User Log, and the rest...
(no code yet)
 

Attachments

  • Workbook.jpg
    Workbook.jpg
    83.4 KB · Views: 6
Upvote 0
Mr. MARK858,

Thanks for your patience and help.

Let me post my solution to other concern for the sake of the newbies (like me, this will also serve as my future reference):

AutoFilter with two or more criteria that I found somewhere on this board, inserted within the code you have given.

Rich (BB code):
    Sheets("User Log").Range("B4:I100").ClearContents
      
    With Sheets("Registry Log")
        .Range("B2:AC100").AutoFilter 27, cboHmDept.Value
        .Range("B2:AC100").AutoFilter Field:=26, Criteria1:=Array("User", "Admin"), Operator:=xlFilterValues
        .Range("B2:AC100").AutoFilter Field:=28, Criteria1:="Active", Operator:=xlFilterValues
        .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
    End With
    
    With Sheets("Registry Log")
        .Range("Y4:AC" & .Columns("Y:AC").Find("*", , xlValues, , xlByRows, xlPrevious).Row).Copy
    End With
    
    With Sheets("User Log")
        .Range("E4").PasteSpecial xlValues
        Application.Goto .Range("B4"), True
    End With
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With

My apology for asking other concerns other than the "greyed-out Paste Options" because actually, those codes will be of big help while looking for a solution about the greyed-out.
 

Attachments

  • Registry & User Log.jpg
    Registry & User Log.jpg
    139.8 KB · Views: 3
Upvote 0
My apology for asking other concerns other than the "greyed-out Paste Options" because actually, those codes will be of big help while looking for a solution about the greyed-out.
So now I have this output after your code.
 

Attachments

  • Output.png
    Output.png
    111.5 KB · Views: 9
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

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
Back
Top