VBA is storing the copied data in the excel clipboard

An Quala

An Quala

Board Regular
Joined
Mar 21, 2022
Messages
146
Office Version
  1. 2021
Platform
  1. Windows
Hello, I have a VBA Code which copies data from certain rows and then paste them to other rows but after the code is finished, I can see all the copied data in the excel clipboard which kind make the whole code slow, so what could be the possible way to stop saving data in the clipboard once it is pasted, I am attaching the screenshot of the clipboard to help you better understand the problem,

Thank you.
Data in the clipboard.png
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
The first part is this, other 2 parts are kind of repetition.


VBA Code: 
    Dim dStart As Double
    Dim dTime As Double

    Dim Answer As VbMsgBoxResult
    
    Answer = MsgBox("Are you sure you want to run this program?", vbYesNo, "Confirmation Message")
    
    If Answer = vbYes Then
    
    dStart = Timer
    
    
    Dim c As Range, va, x
     For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
        Set c = Worksheets(x).UsedRange
        va = c.Value
        Worksheets(x).Cells.NumberFormat = "General"
        c = va
    Next
       
    'Sponsored Products Campaigns
    
    Sheets("Sponsored Products Campaigns").Select
    If Range("A1") <> "" Then
    
    Columns("D:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Columns("Y:Y").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("G2").FormulaR1C1 = "=XLOOKUP(RC[1],R2C8:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C8,R2C10:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C10)"
    
    Range("G2").AutoFill Destination:=Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row)
    
        Columns("G:G").Copy
    Columns("G:G").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

With Sheets("Sponsored Products Campaigns").Cells(1).CurrentRegion.Resize(, 49)
             On Error Resume Next
    With .Offset(1).Resize(.Rows.Count - 1).Columns(49)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -47).Address & "<>""Keyword"")*(" & .Offset(, -47).Address & "<>""Product Targeting"")+(" & .Offset(, -30).Address & "<>""enabled"")+(" & .Offset(, -4).Address & "<>""enabled"")+(" & .Offset(, -3).Address & "<>""enabled""),"""",1)")
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        .ClearContents
         On Error GoTo 0
        
    End With
End With
    Sheets("Control Panel").Select
If Range("H42") <> "" Then
Sheets("Sponsored Products Campaigns").Select


Application.ScreenUpdating = False

    With ActiveSheet
        Set vRng = .UsedRange.Columns("AQ")
        vRngJ42 = Sheets("Control Panel").Range("J42").Value
        vRows = vRng.Rows.Count
        For vN = vRows To 1 Step -1
            If InStr(1, UCase(.Cells(vN, "AQ")), UCase(vRngJ42)) Then _
                .Rows(vN).EntireRow.Delete
        Next vN
    End With

    Application.ScreenUpdating = True
        
End If

    Range("F2").FormulaR1C1 = _
        "=XLOOKUP(RC7,'Control Panel'!R9C4:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C4,'Control Panel'!R9C5:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C5)"
    Range("F2").AutoFill Destination:=Range("F2:F" & Cells(Rows.Count, 1).End(xlUp).Row)
    Sheets("Control Panel").Select
    Range("D2:E2").Copy
    Sheets("Sponsored Products Campaigns").Select
    Range("D2").Select
    ActiveSheet.Paste
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC5<>"""",""update"","""")"
    Range("C2:E2").AutoFill Destination:=Range("C2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
    Range("Y1").NumberFormat = "General"
    Range("Y1").FormulaR1C1 = "Bid Change %"
    Range("Y2").FormulaR1C1 = "=IFERROR((RC4-RC24)/RC24,"""")"
    Range("Y2").Style = "Percent"
    Range("Y2").AutoFill Destination:=Range("Y2:Y" & Cells(Rows.Count, 1).End(xlUp).Row)
    Columns("Y:Y").Copy
    Columns("Y:Y").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("C:F").Copy
    Columns("C:F").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:G").Delete Shift:=xlToLeft
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("V2").Select
    ActiveSheet.Paste
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("Q2").Select
    ActiveSheet.Paste
    Columns("D:E").Delete Shift:=xlToLeft

