I believe I have going in around circles. Being a novice in macros I am trying to get some help from the experts. The goal is to copy data from the latest file kept on SharePoint. I am using Excel 2013.

1) Open the latest file from SharePoint based on the latest modification date. I have another macro to find the name of the latest file which I keep in cell W6 .
2) Select the data in a fixed worksheet in this file (Origin)
3) Copy to the data to the destination file which is kept on the server (And has a drive letter).
4) Close the SharePoint file (Did not get to that point)

My macro, made up of copy/paste of what I could find, is able to do 1) and 2). The issue is that it does not activate the destination file, so it stops at 3). So how can I solve this? And if anybody has a good idea to do 4), please let me know.

Many thanks in advance.

Best regards,


Caspar


Sub Import_outstanding()

Dim wkbMyWorkbook As Workbook
Dim wkbWebWorkbook As Workbook
Dim wksWebWorkSheet As Worksheet

Set wkbMyWorkbook = ActiveWorkbook

Dim strUrl As String
Dim MyFile As String

strUrl = "//team/DavWWWRoot/sites/Shared Documents/"
MyFile = Dir(strUrl & Worksheets("List").Range("W6").Value, vbNormal)

Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Application.AskToUpdateLinks = False
'IgnoreReadOnlyRecommended = False

Workbooks.Open strUrl & MyFile, True, UpdateLinks = 3, ReadOnly = False, Notify = False
ActiveWorkbook.Activate
ActiveWorkbook.LockServerFile

Sheets("Origin").Range("A1:G500").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Destination.xlsm").Activate
Sheets("Pastehere").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("List").Activate

Application.DisplayAlerts = True
Application.ScreenUpdating = True
'IgnoreReadOnlyRecommended = True
'Application.AskToUpdateLinks = True

End Sub