File does not open/copy
 

I am using Excel 2000.

I have an Excel file open. Using a macro, I am opening
another Excel file and then copying the first sheet from
that file to a sheet in the original file.

Everything works fine after a file has been copied once.
Problem: The first file to copy will not copy. The macro
must be run twice to work. It works fine for all file
after that. Here is part of the code I am using:
...................
Dim Current_Sheet As String, Current_Cell As String
Current_Sheet = ActiveSheet.Name
Current_Cell = ActiveCell.Address
Sheets(1).Select
Application.DisplayAlerts = False
Dim Temp_name As Variant, Newbook As Variant, fname As
Variant, Book As Variant
Temp_name = ActiveWorkbook.Name
Dim FileName As String
Dim PathName As Variant

'Key area of code...........
fname = Application.GetOpenFilename("Excel Files
(*.xls), *.xls", , "Load the BIP Data File from the MR&D
Data Warehouse")
If fname < False Then
Workbooks.Open FileName:=fname
Else
Sheets(Current_Sheet).Select
Range(Current_Cell).Select
End
End If
Sheets(1).Select
Cells.Select
Selection.Copy
Workbooks(1).Activate
Sheets(1).Select
Cells.Select
ActiveSheet.Paste
........................

Suggestions?

File does not open/copy
 

It seems the problem of sequence. If I am right, you try
to copy from workbook fname to workbook Temp_name. Try the
following one.

Dim Current_Sheet As String, Current_Cell As String
Current_Sheet = ActiveSheet.Name
Current_Cell = ActiveCell.Address
Sheets(Current_Sheet).Select
Application.DisplayAlerts = False
Dim Temp_name As Variant, Newbook As Variant, fname As
Variant, Book As Variant
Temp_name = ActiveWorkbook.Name
Dim FileName As String
Dim PathName As Variant

'Key area of code...........
fname = Application.GetOpenFilename("Excel Files
(*.xls), *.xls", , "Load the BIP Data File from the MR&D
Data Warehouse")
If fname < False Then
Workbooks.Open FileName:=fname
End If

workbooks(fname).activate
Sheets(1).Select
Cells.Select
Selection.Copy
Workbooks(Temp_name).Activate
Sheets(Current_Sheet).Select
Cells.Select
ActiveSheet.Paste

Best Regards

Bill

-----Original Message-----
I am using Excel 2000.

I have an Excel file open. Using a macro, I am opening
another Excel file and then copying the first sheet from
that file to a sheet in the original file.

Everything works fine after a file has been copied once.
Problem: The first file to copy will not copy. The macro
must be run twice to work. It works fine for all file
after that. Here is part of the code I am using:
...................
Dim Current_Sheet As String, Current_Cell As String
Current_Sheet = ActiveSheet.Name
Current_Cell = ActiveCell.Address
Sheets(1).Select
Application.DisplayAlerts = False
Dim Temp_name As Variant, Newbook As Variant, fname As
Variant, Book As Variant
Temp_name = ActiveWorkbook.Name
Dim FileName As String
Dim PathName As Variant

'Key area of code...........
fname = Application.GetOpenFilename("Excel Files
(*.xls), *.xls", , "Load the BIP Data File from the MR&D
Data Warehouse")
If fname < False Then
Workbooks.Open FileName:=fname
Else
Sheets(Current_Sheet).Select
Range(Current_Cell).Select
End
End If
Sheets(1).Select
Cells.Select
Selection.Copy
Workbooks(1).Activate
Sheets(1).Select
Cells.Select
ActiveSheet.Paste
........................

Suggestions?
.