With Sheets("Sponsored Products Campaigns").Cells(1).CurrentRegion.Resize(, 45)
                    On Error Resume Next
    With .Offset(1).Resize(.Rows.Count - 1).Columns(45)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -42).Address & "<>""update""),"""",1)")
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        .ClearContents
         On Error GoTo 0
        
    End With
End With

Range("A1:AR" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter
End If
 
Upvote 0
Here is the full code,


VBA Code: 
Sub Bulk_OptimizeV2()

    Dim dStart As Double
    Dim dTime As Double

    Dim Answer As VbMsgBoxResult
   
    Answer = MsgBox("Are you sure you want to run this program?", vbYesNo, "Confirmation Message")
   
    If Answer = vbYes Then
   
    dStart = Timer
   
   
    Dim c As Range, va, x
     For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
        Set c = Worksheets(x).UsedRange
        va = c.Value
        Worksheets(x).Cells.NumberFormat = "General"
        c = va
    Next
      
    'Sponsored Products Campaigns
   
    Sheets("Sponsored Products Campaigns").Select
    If Range("A1") <> "" Then
   
    Columns("D:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   
    Columns("Y:Y").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("G2").FormulaR1C1 = "=XLOOKUP(RC[1],R2C8:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C8,R2C10:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C10)"
   
    Range("G2").AutoFill Destination:=Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row)
   
        Columns("G:G").Copy
    Columns("G:G").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

With Sheets("Sponsored Products Campaigns").Cells(1).CurrentRegion.Resize(, 49)
             On Error Resume Next
    With .Offset(1).Resize(.Rows.Count - 1).Columns(49)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -47).Address & "<>""Keyword"")*(" & .Offset(, -47).Address & "<>""Product Targeting"")+(" & .Offset(, -30).Address & "<>""enabled"")+(" & .Offset(, -4).Address & "<>""enabled"")+(" & .Offset(, -3).Address & "<>""enabled""),"""",1)")
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        .ClearContents
         On Error GoTo 0
       
    End With
End With
    Sheets("Control Panel").Select
If Range("H42") = "Turn On" Then
Sheets("Sponsored Products Campaigns").Select


Application.ScreenUpdating = False

    With ActiveSheet
        Set vRng = .UsedRange.Columns("AQ")
        vRngJ42 = Sheets("Control Panel").Range("J42").Value
        vRows = vRng.Rows.Count
        For vN = vRows To 1 Step -1
            If InStr(1, UCase(.Cells(vN, "AQ")), UCase(vRngJ42)) Then _
                .Rows(vN).EntireRow.Delete
        Next vN
    End With

    Application.ScreenUpdating = True
       
End If
    Sheets("Sponsored Products Campaigns").Select
    Range("F2").FormulaR1C1 = _
        "=XLOOKUP(RC7,'Control Panel'!R9C4:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C4,'Control Panel'!R9C5:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C5)"
    Range("F2").AutoFill Destination:=Range("F2:F" & Cells(Rows.Count, 1).End(xlUp).Row)
    Sheets("Control Panel").Select
    Range("D2:E2").Copy
    Sheets("Sponsored Products Campaigns").Select
    Range("D2").Select
    ActiveSheet.Paste
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC5<>"""",""update"","""")"
    Range("C2:E2").AutoFill Destination:=Range("C2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
    Range("Y1").NumberFormat = "General"
    Range("Y1").FormulaR1C1 = "Bid Change %"
    Range("Y2").FormulaR1C1 = "=IFERROR((RC4-RC24)/RC24,"""")"
    Range("Y2").Style = "Percent"
    Range("Y2").AutoFill Destination:=Range("Y2:Y" & Cells(Rows.Count, 1).End(xlUp).Row)
    Columns("Y:Y").Copy
    Columns("Y:Y").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("C:F").Copy
    Columns("C:F").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:G").Delete Shift:=xlToLeft
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("V2").Select
    ActiveSheet.Paste
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("Q2").Select
    ActiveSheet.Paste
    Columns("D:E").Delete Shift:=xlToLeft

With Sheets("Sponsored Products Campaigns").Cells(1).CurrentRegion.Resize(, 45)
                    On Error Resume Next
    With .Offset(1).Resize(.Rows.Count - 1).Columns(45)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -42).Address & "<>""update""),"""",1)")
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        .ClearContents
         On Error GoTo 0
       
    End With
End With

