Userform reloading when I VBA copy a worksheet from another workbook

[PassSystems]

Hi All

Hopefully one of you awesome excel magicians can help...

I am generating a spreadsheet and have noticed an odd thing happening which has me at a current standstill. The spreadsheet utilises UserForm's and I have noticed that when I import worksheets from another workbook the current workbook will reload when the first worksheet import occurs. I then have to re-load the userform again to import the next worksheet and so no.

I am running a For Next to import all worksheets from an external workbook so it should just go ahead and continue until all worksheets are imported. I think what is happening is the spreadsheet is re-initializing / re-activating once each worksheet is imported and flicking back to the ThisWorkbook Activate module which reloads the starting UserForm that pops up when the workbook is activated.

I don't think the code is the issue but have included it below.
___________________________________________________________________________________________________________________________________________________
ThisWorkbook start up module, couldn't get any simpler

Private Sub Workbook_Activate()

Application.Visible = False

WelcomeSplash.Show
TheHub.Show

End Sub
__________________________________________________________________________________________________________________________________________________
Module for the selection of the client data file and the import of the worksheets in the file.

Private Sub DataFile_Click()

Application.ScreenUpdating = False

Unload TheHub

Dim CurrentWorkbook As Workbook
Dim SourceWorkbook As Workbook
Dim SourceWorksheet As Worksheet
Dim FileName As Variant
Dim FileNameFull As Variant
Set CurrentWorkbook = ThisWorkbook


' Allows the user to select the client data file name, with path and saves it to the cell "B4" in the "Data" worksheet
' Also sets the Me.TextFullFile.Value as the client file name with path information.

FileNameFull = Application.GetOpenFilename(Filefilter:="Excel Files,*.xls*")

If FileNameFull = "False" Then
Me.Text_FileName = "Select Data File Before Continuing"
Else
Me.TextFullFile.Value = FileNameFull
CurrentWorkbook.Sheets("Data").Range("B4").Value = Me.TextFullFile.Value
End If

' Removes the path details and saves the file name only it to the cell "B3" in the "Data" worksheet
' Also sets the Me.Text_FileName.Value as the client file name without path information.

FileName = Mid(FileNameFull, InStrRev(FileNameFull, "\") + 1)

If FileName = "False" Then
Me.Text_FileName = "Select Data File Before Continuing"
Else
Me.Text_FileName.Value = FileName

CurrentWorkbook.Sheets("Data").Range("B3").Value = Me.Text_FileName.Value
End If

' Copies the "Appliance Test Data", "Front Page" and "Site Information" worksheets to the AssetPass - Plus workbook

Set SourceWorkbook = Workbooks.Open(FileNameFull)
Set SourceWorkbook = Workbooks(FileName)

For Each SourceWorksheet In SourceWorkbook.Sheets
SourceWorksheet.Copy after:=CurrentWorkbook.Sheets(CurrentWorkbook.Sheets.Count)
Next

' Closes the client file and saves AssetPass - Plus

SourceWorkbook.Close SaveChanges:=False
ThisWorkbook.Save


TheHub.Show

Application.ScreenUpdating = True

End Sub


Your help would be greatly appreciated....

 

[PassSystems]

All Good, I worked out that I had the ThisWorkbook module set to Activate instead of Open.... D'OH!