Hello. I am trying to allow a user to select from a list of all Open Workbooks. Then, once selected, have all of those files combined into one workbook. Each file will contain only one worksheet named 'Sheet1' but will be renamed after all have been consolidated (I can handle that piece.) Can someone please help.
OS: Windows 7 64 Bit
Excel Version: 2013
Code I am using to create the new workbook.
--Create a new workbook to combine all files
Private Sub NewWb()
Dim NewName As String
NewName = "Client_CallMetrics_" & Format(Now(), "mmddyy")
ActiveWorkbook.SaveAs FileName:=NewName & ".xlsx"
This is the code I have to combine the workbooks that are open; however, this blindly combines all open workbooks. I need to allow the user to select which workbooks he/she would like to combine.
Private Sub CopySheets1()
Dim wkb As Workbook
Dim sWksName As String
sWksName = "Sheet1"
For Each wkb In Workbooks
If wkb.Name <> ThisWorkbook.Name Then
Set wkb = Nothing
Thank you so much for the help in advance.