Copy data from workbook 2 to workbook 3 mystery!

  • Thread starter Thread starter laavista
  • Start date Start date
L

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