Range("A1:AR" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter
End If

'Sponsored Brands Campaigns

Sheets("Sponsored Brands Campaigns").Select
      If Range("A1") <> "" Then
   
    Columns("D:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   
    Columns("X:X").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("G2").FormulaR1C1 = "=XLOOKUP(RC[1],R2C8:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C8,R2C10:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C10)"
   
    Range("G2").AutoFill Destination:=Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row)
   
        Columns("G:G").Copy
    Columns("G:G").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

With Sheets("Sponsored Brands Campaigns").Cells(1).CurrentRegion.Resize(, 54)
                 On Error Resume Next
    With .Offset(1).Resize(.Rows.Count - 1).Columns(54)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -52).Address & "<>""Keyword"")*(" & .Offset(, -52).Address & "<>""Product Targeting"")+(" & .Offset(, -36).Address & "<>""running"")*(" & .Offset(, -36).Address & "<>""other"")+(" & .Offset(, -37).Address & "<>""enabled"")+(" & .Offset(, -3).Address & "<>""enabled""),"""",1)")

        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        .ClearContents
         On Error GoTo 0
       
    End With
End With
    Range("F2").FormulaR1C1 = _
        "=XLOOKUP(RC7,'Control Panel'!R9C4:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C4,'Control Panel'!R9C5:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C5)"
    Sheets("Control Panel").Select
    Range("D4:E4").Copy
    Sheets("Sponsored Brands Campaigns").Select
    Range("D2").Select
    ActiveSheet.Paste
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC5<>"""",""update"","""")"
    Range("C2:F2").AutoFill Destination:=Range("C2:F157")
    Range("X2").FormulaR1C1 = "=IFERROR((RC4-RC23)/RC23,"""")"
    Range("X2").Style = "Percent"
    Range("X1").NumberFormat = "General"
    Range("X1").FormulaR1C1 = "Bid Change %"
    Range("X2").AutoFill Destination:=Range("X2:X" & Cells(Rows.Count, 1).End(xlUp).Row)
    Columns("X:X").Copy
    Columns("X:X").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("C:F").Copy
    Columns("C:F").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:G").Delete Shift:=xlToLeft
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("U2").Select
    ActiveSheet.Paste
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("O2").Select
    ActiveSheet.Paste
    Columns("D:E").Delete Shift:=xlToLeft
With Sheets("Sponsored Brands Campaigns").Cells(1).CurrentRegion.Resize(, 49)
               On Error Resume Next
    With .Offset(1).Resize(.Rows.Count - 1).Columns(49)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -46).Address & "<>""update""),"""",1)")

        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        .ClearContents
         On Error GoTo 0
       
    End With
End With
 
    Range("A1:AV" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter
    End If
   
    'Sponsored Display Campaigns
  
 
    Sheets("Sponsored Display Campaigns").Select
     If Range("A1") <> "" Then
   
    Columns("D:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   
    Columns("Y:Z").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("G2").FormulaR1C1 = "=XLOOKUP(RC[1],R2C8:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C8,R2C9:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C9)"
   
    Range("G2").AutoFill Destination:=Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row)
   
        Columns("G:G").Copy
    Columns("G:G").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

With Sheets("Sponsored Display Campaigns").Cells(1).CurrentRegion.Resize(, 48)
                 On Error Resume Next
    With .Offset(1).Resize(.Rows.Count - 1).Columns(48)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -46).Address & "<>""Audience Targeting"")*(" & .Offset(, -46).Address & "<>""Product Targeting"")+(" & .Offset(, -31).Address & "<>""enabled"")+(" & .Offset(, -5).Address & "<>""enabled"")+(" & .Offset(, -4).Address & "<>""enabled""),"""",1)")

        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
         .ClearContents
         On Error GoTo 0
       
    End With
