Userform reloading when I VBA copy a worksheet from another workbook

PassSystems

New Member
Joined
Sep 19, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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....
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

PassSystems

New Member
Joined
Sep 19, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
All Good, I worked out that I had the ThisWorkbook module set to Activate instead of Open.... D'OH!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,536
Messages
5,548,626
Members
410,859
Latest member
RamIndia
Top