excel 1004 "cannot paste the data"

D

domleg

New Member
Joined
Oct 21, 2014
Messages
5
I have a workbook that provides specifications for a PC. I have a specs sheet, spec sheets worker (not sure if I need this but it fixed issues with formatting) The idea is that after the data is filled in I run a Finalize sub that shows a picture, but when I toggle a radio button or make changes, it randomly gives the error cannot paste the data (1004).

Here is my sub:

VBA Code: 
Sub Finalize()
   
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    Sheet7.Unprotect Password:="T@le2018"
    Sheet6.Unprotect Password:="T@le2018"
    Sheet5.Unprotect Password:="T@le2018"
    Sheet4.Unprotect Password:="T@le2018"
    Sheet3.Unprotect Password:="T@le2018"
    Sheet2.Unprotect Password:="T@le2018"
    Sheet1.Unprotect Password:="T@le2018"
   
    Worksheets("Configuration").Select
    If Range("A2").Value = "No" Then
   
    MsgBox ("Please enter all data before clicking this button.")
   
    Else

    Dim doc As Worksheet
    Dim spe As Shape
    Dim pictitle As String
    Dim currentcell As String
   
    currentcell = ActiveCell.Address
   
    Set doc = Worksheets(1)
    pictitle = ""
   
    For Each Shp In doc.Shapes
   
        If Shp.Name = "ClassA" Or Shp.Name = "ClassB" Or Shp.Name = "ClassC" Or Shp.Name = "ClassD" Or Shp.Name = "ClassE" Then
       
        Shp.Delete
       
        End If
   
    Next Shp
   
    'Node Standalone from 24 to 30, Node + 1PC 31 to 41, Node +2PC 42 to 60 SCR will be 0.5 of the total number of RU
   
    If Cells(17, 1).Value < 1 Then
        Cells(17, 2).Value = ""
    End If

    If Cells(17, 1).Value >= 1 And Cells(17, 1).Value <= 9 Then
        Cells(17, 2).Value = "A"
        Cells(17, 3).Value = "1"
        Cells(17, 4).Value = "9"
    End If
   
    If Cells(17, 1).Value >= 10 And Cells(17, 1).Value <= 14 Then
        Cells(17, 2).Value = "B"
        Cells(17, 3).Value = "10"
        Cells(17, 4).Value = "14"
    End If

    If Cells(17, 1).Value >= 15 And Cells(17, 1).Value <= 19 Then
        Cells(17, 2).Value = "C"
        Cells(17, 3).Value = "15"
        Cells(17, 4).Value = "19"
    End If

    If Cells(17, 1).Value >= 20 And Cells(17, 1).Value <= 30 Then
        Cells(17, 2).Value = "D"
        Cells(17, 3).Value = "20"
        Cells(17, 4).Value = "30"
    End If

    If Cells(17, 1).Value >= 31 And Cells(17, 1).Value <= 41 Then
        Cells(17, 2).Value = "E"
        Cells(17, 3).Value = "31"
        Cells(17, 4).Value = "41"
    End If
   
    If Cells(17, 1).Value >= 42 And Cells(17, 1).Value <= 60 Then
        Cells(17, 2).Value = "E + Node1"
        Cells(17, 3).Value = "42"
        Cells(17, 4).Value = "60"
    End If
   
    If Cells(17, 1).Value >= 61 And Cells(17, 1).Value <= 80 Then
        Cells(17, 2).Value = "E + Node2"
        Cells(17, 3).Value = "61"
        Cells(17, 4).Value = "80"
    End If
   
    If Cells(17, 1).Value >= 81 And Cells(17, 1).Value <= 200 Then
        Cells(17, 2).Value = "Contact LNA"
        Cells(17, 3).Value = "81"
        Cells(17, 4).Value = "200"
    End If

    If Range("B17").Value = "A" Then

    pictitle = "ClassA"
    Sheets("Server Specs").Select
    Sheets("Server Specs").Range("A1:B21").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Configuration").Select
    Sheets("Configuration").Range("K19").Select
    ActiveSheet.Pictures.Paste
    ActiveSheet.Pictures(ActiveSheet.Pictures.Count).Name = pictitle
    Range(currentcell).Select
   
    End If
   
    If Range("B17").Value = "B" Then
   
    pictitle = "ClassB"
    Sheets("Server Specs").Select
    Sheets("Server Specs").Range("C1:D21,M1:P21").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Server Specs Worker").Select
    Sheets("Server Specs Worker").Range("A1").Select
    ActiveSheet.Paste
    Sheets("Server Specs Worker").Range("A1:F21").Select
    Selection.Copy
    Sheets("Configuration").Select
    Sheets("Configuration").Range("K19").Select
    ActiveSheet.Pictures.Paste
    ActiveSheet.Pictures(ActiveSheet.Pictures.Count).Name = pictitle
    Range(currentcell).Select
   
    End If
   
   
    If Range("B17").Value = "C" Then
    pictitle = "ClassC"
    Sheets("Server Specs").Select
    Sheets("Server Specs").Range("E1:F21,M1:P21").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Server Specs Worker").Select
    Sheets("Server Specs Worker").Range("A1").Select
    ActiveSheet.Paste
    Sheets("Server Specs Worker").Range("A1:F21").Select
    Selection.Copy
    Sheets("Configuration").Select
    Sheets("Configuration").Range("K19").Select
    ActiveSheet.Pictures.Paste
    ActiveSheet.Pictures(ActiveSheet.Pictures.Count).Name = pictitle
    Range(currentcell).Select
   
    End If
   
    If Range("B17").Value = "D" Then
    pictitle = "ClassD"
    Sheets("Server Specs").Select
    Sheets("Server Specs").Range("G1:H21,M1:P21").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Server Specs Worker").Select
    Sheets("Server Specs Worker").Range("A1").Select
    ActiveSheet.Paste
    Sheets("Server Specs Worker").Range("A1:F21").Select
    Selection.Copy
    Sheets("Configuration").Select
    Sheets("Configuration").Range("K19").Select
    ActiveSheet.Pictures.Paste
    ActiveSheet.Pictures(ActiveSheet.Pictures.Count).Name = pictitle
    Range(currentcell).Select
   
    End If
   
    If Range("B17").Value = "E" Then
    pictitle = "ClassE"
    Sheets("Server Specs").Select
    Sheets("Server Specs").Range("I1:J21,M1:P21").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Server Specs Worker").Select
    Sheets("Server Specs Worker").Range("A1").Select
    ActiveSheet.Paste
    Sheets("Server Specs Worker").Range("A1:F21").Select
    Selection.Copy
    Sheets("Configuration").Select
    Sheets("Configuration").Range("K19").Select
    ActiveSheet.Pictures.Paste
    ActiveSheet.Pictures(ActiveSheet.Pictures.Count).Name = pictitle
    Range(currentcell).Select
   
    End If
   
    If Range("B17").Value = "E + Node1" Then
    pictitle = "ClassE"
    Sheets("Server Specs").Select
    Sheets("Server Specs").Range("I1:K21,M1:P21").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Server Specs Worker").Select
    Sheets("Server Specs Worker").Range("A1").Select
    ActiveSheet.Paste
    Sheets("Server Specs Worker").Range("A1:G21").Select
    Selection.Copy
    Sheets("Configuration").Select
    Sheets("Configuration").Range("K19").Select
    ActiveSheet.Pictures.Paste
    ActiveSheet.Pictures(ActiveSheet.Pictures.Count).Name = pictitle
    Range(currentcell).Select

    End If
   
    If Range("B17").Value = "E + Node2" Then 'D + 1 Node and D + 2 Nodes
    pictitle = "ClassE"
    Sheets("Server Specs").Select
    Sheets("Server Specs").Range("I1:P21").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Configuration").Select
    Sheets("Configuration").Range("K19").Select
    ActiveSheet.Pictures.Paste
    ActiveSheet.Pictures(ActiveSheet.Pictures.Count).Name = pictitle
    Range(currentcell).Select
   
    End If
   
    If Range("B17").Value = "Contact LNA" Then 'Contact LNA
    pictitle = "ClassE"
    Sheets("Server Specs").Select
    Sheets("Server Specs").Range("I1:I21,Q1:Q21").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Server Specs Worker").Select
    Sheets("Server Specs Worker").Range("A1").Select
    ActiveSheet.Paste
    Sheets("Server Specs Worker").Range("A1:B21").Select
    Selection.Copy
    Sheets("Configuration").Select
    Sheets("Configuration").Range("K19").Select
    ActiveSheet.Pictures.Paste
    ActiveSheet.Pictures(ActiveSheet.Pictures.Count).Name = pictitle
    Range(currentcell).Select
   
    End If
   
    For Each Shp In doc.Shapes
       
        If Shp.Name = "Legend" Then
           
            Dim colorVal As Long
            Dim R As Long
            Dim G As Long
            Dim B As Long
           
            colorVal = Range("K18").DisplayFormat.Interior.Color
            R = colorVal Mod 256
            G = colorVal \ 256 Mod 256
            B = colorVal \ 65536
               
            Shp.Fill.ForeColor.RGB = RGB(R, G, B)
               
        End If
   
    Next Shp
   
    End If
     
    Sheet2.Range("B6", "H6").Locked = False
   
    Sheet7.EnableSelection = xlNoSelection
    Sheet6.EnableSelection = xlNoSelection
    Sheet5.EnableSelection = xlNoSelection
    Sheet4.EnableSelection = xlNoSelection
    Sheet3.EnableSelection = xlNoSelection
    Sheet2.EnableSelection = xlNoSelection

    Sheet7.Protect Password:="T@le2018"
    Sheet6.Protect Password:="T@le2018"
    Sheet5.Protect Password:="T@le2018"
    Sheet4.Protect Password:="T@le2018"
    Sheet3.Protect Password:="T@le2018"
    Sheet2.Protect Password:="T@le2018"
    Sheet1.Protect Password:="T@le2018"

    Worksheets("Configuration").Select
    If Range(currentcell).Address = "$B$3" Or Range(currentcell).Address = "$C$3" Then
   
    Range("B1").Select
   
    Else
   
    Range(currentcell).Select
   
    End If
   
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
You must log in or register to reply here.

Similar threads

D
  • Solved
Unable to paste data
Replies
5
Views
439
MARK858
MARK858
A
  • Question
Import Table Data from Web using VBA to excel Worksheet and able to input Values which can modify the web data to be extract
Replies
0
Views
344
Ambrosia
A
D
  • Question
Copy range from one sheet and paste it on another sheet as a picture
Replies
1
Views
389
Fluff
Fluff
J
  • Question
Copy cell content with an if condition + small loop (VBA Excel)
Replies
7
Views
269
mumps
mumps
N
  • Solved
Error 1004 Paste method of Worksheet class failed
Replies
5
Views
599
MARK858
MARK858

Forum statistics

Threads
1,214,518
Messages
6,119,988
Members
448,935
Latest member
ijat

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