End With
    Range("Z2").FormulaR1C1 = "=IF(RC24="""",RC45,RC24)"
    Range("Z2").AutoFill Destination:=Range("Z2:Z" & Cells(Rows.Count, 1).End(xlUp).Row)
    Columns("Z:Z").Copy
    Columns("X:X").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("X1").Select
    Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = "Bid"
    Columns("Z:Z").Delete Shift:=xlToLeft
    Range("F2").FormulaR1C1 = _
        "=XLOOKUP(RC7,'Control Panel'!R9C4:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C4,'Control Panel'!R9C5:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C5)"
    Range("F2").AutoFill Destination:=Range("F2:F" & Cells(Rows.Count, 1).End(xlUp).Row)
    Sheets("Control Panel").Select
    Range("D6:E6").Copy
    Sheets("Sponsored Display Campaigns").Select
    Range("D2").Select
    ActiveSheet.Paste
    Range("C2").FormulaR1C1 = "=IF(RC5<>"""",""update"","""")"
    Range("C2:E2").AutoFill Destination:=Range("C2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
    Range("C2:E" & Cells(Rows.Count, 1).End(xlUp).Row).Select
    Range("Y1").NumberFormat = "General"
    Range("Y1").FormulaR1C1 = "Bid Change %"
    Range("Y2").FormulaR1C1 = "=IFERROR((RC4-RC24)/RC24,"""")"
    Range("Y2").Style = "Percent"
    Range("Y2").AutoFill Destination:=Range("Y2:Y" & Cells(Rows.Count, 1).End(xlUp).Row)
    Columns("Y:Y").Copy
    Columns("Y:Y").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("C:F").Copy
    Columns("C:F").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:G").Delete Shift:=xlToLeft
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("V2").Select
    ActiveSheet.Paste
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("O2").Select
    ActiveSheet.Paste
    Columns("D:E").Delete Shift:=xlToLeft

With Sheets("Sponsored Display Campaigns").Cells(1).CurrentRegion.Resize(, 43)
               On Error Resume Next
    With .Offset(1).Resize(.Rows.Count - 1).Columns(43)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -40).Address & "<>""update""),"""",1)")

        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        .ClearContents
         On Error GoTo 0
       
    End With
End With

Range("A1:AP" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter
End If

dTime = Timer - dStart

     For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
          s = s & Sheets(CStr(x)).Range("A" & Rows.Count).End(xlUp).Row - 1 & " "     'collect the last rows and use the space as separator
     Next
     sp = Split(s)                                              'split string on space
     MsgBox sp(0) & " SP, " & sp(1) & " SB and " & sp(2) & " SD targets have been optimized in " & Format(dTime, "0.0") & "seconds."    'make the text
    
    With ThisWorkbook.Sheets(Array("Sponsored Products Campaigns", "Sponsored Display Campaigns", "Sponsored Brands Campaigns"))
        .Copy
    End With
   
    With ActiveWorkbook
        .Worksheets("Sponsored Products Campaigns").Range("U:U,AB:AR").EntireColumn.Delete
        .Worksheets("Sponsored Brands Campaigns").Range("T:T,AI:AW").EntireColumn.Delete
        .Worksheets("Sponsored Display Campaigns").Range("U:U,Y:AO").EntireColumn.Delete
    End With
   
    If MsgBox("Columns have been deleted" & vbNewLine & vbNewLine & _
        "Do you want to save as a new file?", vbYesNo, "Confirm") = vbNo Then
        ActiveWorkbook.Close SaveChanges:=0
        Exit Sub
    End If
   
    Dim FileName As Variant
    FileName = Application.GetSaveAsFilename(FileFilter:="Microsoft Excel file (*.xlsx), *.xlsx")
    ActiveWorkbook.SaveAs FileName:=FileName
   
   
End If



End Sub
 
Last edited by a moderator:
Upvote 0
Easiest option is to add this line to the end of your code
VBA Code: 
Application.CutCopyMode = False
 
Upvote 0
Fluff said:
Easiest option is to add this line to the end of your code
VBA Code: 
Application.CutCopyMode = False
Click to expand...
Should I add this after every time I copy something or just at the end of the code?
 
Upvote 0
Up to you, you can do it either way. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Clipboard error when copying shape
Replies
4
Views
411
marc81
M
C
  • Solved
Using Active X buttons to copy data to clipboard
2
Replies
11
Views
387
DanteAmor
DanteAmor
D
  • Question
VBA + Excel or Webpage Screenshot along with DateTime stamp from task bar
Replies
0
Views
160
DDRO
D
P
  • Question
VBA to copy a dynamic range to clipboard
2 3
Replies
20
Views
783
Skyybot
Skyybot
T
  • Solved
VBA code cannot find sheet name
2
Replies
11
Views
174
jolivanes
jolivanes

Forum statistics

Threads
1,214,917
Messages
6,122,233
Members
449,075
Latest member
staticfluids

Share this page

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