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:
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