Hi everyone.
I have a macro to copy a range of data from one workbook to another and have developed the following code.
It works fine on my machine but when I load it on to the server it displays a run time error on the paste line.
Would appreciate any advice.
Many thanks
John
Option Explicit
Sub D_TransferToProductionShiftReport()
' This macro copies confirmed schedule from S&Op planner to Production Shift Report Workbook.
Dim SCHED As Workbook
Dim PROD As Workbook
Dim LastRow As Long
Set SCHED = ThisWorkbook
Application.ScreenUpdating = False
Application.CutCopyMode = False
If MsgBox("This will open the Production Shift Report and copy the current schedule, do you wish to continue? You will not lose current data.", vbYesNo, "Copy") = vbNo Then Exit Sub
'Change the address of the Production Shift Report on the following line
'Set PROD = Workbooks.Open("C:\Data\Intent\Hubbards\1807 Provided to Hubbards\180818\180808 Production Shift Report.xlsm")
Set PROD = Workbooks.Open("W:\PRODUCTION\Planning\Intent Planning\180808 Production Shift Report.xlsm")
If PROD.ReadOnly Then
MsgBox "The Packaging Shift Report is open, please organise to have closed and try again."
Exit Sub
End If
PROD.Sheets("Schedules").Activate
PROD.Sheets("Schedules").Range("SchedRecords").Select
Selection.ClearContents
SCHED.Sheets("4-Prod S&Op").Activate
Range("D_ProdRecords").Copy
PROD.Sheets("Schedules").Activate
PROD.Sheets("Schedules").Range("A5").Select
Application.Wait (Now + TimeValue("00:00:03")) 'wait 3 seconds
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A2").Select
PROD.Save
PROD.Close
Application.CutCopyMode = False
Application.ScreenUpdating = True
Range("A2").Select
MsgBox "You have successfully copied the scheduling data into the Production Shift Report Workbook"
End Sub