PDA

View Full Version : Importing data from another workbook causes error 9 message



lucpian
04-09-2008, 01:01 PM
Hi All,

I have this code which I wrote to import data from one Excel workbook to another. It works for some amd for most it comes up with error 9, out of subscripts range. Please, I urgently need your help to fix this. Here is the code:

Sub ImportDataintoExcelTemplate()
Dim SourceBook As Workbook, NewBook As Workbook
Dim rnStart As Range, rnData As Range
Dim i As Long
Dim T As Boolean
Set SourceBook = ThisWorkbook
T = Application.Dialogs(xlDialogOpen).Show("*.xls")
If T = False Then Exit Sub
Set NewBook = ActiveWorkbook
With NewBook.Sheets("Sheet1")
.Range(.Range("A1:Z6000"), .Cells("65536").End (xlUp)).Copy
End With
Application.ScreenUpdating = True
With SourceBook.Sheets("Sheet1")
.Activate
.Range("A1:Z6000").Activate
.Paste
End With
Application.CutCopyMode = False
NewBook.Close SaveChanges:=False
End Sub

Aussiebear: Edited to place code within the tags

Thanks

Lucpian

tstav
04-09-2008, 01:17 PM
You get the error message when there is no "Sheet1" Sheet.

It would be better, as mdmackillop has already asked you to do, to click on the VBA button and THEN paste the code BETWEEN the two bracketed words that you will see.

Try it please.

tstav
04-09-2008, 01:24 PM
There is no "Sheet1" Sheet, that's why you get the error message.

Congrats for the VBA button!!!!

You double-posted you know, but I guess that happened while you were trying to get the VBA button to work...

MikeO
04-09-2008, 01:24 PM
lucpian,
There's a button that says "VBA" and it allows you to add code to your posts so that it displays in VBA editor format. Please start doing this.

See if this works for you:
Sub ImportDataintoExcelTemplate()
Dim SourceBook As Workbook, NewBook As Workbook
Dim rnStart As Range, rnData As Range
Dim i As Long
Dim T As Boolean

Set SourceBook = ThisWorkbook
T = Application.Dialogs(xlDialogOpen).Show("*.xls")
If T = False Then Exit Sub
Set NewBook = ActiveWorkbook
NewBook.Sheets("Sheet1").UsedRange.Copy
SourceBook.Sheets("Sheet1").Range("A1").Paste
Application.CutCopyMode = False
NewBook.Close SaveChanges:=False
End Sub


or if you're having problems with sheet names then:

Sub ImportDataintoExcelTemplate()
Dim SourceBook As Workbook, NewBook As Workbook
Dim rnStart As Range, rnData As Range
Dim i As Long
Dim T As Boolean

Set SourceBook = ThisWorkbook
T = Application.Dialogs(xlDialogOpen).Show("*.xls")
If T = False Then Exit Sub
Set NewBook = ActiveWorkbook
NewBook.Sheets(1).UsedRange.Copy
SourceBook.Sheets(1).Range("A1").Paste
Application.CutCopyMode = False
NewBook.Close SaveChanges:=False
End Sub

lucpian
04-09-2008, 01:31 PM
Sub ImportDataintoExcelTemplate()
Dim SourceBook As Workbook, NewBook As Workbook
Dim rnStart As Range, rnData As Range
Dim i As Long
Dim T As Boolean

Set SourceBook = ThisWorkbook
T = Application.Dialogs(xlDialogOpen).Show("*.xls")
If T = False Then Exit Sub
Set NewBook = ActiveWorkbook
NewBook.Sheets("Sheet1").UsedRange.Copy
SourceBook.Sheets("Sheet1").Range("A1").Paste
Application.CutCopyMode = False
NewBook.Close SaveChanges:=False
End Sub



Thanks so much Mike, but I still have errors at the underlined areas above in the code.

Lucpian

MikeO
04-09-2008, 01:35 PM
Then you probably don't have sheets named "Sheet1".

lucpian
04-09-2008, 01:39 PM
What should I replace the sheet1 with because it has to copy to sheet1.

Thanks

Lucpian

tstav
04-09-2008, 01:41 PM
If the Sheet you are referring to as "Sheet1" is the first Sheet, use the following in your original code
With NewBook.Sheets(1)

tstav
04-09-2008, 01:48 PM
instead of
With NewBook.Sheets("Sheet1")
write
With NewBook.Sheets(1)

lucpian
04-09-2008, 01:55 PM
Thanks, it works fine with that line of code changed.

Lucpian

mdmackillop
04-09-2008, 02:06 PM
This is not selecting a resticted range but 6000 rows.
With NewBook.Sheets("Sheet1")
.Range(.Range("A1:Y6000"), .Cells("65536").End(xlUp)).Copy
End With

mdmackillop
04-09-2008, 02:08 PM
Threads merged

sujittalukde
04-09-2008, 10:24 PM
My thread is merged but no solution provded.

tstav
04-09-2008, 11:19 PM
sujittalukde,
if you are referring to the 'Problem with codes on different machines' as not solved, I can see that rory has answered to all your questions.

In case you need something more, please use your thread and not this one. THIS, is Lucpian's territory...:)

sujittalukde
04-10-2008, 12:02 AM
Yes tstav, I was referring the 'Problem with codes on different machines'. you said that rory has answered my queries. But when I open my own link titled 'Problem with codes on different machines' , this thread is opening. Where is the solution then?

tstav
04-10-2008, 12:13 AM
Your thread works just fine. I just posted a test post to it.
Please use your thread or tell the administrators if sth puzzles you.