CODEWITHSUNDEEP
vbaexcel
bmyers
bmyers

Reputation: 49

VBA Opening File error

I have a macro on my workbook named CTAttendanceProgram. In my code, I am attempting to open an additional workbook using the browse function. I would like to place Sheet1 of this additional file after the sheet "Employee Data" in the original "CTAttendanceProgram". Here is my code so far:

Dim fn
fn = Application.GetOpenFilename
If fn = False Then
    MsgBox "Nothing Chosen"
    Exit Sub
Else
    Workbooks.Open (fn)
    Workbooks("CTAttendanceProgram.xlsm").Activate
End If

Application.DisplayAlerts = False
Worksheets("Recent Attendance").Delete
Application.DisplayAlerts = Trueppl

Workbooks(fn).Sheets(1).Copy After:=Workbooks("CTAttendanceProgram.xlsm").Sheets("Employee Data")
Sheets(4).Name = "Recent Attendance"

Previously, I was using this code to open a pre-determined file and it worked just fine. However, now that I'm using the browse function (yet still opening the same file), I am running into an error on the "Workbooks(fn).Sheet(1).Copy..." (second to last) line). Any tips/suggestions? Thanks in advance for any support.

Upvotes: 1

Views: 751

Answers (1)

DragonSamu
DragonSamu

Reputation: 1163

because fn is used to define the File path & File name it can not be used as a workbook.

do the following:

Dim fn
Dim wb As Workbook

fn = Application.GetOpenFilename
If fn = False Then
    MsgBox "Nothing Chosen"
    Exit Sub
Else
    Workbooks.Open (fn)
    Set wb = ActiveWorkbook
    Workbooks("CTAttendanceProgram.xlsm").Activate
    Application.DisplayAlerts = False
    Worksheets("Recent Attendance").Delete
    Application.DisplayAlerts = True
    wb.Sheets(1).Copy After:=Workbooks("CTAttendanceProgram.xlsm").Sheets("Employee Data")
    Sheets(4).Name = "Recent Attendance"
    wb.Close False
End If

Upvotes: 1

Related Questions