Copy worksheet from one Workbook to another

[gototcm]

This seems to simple I feel dumb to not be able to figure it out.

I have an open workbook (call it Final) with 2 WS. I want to open a second WB, called File1 and copy its only worksheet as the first WS in the WB Final. I have tried the code below and get an error 424 (object required) for the line SET WBDest. I can't figure out how to correct it. Also, is the last line in the code what I need to paste the WS as the 1st WS in Final?

The reason Fpath and Fname are separate is that Fname will change each week. The particular name will come from User input.

#Sub get_files()

' This procedure opens the workbook ("File1"), copies its only worksheet ("CG Sht") and pastes it as the 1st
' Worksheet into the Workbook ("Final") which contains 2 worksheets. Final is already open.

Dim WBSource As Workbook
Dim WBDest As Workbook
Dim Sht_To_Copy As Worksheet
Dim FilePath As String
Dim FileName As String

' setup Destination WB
FilePath = "C:\users\myname\documents\" 'set path to correct folder
FileName = "final"
Set WBDest = FPath & FName & ".xlsm"

' Open source workbook, select sheet and copy it.
Set WBSource = Workbooks.Open("C:\users\myname\documents\File1.xlsm")
WBSource.Sheets("cg Sht").Copy
Workbooks(WBDest).Activate

' Now paste it as the 1st worksheet
ActiveSheet.Paste

End Sub#

[Norie]

Why aren't you using Workbooks.Open for the destination workbook?

PS Can you add code tags?