Using VBA to Open A Changing File Name

Contributor

Hello,

 

I am writing a code that will open a SharePoint path, I will select the file I want to open (the name changes). That is where I get stuck, I want the code to copy a range from the changing name file and paste it into another set file name. I just don't know how to determine what the code is for the changing file name and how to get the code to continue once I open it.

 

In red is where I need the code to go for the changing file name. It needs to continue once I open the file. I have used this code in the past, but it was with a C: Drive file and it continued once I opened the file.

This is what I have (I changed the name of the SharePoint file as it is a work file):

 

Sub Audit_Sheet()
'
' Audit_Sheet_Data Macro
'

'
Sheets("Audit Sheets").Select
Columns("B:N").Select
Selection.ClearContents

ActiveWorkbook.FollowHyperlink Address:="SharePoint.file"
filetoopen = ThisWorkbook.Worksheets("Audit Sheets").Range("A1")

ActiveWorkbook.Select
Sheets("Audit Sheets").Select
Columns("B:N").Select
Selection.Copy


Windows("Voice and Season Training - Official.xlsm").Activate
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save

MsgBox ("Run Complete")

End Sub

 

Thanks in advance.

0 Replies