VBA Copy & Paste between Workbooks Error 1004

  • 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

    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

    SCHED.Sheets("4-Prod S&Op").Activate
    Application.Wait (Now + TimeValue("00:00:03")) 'wait 3 seconds
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False


    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "You have successfully copied the scheduling data into the Production Shift Report Workbook"

    End Sub

  • I didn't really want to do this, but try replacing your code after the End If line and before the PROD.Save line with something like this:

    SCHED.Sheets("4-Prod S&Op").Range("D_ProdRecords").Copy
    PROD.Sheets("Schedules").Range("A5").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

    The code with all the Selects is obviously from the macro recorder, which writes bad code for this sort of thing. ;)

  • Jonathan
    Many thanks, will give this a go - I don't quite understand why the run time error occurs and am surmising that the macro is trying to run faster than the computers accessing the files from the server hence I introduced the "wait" line. This was based on the thought that the macro had copied the data and was switching across to the other worksheet and pasting it before the computer had caught up. Any thoughts, any would be welcome and appreciate you're a busy man, I always want to understand the why :)

  • I do not know what caused the error and (therefore) whether using better code will fix it. Your hypothesis is incorrect, though: VBA is synchronous enough that a range will not be selected or copied until its workbook is loaded into memory.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!