Copy and paste between workbooks

[mcinnes01]

Hi,

I have and updater module that sits on a server and essentially the purpose of it is to copy and paste a range that comes from an SQL data link, to another spreadsheet that sits on the intranet.

The problem I am having is that the range isn't being pasted to the other spreadsheet when I run the macro.

It seems to work when I step through the macro, but not when I run it.

This is the bit of code that does the copy paste function:

Sub SaveSrv()
Dim MasterBk As Workbook
Dim UpdateBK As Workbook
Dim MSsht As Worksheet
Dim USsht As Worksheet


Set UpdateBK = ActiveWorkbook
Set USsht = UpdateBK.Worksheets("STAFF")

Application.ScreenUpdating = False

Set MasterBk = Workbooks.Open("H:\QL_SCHEDULES\SAM.xls")
Set MSsht = MasterBk.Worksheets("MASTER")


MSsht.Range("A2:I65536").ClearContents

USsht.Range("A2:I65536").Copy Destination:=MSsht.Range("A2")

With MasterBk
    .Save
    .Close
End With

Call email.list

End sub

[mcinnes01]

further to my previous post I have played around with various copy methods and have identified the problem, but cannot resolve it.

Basically the spreadsheet that is opened and where the data is copied to, has a custom save routine that is affecting the save. I have removed the close and on the workbook I can see the data has been copied, but I think the customsave routine then doesn't actually save the book and so when it closes the copied data is lost.

This is the code I think affects the save but I can't work out how to negotiate the custom save when the updater runs?

With MasterBk
    .Save
    .Close
End With

this is the code I can't remember where I found it, but it is required for disabling cut copy paste and a few other things

 Sub CustomSave(Optional SaveAs As Boolean)
    Dim ws As Worksheet, aWs As Worksheet, newFname As String
     'Turn off screen flashing
    Application.ScreenUpdating = False
     
     'Record active worksheet
    Set aWs = ActiveSheet
     
     'Hide all sheets
    Call HideAllSheets
     
     'Save workbook directly or prompt for saveas filename
    If SaveAs = True Then
        newFname = Application.GetSaveAsFilename( _
        fileFilter:="Excel Files (*.xls), *.xls")
        If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
    Else
        ThisWorkbook.Save
    End If
     
     'Restore file to where user was
    Call ShowAllSheets
    aWs.Activate
     
     'Restore screen updates
    Application.ScreenUpdating = True
End Sub

Option Explicit
 
Const WelcomePage = "macros"
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    Call ToggleCutCopyAndPaste(True)
    
     'Turn off events to prevent unwanted loops
    Application.EnableEvents = False
     
     'Evaluate if workbook is saved and emulate default propmts
    With ThisWorkbook
        If Not .Saved Then
            Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
                vbYesNoCancel + vbExclamation)
            Case Is = vbYes
                 'Call customized save routine
                Call CustomSave
            Case Is = vbNo
                 'Do not save
            Case Is = vbCancel
                 'Set up procedure to cancel close
                Cancel = True
            End Select
        End If
         
         'If Cancel was clicked, turn events back on and cancel close,
         'otherwise close the workbook without saving further changes
        If Not Cancel = True Then
            .Saved = True
            Application.EnableEvents = True
            .Close savechanges:=False
        Else
            Application.EnableEvents = True
        End If
    End With
End Sub
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
     'Turn off events to prevent unwanted loops
    Application.EnableEvents = False
     
     'Call customized save routine and set workbook's saved property to true
     '(To cancel regular saving)
    Call CustomSave(SaveAsUI)
    Cancel = True
     
     'Turn events back on an set saved property to true
    Application.EnableEvents = True
    ThisWorkbook.Saved = True
End Sub