Sub CopyPaste2() Dim Wb As Workbook ' current workbook Dim Report As Workbook ' referenced workbook Dim Ffn As String ' full file name of Report Dim Fname As String ' retrieved file name Ffn = "H:\F&O Report Instructions Macro 1-22-2014.xlsm" Set Report = GetWorkbook(Ffn) If Report Is Nothing Then MsgBox "Couldn't find the Report", vbCritical, _ "Missing workbook" Exit Sub Else Fname = Report.Range("J11").Text MsgBox Fname End If 'If Len(Fn) Then End Sub Private Function GetWorkbook(Wn As String) As Workbook Dim Wb As Workbook Dim Sp() As String Sp = Split(Wn, "\") Debug.Print Sp(UBound(Sp)) On Error Resume Next Set GetWorkbook = Workbooks(Sp(UBound(Sp))) If Err Then Set GetWorkbook = Workbooks.Open(Wn) End If End Function
|Windows 7 Share with concurrent edits(Excel)||3||31|
|How to change associated macro so it asks for folder rather than using hardcoded folder?||4||34|
|VBA Array, write each column's start position into an array||17||32|
|Most Consistent Performer||4||20|
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
15 Experts available now in Live!