Copy data from workbook 2 to workbook 3 mystery!

[laavista]

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").Range("B" & LastRowOrders +
1).Copy Workbooks(strDestFile).Worksheets("Stats").Range("C",
LastRowStats + 1)

Your help would be greatly appreciated!

 

[Joel]

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

 

[Karan]

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!!