Duplicated sheet's name after re-ran the workbook macro

[hamdan85]

Hi all,

I am a newbie into VBA. My boss asked me to create a macro which will import all the worksheets from different workbooks into one workbook.

I created the following 2 scenarios to presented to him,
Scenario 1; a window will pop up and ask me to select the workbook/s which I want to import to this new workbook, below is the code for it

#Sub mergeFiles()#
'Define variables:
#Dim numberOfFilesChosen, i As Integer#
#Dim tempFileDialog As FileDialog#
#Dim mainWorkbook, sourceWorkbook As Workbook#
#Dim tempWorkSheet As Worksheet##

#Set mainWorkbook = Application.ActiveWorkbook#
#Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker) #

'Allow the user to select multiple workbooks
#tempFileDialog.AllowMultiSelect = True#

#numberOfFilesChosen = tempFileDialog.Show#

'Loop through all selected workbooks
#For i = 1 To tempFileDialog.SelectedItems.Count#

'Open each workbook
#Workbooks.Open tempFileDialog.SelectedItems(i) #

#Set sourceWorkbook = ActiveWorkbook#

'Copy each worksheet to the end of the main workbook
# For Each tempWorkSheet In sourceWorkbook.Worksheets#
#tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count) #
#Next tempWorkSheet#

'Close the source workbook
#sourceWorkbook.Close#
#Next i#

#End Sub#

Scenario 2, the workbook’s sheets will be pulled automatically from a specified bath and brought them all to the new workbook, below is the code for it

#Sub CombineWorkbooks()#

#Dim Path As String#
#Path = "C:\Users \Desktop\Example\"#

#Dim FileName As String#
#FileName = Dir(Path & "*.xlsx")#

#Dim ws As Worksheet#

#Application.ScreenUpdating = False#
#Application.DisplayAlerts = False#

#Do While FileName <> ""#
#Workbooks.Open Path & FileName#
#For Each ws In ActiveWorkbook.Sheets#
#ws.Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) #
#Next ws#
#Workbooks(FileName).Close#
#FileName = Dir()#
#Loop#

#Application.ScreenUpdating = False#
#Application.DisplayAlerts = False#

#End Sub#

The problem is that every time I ran the macro it pulls the data and put it in a new worksheet.
What I want is that every time I ran the macro the existing sheets will be updated.

Thank you so much for your help in advance

[alansidman]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)