Code:
'Please note that I recorded a macro using the standard record button in Excell to perform this very
'operation, so I could then adapt it to my own code. The macro code it gave me was
'Sheets("Summary").Copy Before:=Workbooks("FSRmonthdata.csv").Sheets(1)
Sub Modifedtest()
Application.ScreenUpdating = False
Dim xlTmpBook As Workbook
Dim xlBook As Workbook
'Get the summary sheet pointed to by the user and apply to ThisWorkbook
Set xlBook = ThisWorkbook
Set xlTmpBook = Workbooks.Open(GetFile("Select the FSR summary dashboard to use"))
'This next line is the line it gives me the error in regards, and is also the line I modified
'to do what the recorded macro did.
'NOTE: you need to check that the index number of the sheets in
'the xltmpbook & xlbook is actually = 1 (b/c I it is possible that it may not be
' even if there is only one sheet in the files)
xlTmpBook.Worksheets(1).Copy Before:=xlBook.Sheets(1)
xlTmpBook.Close
Application.ScreenUpdating = true
End Sub
Private Function GetFile(Optional HeaderMsg As String) As String
With Application.FileDialog(msoFileDialogFilePicker)
.InitialFileName = Application.DefaultFilePath
.Title = HeaderMsg
If .Show = -1 Then
GetFile = .SelectedItems(1)
Else
GetFile = ""
End If
End With
End Function
Bookmarks