Where am I going wrong with this code? -

[Si1209]

At random times throughout running this macro it keeps coming back with the dreaded "1004 PasteSpecial method of Range class failed" I'm not sure why this is and I cant see anything throughout the code that sticks out as to what could be wrong. Its not like it stops at a certain place either it will be at random points in the macro.

Thanks

Sub CMSImport()


Application.ScreenUpdating = False
On Error GoTo e:
Application.ScreenUpdating = 1
sk = Range("B2") ''''''' change skill number as required"
ImpDate1 = Range("B1")
Range("A11:W41").Select
Selection.ClearContents

Set cvsSrv = cvsApp.Servers(1) '(cvsApp.Servers.Count) '''''' we set a reference to the server here
Call doRep5("Historical\Designer\GI Skill Perf (I) ASA", sk, ImpDate1)
ThisWorkbook.ActiveSheet.Cells(11, 1).PasteSpecial
logout
Application.ScreenUpdating = 0

On Error GoTo e:
Application.ScreenUpdating = 1
sk = Range("B44") ''''''' change skill number as required"
ImpDate1 = Range("B1")
Range("A45:W74").Select
Selection.ClearContents

Set cvsSrv = cvsApp.Servers(1) '(cvsApp.Servers.Count) '''''' we set a reference to the server here
Call doRep5("Historical\Designer\GI Skill Perf (I) ASA", sk, ImpDate1)
ThisWorkbook.ActiveSheet.Cells(45, 1).PasteSpecial
logout
Application.ScreenUpdating = 0

On Error GoTo e:
Application.ScreenUpdating = 1
sk = Range("B77") ''''''' change skill number as required"
ImpDate1 = Range("B1")
Range("A78:W109").Select
Selection.ClearContents

Set cvsSrv = cvsApp.Servers(1) '(cvsApp.Servers.Count) '''''' we set a reference to the server here
Call doRep5("Historical\Designer\GI Skill Perf (I) ASA", sk, ImpDate1)
ThisWorkbook.ActiveSheet.Cells(78, 1).PasteSpecial
logout
Application.ScreenUpdating = 0

On Error GoTo e:
Application.ScreenUpdating = 1
sk = Range("B110") ''''''' change skill number as required"
ImpDate1 = Range("B1")
Range("A111:W141").Select
Selection.ClearContents

Set cvsSrv = cvsApp.Servers(1) '(cvsApp.Servers.Count) '''''' we set a reference to the server here
Call doRep5("Historical\Designer\GI Skill Perf (I) ASA", sk, ImpDate1)
ThisWorkbook.ActiveSheet.Cells(111, 1).PasteSpecial
logout
Application.ScreenUpdating = 0

On Error GoTo e:
Application.ScreenUpdating = 1
sk = Range("B176") ''''''' change skill number as required"
ImpDate1 = Range("B1")
Range("A177:W208").Select
Selection.ClearContents

Set cvsSrv = cvsApp.Servers(1) '(cvsApp.Servers.Count) '''''' we set a reference to the server here
Call doRep5("Historical\Designer\GI Skill Perf (I) ASA", sk, ImpDate1)
ThisWorkbook.ActiveSheet.Cells(177, 1).PasteSpecial
logout
Application.ScreenUpdating = 0

On Error GoTo e:
Application.ScreenUpdating = 1
sk = Range("B209") ''''''' change skill number as required"
ImpDate1 = Range("B1")
Range("A210:W241").Select
Selection.ClearContents

Set cvsSrv = cvsApp.Servers(1) '(cvsApp.Servers.Count) '''''' we set a reference to the server here
Call doRep5("Historical\Designer\GI Skill Perf (I) ASA", sk, ImpDate1)
ThisWorkbook.ActiveSheet.Cells(210, 1).PasteSpecial
logout
Application.ScreenUpdating = 0

On Error GoTo e:
Application.ScreenUpdating = 1
sk = Range("B242") ''''''' change skill number as required"
ImpDate1 = Range("B1")
Range("A243:W273").Select
Selection.ClearContents

Set cvsSrv = cvsApp.Servers(1) '(cvsApp.Servers.Count) '''''' we set a reference to the server here
Call doRep5("Historical\Designer\GI Skill Perf (I) ASA", sk, ImpDate1)
ThisWorkbook.ActiveSheet.Cells(243, 1).PasteSpecial
logout
Application.ScreenUpdating = 0

On Error GoTo e:
Application.ScreenUpdating = 1
sk = Range("B275") ''''''' change skill number as required"
ImpDate1 = Range("B1")
Range("A276:W304").Select
Selection.ClearContents

Set cvsSrv = cvsApp.Servers(1) '(cvsApp.Servers.Count) '''''' we set a reference to the server here
Call doRep5("Historical\Designer\GI Skill Perf (I) ASA", sk, ImpDate1)
ThisWorkbook.ActiveSheet.Cells(276, 1).PasteSpecial
logout
Application.ScreenUpdating = 0

On Error GoTo e:
Application.ScreenUpdating = 1
sk = Range("B306") ''''''' change skill number as required"
ImpDate1 = Range("B1")
Range("A307:W336").Select
Selection.ClearContents

Set cvsSrv = cvsApp.Servers(1) '(cvsApp.Servers.Count) '''''' we set a reference to the server here
Call doRep5("Historical\Designer\GI Skill Perf (I) ASA", sk, ImpDate1)
ThisWorkbook.ActiveSheet.Cells(307, 1).PasteSpecial
logout
Application.ScreenUpdating = 0

On Error GoTo e:
Application.ScreenUpdating = 1
sk = Range("B340") ''''''' change skill number as required"
ImpDate1 = Range("B1")
Range("A341:W371").Select
Selection.ClearContents

Set cvsSrv = cvsApp.Servers(1) '(cvsApp.Servers.Count) '''''' we set a reference to the server here
Call doRep5("Historical\Designer\GI Skill Perf (I) ASA", sk, ImpDate1)
ThisWorkbook.ActiveSheet.Cells(341, 1).PasteSpecial
logout
Application.ScreenUpdating = 0

Exit Sub

e:
If Err.Number <> 91 Then
MsgBox Err.Number & " " & Err.Description
End If

End Sub


Sub doRep5(sReportName As String, ByVal sk As String, ByVal ImpDate As String)
Application.ScreenUpdating = False

ImpDate1 = Range("B1")

On Error Resume Next
cvsSrv.Reports.ACD = 1
Set Info = cvsSrv.Reports.Reports(sReportName)
If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The Report " & sReportName & " was not found on ACD 1", vbCritical Or vbOKOnly, "CentreVu Supervisor"
Else
Set Log = CreateObject("ACSERR.cvslog")
Log.AutoLogWrite "The Report " & sReportName & " was not found on ACD 1"
Set Log = Nothing
End If
Else
B = cvsSrv.Reports.CreateReport(Info, Rep)
If B Then
Debug.Print Rep.SetProperty("Skill(s)", sk)
Debug.Print Rep.SetProperty("Interval(s)", "8-20")
Debug.Print Rep.SetProperty("Date", ImpDate1) 'Relative Date Cell Reference
B = Rep.ExportData("", 9, 0, False, False, True) 'this is for the report option
Rep.Quit
If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove Rep.TaskID
Set Rep = Nothing
End If
End If

Set Info = Nothing



End Sub

 

[JLGWhiz]

Maybe i missed it, but I don't see anything to paste. Also, I would use the actual sheet name instead of ActiveSheet, since the host workbook might not be the active workbook.

 

[Si1209]

Maybe i missed it, but I don't see anything to paste. Also, I would use the actual sheet name instead of ActiveSheet, since the host workbook might not be the active workbook.

I'll try the active sheet thing, it does copy, thats my bad i didnt paste the part of code that it copies.