Joel, you are a genius! I had worked on this for 2 hours. I goggled,
searched, and tried different ways of copying from the one workbook to
another and couldn't get it to work. This worked!

THANK YOU so much for taking the time to help!!



"Joel" wrote:

Set variables like this to reference each workbook

Set bk1 = ThisWorkbook
Set bk2 = Workbooks.Add
Set bk3 = Workbooks.Open(Filename:="Stats_Orders.xls")


The workbook names need the XLS extension which maybe your problem

"laavista" wrote:

I'm using Excel 2003

I have code in workbook1 that
- creates workbook2, named "Orders" with a tab named "OpenOrders" (and
saves it)
- opens an existing workbook, workbook3, named "Stats_Orders" with a tab
named "Stats"

I want to copy a value from workbook2 to workbook3. Everything works until
I try to copy the data from workbook 2 to workbook 3. I get a "subscript out
of range" error.

A snippet of code:

Dim strFromFile as string
Dim strDestFile as string
Dim LastRowOrders as integer
Dim LastRowStats as integer
LastRowOrders = 3
LastRowStats = 4
strFromFile = "Orders"
strDestFile = "Stats_Orders"

Workbooks(strFromFile).Worksheets("OpenOrders").Ra nge("B" & LastRowOrders +
1).Copy Workbooks(strDestFile).Worksheets("Stats").Range(" C",
LastRowStats + 1)

Your help would be greatly appreciated!