Importing Worksheet with same name always goes to first one imported

[mentalp]

Hi

I am new to VBA and trying to import worksheets from other workbooks. I have tried looking at forums to get an answer but can't find my example. I am using Excel 2010 (in a Cirtix environment - if that makes a difference).

I open a dialogue box to get the name of the workbook and it imports the first worksheet with no problems. The workbooks are differnt but the sheet I want o import is always the same name ("Detailed Overall") so the code is designed to change the name of the first sheet.

The problem is when I try to import another worksheet, it just imports the first one again. I have deleted the sheets and tried importing the second in the first instance but it still imports the first worksheet.

I assume it is an issue with the way I am naming/calling my workbooks.

Sub OpenWorkBook()</SPAN>
 
    Dim ws As Worksheet</SPAN>
    Dim wsData As Boolean</SPAN>
   
    wsData = False</SPAN>
   
    For Each ws In ThisWorkbook.Sheets</SPAN>
   
       If ws.Name = "Detailed Overall" Then</SPAN>
            wsData = True</SPAN>
        End If</SPAN>
       
    Next ws</SPAN>
   
    If wsData = True Then</SPAN>
        wsName = " Original for " & Sheets("Detailed Overall").Range("B10").Value</SPAN>
       
            Sheets("Detailed Overall").Name = wsName</SPAN>
   
    End If</SPAN>
   
    myFilePath = Application.GetOpenFilename()</SPAN>
   
    'if user cancels</SPAN>
   
    If myFilePath = False Then</SPAN>
        Target = ""</SPAN>
   
    Else</SPAN>
        Target = myFilePath</SPAN>
   
    End If</SPAN>
   
    Workbooks.Open Filename:=myFilePath, UpdateLinks:=3</SPAN>
    wbName = ActiveWorkbook.Name</SPAN>
   
   
    Sheets("Detailed Overall").Select</SPAN>
    Sheets("Detailed Overall").Copy After:=ThisWorkbook.Sheets(1)</SPAN>
    Windows(wbName).Activate</SPAN>
    ActiveWindow.Close</SPAN>
   
Me.Select</SPAN>
   
   
End Sub

</SPAN>
Any assisatnce is greatly appreciated.

Thanks
Ewan

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

My compiler did not like the "Me.Select" command so I removed it.

Apart from that everything seemed to work. So I don't know what to say.

I created a workbook with the macro in it which just had a worksheet called Sheet1.
Then I created four more called DO1-4. They all had a number between 1 and 4 in A1 and the filename (e.g. DO1) in B10.
When I ran the macro I ended up with a workbook with Sheet1 and Detailed Overall worksheets.
Running it again resulted in Sheet1, Detailed Overall and Original for DO1. The Detailed Overall worksheet had the figure "2" in it indicating it was the second workbook.