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
Bookmarks