Import another spreadsheet into existing workbook Excel 2010 1

[dheim70]

Hi All,
I'm a novice with VBA and Excel, I am trying to do something that I would think is simple but I can't get it to work. I have an excel (2010) spreadsheet with multiple sheets in it. I want to have a button that will import another spreadsheet from a different workbook when clicked. (the spreadsheet that needs to be imported is in Excel 2003), and is in the same directory as the main spreadsheet that it will be going to. I want to automate this as it will be a monthly thing and I don't want to have to cut and paste each time (because of accuracy concerns) Here is the code I am using:

Private Sub CommandButton1_Click()
Dim wkbSource As Workbook
Dim wkbDest As Workbook
Dim shtToCopy As Worksheet

Set wkbSource = Workbooks.Open("C:\B Drive\TestSpread\UORG0104PCLDMAY.xls")
Set wkbDest = Workbooks("C:\B Drive\TestSpread\NYCPA-May2.xlsm")
Set shtToCopy = wkbSource.Sheets("UORG0409-PCLD")
shtToCopy.Copy wkbDest.Sheets()
End Sub

I get a Runtime error '9' subscript out of range. Can anyone help or suggest different code to do this? Any help is greatly appreciated!

Tia!

 

[SkipVought]

hi,

" subscript out of range" usually means that a specified object does not exist.

Check your spelling, for instance, the sheet name.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

And it may not be necessary to do the open & copy, if your use MS Query, assuming that the data you want to import is tabular only, not graphics or formatting. Once a querytable is added to your sheet, you need only REFRESH the querytable to get new data. In other words, no VBA required.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

actually, you did not specify which sheet to place the copy before...

shtToCopy.Copy wkbDest.Sheets([highlight]1[/highlight])

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dheim70]

Hi Skip, thanks for the response,I did the debug process and I am getting that run time '9' error on the line:
Set wkbDest = Workbooks("C:\B Drive\TestSpread\NYCPA-May2.xlsm")
I double checked the file spelling and it is ok, I even copied and pasted it and the path is the same as the previous line and that seems to be ok.
Any thoughts?

Thanks again!

 

[SkipVought]

Set wkbSource = Workbooks.Open("C:\B Drive\TestSpread\UORG0104PCLDMAY.xls")
Set wkbDest = Workbooks[highlight].Open[/highlight]("C:\B Drive\TestSpread\NYCPA-May2.xlsm")

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dheim70]

Thanks Skip, got it working now