Copying tab from one workbook to another without Excel requesting the Path of the File

[shade2189]

Dear Excel Forum,

The is the line of code I am using to copy the tab "PE Master" to the workbook I am currently in.

Workbooks("Profit Template.xlsm").Worksheets("PE Master").Range("A1:K200").Copy Destination:=Range("A1")

When I run the code, Excel opens a window and is looking for me to select the file "Profit Template.xlsm". After I select the file, It then copies the tab "PE Master" into the file I am in. How can I have this process done without Excel expecting me to select the file. I have used similar code before in other Macros and never had any problems (i.e., it copies without requesting that I select the file).

Also, when I copied the full path of the file: Workbooks("G:\BusUnits\Finance\USPR\Work Folder\Profit Template.xlsm").Worksheets("PE Master").Range("A1:K200").Copy Destination:=Range("A1")

I get a subscript out of error 9 message.

I've looked at it and can't figure out what is going on. Any help would be greatly appreciated.

 

[rlv01]

You have to open the workbook first.

 Workbooks.Open [COLOR=#333333]"G:\BusUnits\Finance\USPR\Work Folder\Profit Template.xlsm"[/COLOR]

Here's an example:

Sub Test()
    Dim FName As String
    Dim SrcWB As Workbook    'Source workbook
    Dim DestWB As Workbook    'Destination workbook

    For Each SrcWB In Application.Workbooks
        If SrcWB.Name = "Profit Template.xlsm" Then
            Exit For    'workbook is already open, no need to open it
        End If
        Set SrcWB = Nothing
    Next SrcWB

    If SrcWB Is Nothing Then    'workbook is not open, so open it.
        FName = "G:\BusUnits\Finance\USPR\Work Folder\Profit Template.xlsm"
        Workbooks.Open FName
        Set SrcWB = ActiveWorkbook
    End If

    Set DestWB = ThisWorkbook

    SrcWB.Worksheets("PE Master").Copy After:=DestWB.Sheets(1)    'Copy the sheet to another workbook
    SrcWB.Close False    'close workbook
End Sub

 

[shade2189]

Thanks for the quick reply. Your response was very helpful.