Copy from one workbook to another failing

[Shazbot79]

I have copied this code pretty much entirely from another post but it
throws up Run time error 9 subscript out of range'. Can anyone help?
Thanks


Public Sub ImportData()
Dim myFilename As Variant
Dim importbook As Workbook
Dim mainbook As Workbook



MsgBox "Please select the Do Not Trace workbook you wish to import."

myFilename = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
'launches prompt to select import file

Set mainbook = ActiveWorkbook

Set importbook = Workbooks.Open(Filename:=myFilename) 'sets variable
to be workbook that opens



importbook.Worksheets("sheet1").Copy _
before:=mainbook.Worksheets(1)


importbook.Close savechanges:=False 'closes the import sheet
end sub

 

[JLGWhiz]

Couldn't duplicate the error. It worked Ok for me.

 

[Jeff]

Public Sub ImportData()
Dim myFilename As String
Dim importbook As Workbook
Dim mainbook As Workbook

MsgBox "Please select the Do Not Trace workbook you wish to import."

myFilename = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
'launches prompt to select import file

Set mainbook = ActiveWorkbook

Set importbook = Workbooks.Open(Filename:=myFilename)
'sets variable to be workbook that opens


'Your looking for a worksheet named "sheet1"
'The sheet your importing likley has a differnt name.
' Try

importbook.Worksheets(1).Copy _
before:=mainbook.Worksheets(1)
'Instead

importbook.Close savechanges:=False 'closes the import sheet
End Sub

 

[Ryan H]

Most likely you don't have a sheet named "Sheet1" in your ImportBook
workbook. Check that and let me know. Hope this helps! If so, let me know,
click "YES' below.

Public Sub ImportData()

Dim MainBook As Workbook
Dim myFileName As Variant
Dim ImportBook As Workbook

MsgBox "Please select the Do Not Trace workbook you wish to import."


' launches prompt to select import file
myFileName = Application.GetOpenFilename("Excel Files (*.xls), *.xls")

Set MainBook = ActiveWorkbook

' sets variable to be workbook that opens
Set ImportBook = Workbooks.Open(Filename:=myFileName)

' copy import sheet to mainbook sheet
ImportBook.Sheets("Sheet1").Copy Before:=MainBook.Sheets(1)

' closes the import sheet
ImportBook.Close SaveChanges:=False

End Sub

 

[Shazbot79]

Thanks guys that helps me a bit.

I changed the bit of code that was failing to:

importbook.Worksheets(1).Copy _
before:=mainbook.Worksheets(1)

mainbook.Worksheets(1).Cells.Copy
With mainbook.Worksheets(2).Cells
ActiveSheet.Paste
End With

but now it doesn't fail....sadly nor does it copy, if I try to
reference the worksheet directly is fails again.

What I want to do is just copy the cells from importbook.worksheets(1)
to a named worksheet in mainbook.

Can anyone help?
Thanks

 

[JLGWhiz]

If you want to get the first sheet of each workbook that you open, then use
the sheet index instead of the sheet name:

importbook.Worksheets(1).Copy _
before:=mainbook.Worksheets(1)

If you only want to copy those sheets named Sheet1, then:

On Error GoTo ErrHndl:
importbook.Worksheets("sheet1").Copy _
before:=mainbook.Worksheets(1)

ErrHndl:
If Err.Number . 0 Then
If Err.Number = 9 Then
MsgBox "Sheet1 not found"
Err.Clear
Else
MsgBox Err.Number & " has occured, consult help file" _
& " for trappable errors."
End If
End If
On Error GoTo 0

 

[Ryan H]

This code worked fine for me. I wouldn't suggest coping entire worksheet
cells. If the worksheet is somewhat large it may run slow. Plus I added an
If...Then Statement to prevent Excel throwing an error in case the user
clicks Cancel when asked to select the import workbook. Hope this helps! If
so, let me know, click "YES" below.

Public Sub ImportData()

Dim MainBook As Workbook
Dim myFileName As Variant
Dim ImportBook As Variant

MsgBox "Please select the Do Not Trace workbook you wish to import."


' launches prompt to select import file
myFileName = Application.GetOpenFilename("Excel Files (*.xls), *.xls")

' user clicks cancel
If myFileName = False Then
MsgBox "You didn't select an import workbook.", vbExclamation
Exit Sub
End If

Set MainBook = ActiveWorkbook

' sets variable to be workbook that opens
Set ImportBook = Workbooks.Open(Filename:=myFileName)

' copy import sheet to mainbook sheet
ImportBook.Sheets(1).Copy Before:=MainBook.Sheets(1)

' closes the import sheet
ImportBook.Close SaveChanges:=False

End Sub

 

[JLGWhiz]

Then try this:

Replace this:

importbook.Worksheets(1).Copy _
before:=mainbook.Worksheets(1)

With This:


importbook.Worksheets(1).UsedRange.Copy _
mainbook.Worksheets(1).Range("A1")

 

[Shazbot79]

Thanks so much...I'm still not 100% why the code wasn't working but
I've made changes as suggested and it works fine now!
Thanks