Importing data from another workbook causes error 9 message

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:
[VBA]

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[/VBA]

Aussiebear: Edited to place code within the tags

Thanks

Lucpian

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.

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

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:
[vba]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
[/vba]

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

[VBA]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
[/VBA]

[VBA]

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

[/VBA]

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

Lucpian

Then you probably don't have sheets named "Sheet1".

What should I replace the sheet1 with because it has to copy to sheet1.

Thanks

Lucpian

If the Sheet you are referring to as "Sheet1" is the first Sheet, use the following in your original code
[vba]With NewBook.Sheets(1)[/vba]

instead of
With NewBook.Sheets("Sheet1")
write
With NewBook.Sheets(1)

Thanks, it works fine with that line of code changed.

Lucpian

This is not selecting a resticted range but 6000 rows.
[VBA]With NewBook.Sheets("Sheet1")
.Range(.Range("A1:Y6000"), .Cells("65536").End(xlUp)).Copy
End With
[/VBA]

Threads merged

My thread is merged but no solution provded.

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...:)

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?

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.