Switching between workbooks

Good evening,

I am trying to import data from one workbook into another but it is not working. Any ideas on what is happening? This is something I am piecing together from other posts but I have hit a snag.

Sub Updating()
Dim ImportFileName as Variant
Dim CFileNeams as Variant

    CFileName = ActiveWorkbook.Name
    MainMenu.Hide

    spath = CurDir
    ChDrive ThisWorkbook.Path
    ChDir ThisWorkbook.Path & "\Import File"

    ImportFileName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls),*.xls", MultiSelect:=False)

    If ImportFileName = False Then
        MsgBox "Error"
        MainMenu.Show
    Else
        Workbooks.Open Filename:=ImportFileName
    End If

ImportFileName = ActiveWorkbook.Name
    Application.WindowState = xlMinimized
    Windows(ImportFileName).Activate
        Sheets("Setup1").Select
        Cells.Copy
    Windows(CFileName).Activate
        Sheets("Setup").Select
        Range("A1").PasteSpecial Paste:=xlPasteValues
        
    Application.WindowState = xlMaximized
End Sub

It works up until Windows(CFileName).activate
I am not sure what is happening here as I have checked, using a message box, and CFileName still has the correct file name. For some reason excel will not switch to the other file. If I switch this to Windows("Workbooks name.xls").activate the code works.

Any ideas? As always, thank you in advance for your help!
tc

hi TC,
You may have solved your problem by now but here is a revised macro with some possible changes...

Option Explicit
Sub ModifiedUpdating()
Application.ScreenUpdating = False
Dim ImportFileName As Variant
Dim ImportFile As Workbook
Dim ValuesToCopy As Range
Dim MainMenu
    
    MainMenu.Hide 'do you want this shown again at the end of the macro?
    ChDrive ThisWorkbook.Path
    ChDir ThisWorkbook.Path & "\Import File"

    ImportFileName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls),*.xls", MultiSelect:=False)
Select Case ImportFileName
    Case Is = False
        MsgBox "Error"
    Case Else
        Set ImportFile = Workbooks.Open(ImportFileName)
        
        ''option 1 - may be slightly quicker than option 2 due to the limiting of _
the range that is to be changed?
            'Set ValuesToCopy = ImportFile.Sheets("Setup1").UsedRange
            'With ThisWorkbook.Sheets("Setup")
                '.Cells.ClearContents 'to remove any previous values. _Is this needed?
                '.Range(ValuesToCopy.Address).Value = ValuesToCopy
            'End With
        
        ''option 2
            ImportFile.Sheets("Setup1").Cells.Copy
            ThisWorkbook.Sheets("Setup").Range("A1").PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
End Select

'added in as possible actions
MainMenu.Show
ImportFile.Close False

'to free memory & refresh the screen
Set ImportFile = Nothing
Set ValuesToCopy = Nothing
Application.ScreenUpdating = True
End Sub

hth
Rob

HI TC,

Thanks for the feedback, I'm pleased I could help :-)

I used Select Case b/c I've got into the habit of using it rather than If statements recently - I've read in various places (*) that the Select Case approach can be quicker b/c:
it only evaluates the first case which is true (therefore try & place the most common "Case" first in the list), completes the action, & then ends the Select compared with an If statement where both the True & the False "checks" (for want of a better word) are evaluated every time.

The impact isn't that great in a situation such as this where there are only 2 possible options which are only being used once but can add up if you are using an If-Then-If-Then... structure or looping repeatedly through the code.

One looking at the code I gave you it would probably be better around the other way to place the most common option first ie:

Select Case ImportFileName
    Case Is <> False
        Set ImportFile = Workbooks.Open(ImportFileName)
        ''option 2
            ImportFile.Sheets("Setup1").Cells.Copy
            ThisWorkbook.Sheets("Setup").Range("A1").PasteSpecial _
Paste:=xlPasteValues
            Application.CutCopyMode = False
    Case Else
        MsgBox "Error"
End Select

* not that I can find them again! anyway, see below for 2 links - when googling I found these but couldn't find the ones I've seen in the past:

HTML Code: 

HTML Code: 

(esp the links at the base of this page)

For general ss tips you may find the LH tab ("Excel Pages") on the following link to contain some useful background:

HTML Code: 

hth
Rob