How do I use Excel VBA to copy a worksheet from a workbook selected using a file browser?

Question

I'm trying to create what I thought should be a really simple Macro. I deal with the same report every day, which is updated each morning. That said, it would be really nice for me to be able to have the data I worked with yesterday in the same workbook as the new data from a given morning.

Basically, all I want to do is copy a worksheet from a different workbook (the one with yesterday's report) into my active workbook (the one with today's report) using a file browser.

The Application.GetOpenFilename method opens up a file browser like what I want, but I can't figure out how to use the directory string that it returns to copy in the worksheet I want.

Here's the code I've been using:

Sub Macro5()

Dim todayWBName As String
Dim yesterdayWB As Workbook
Set todayWB = ActiveWorkbook
todayWBName = todayWB.Name

'open file from last business day
    yesterdayWBName = Application.GetOpenFilename( _
                      Title:="Select backorder file from last business day", _
                      MultiSelect:=False)
    If yesterdayWBName = False Then
        Exit Sub
    Else
        End If
    Workbooks.Open yesterdayWBName
    Set yesterdayWB = ActiveWorkbook
    todayWB.Activate

'copy in yesterday's info
    yesterdayWB(1).Copy After:=todayWB.Sheets(1)
    yesterdayWB.Close
    Sheets("Sheet 1 (2)").Name = "YesterdayResolution"
    Sheets(1).Activate

End Sub

If anyone could tell me what I'm doing wrong here, I'd really appreciate it.

Thanks in advance.

score 2 · Accepted Answer · answered Mar 22 '17 at 13:14

Try this. Use the string result from the GetOpenFileName and pass it directly to the Workbooks.Open method, handling the return value as an object (workbook) which assigns to yesterdayWB object.

Also, you have an error:

yesterdayWB(1).Copy After:=todayWB.Sheets(1)

The workbook object is not subscriptable, what you meant (I think) was yesterdayWB.Worksheets(1).Copy ...

Sub Macro5()

    Dim yesterdayWB As Workbook
    Dim yesterdayWBName As String
    Dim todayWB As Workbook

    Set todayWB = ActiveWorkbook

    'open file from last business day
    yesterdayWBName = Application.GetOpenFilename( _
                      Title:="Select backorder file from last business day", _
                      MultiSelect:=False)
    If yesterdayWBName = False Then
        Exit Sub
    End If
    Set yesterdayWB = Workbooks.Open(yesterdayWBName)

    'copy in yesterday's info
    yesterdayWB.Worksheets(1).Copy After:=todayWB.Sheets(1)
    yesterdayWB.Close
    ' the sheet you copied is at index "2" because you put it after Sheets(1)
    todayWB.Sheets(2).Name = "YesterdayResolution"
    todayWB.Sheets(1).Activate

End Sub

How do I use Excel VBA to copy a worksheet from a workbook selected using a file browser?

1 Answers1