List all open Excel files so user may select a file

Question

I want to copy data from an old file to a new file.

I have code that copies my data. The name of the old file and new file changes and I have to change that in my code every time.

Is there a way, I can get a list of open Excel files and select the file from which I want the data and the file to which I want to copy the data?

Sub Copy_rental()

Dim PYFILE As String
Dim CYFILE As String
Dim Columnstocopy As String

PYFILE = "Pro-invest HIE ADL Trust -  2018 WP.XLSX"
CYFILE = "Pro-invest HIE ADL Trust -  2019 WP.XLSX"

Columnstocopy = "C"

Windows(PYFILE).Activate
Sheets("Rental Schedule").Select
Range(Columnstocopy & "10:" & Columnstocopy & "14").Select
Selection.Copy

Windows(CYFILE).Activate
Sheets("Rental Schedule").Select
Range(Columnstocopy & "10").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Windows(PYFILE).Activate
Sheets("Rental Schedule").Select
Range(Columnstocopy & "16:" & Columnstocopy & "24").Select
Selection.Copy

Windows(CYFILE).Activate
Sheets("Rental Schedule").Select
Range(Columnstocopy & "16").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Windows(PYFILE).Activate
Sheets("Rental Schedule").Select
Range(Columnstocopy & "26:" & Columnstocopy & "27").Select
Selection.Copy

Windows(CYFILE).Activate
Sheets("Rental Schedule").Select
Range(Columnstocopy & "26").Select
ActiveSheet.Paste
Application.CutCopyMode = False
   
Windows(PYFILE).Activate
Sheets("Rental Schedule").Select
Range(Columnstocopy & "29").Select
Selection.Copy

Windows(CYFILE).Activate
Sheets("Rental Schedule").Select
Range(Columnstocopy & "29").Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

EDIT: Thanks to the tip I am using this code to get the list of the open Excel files but it does not appears to working. VBA is also not throwing any error. Could this be because I have made the Userform on my personal.xlsb workbook?

The same code in the newblankworkbook it appears to be working.

Private Sub Onesource_copy_data_Initialize()

Dim wkb As Workbook

Me.Onesource_ComboBox1_oldfile.Clear
For Each wkb In Application.Workbooks
    Me.Onesource_ComboBox1_oldfile.AddItem wkb.Name
Next wkb

Me.Onesource_ComboBox1_newfile.Clear
For Each wkb In Application.Workbooks
    Me.Onesource_ComboBox1_oldfile.AddItem wkb.Name
Next wkb

End Sub

EDIT 2: I think the issue was Onesource_copy_data_Initialize. I should have left it as UserForm_Initialize.

Private Sub UserForm_Initialize()

Dim wkb As Workbook

Me.Onesource_ComboBox_oldfile.Clear
For Each wkb In Application.Workbooks
    Me.Onesource_ComboBox_oldfile.AddItem wkb.Name
Next wkb

Me.Onesource_ComboBox_newfile.Clear
For Each wkb In Application.Workbooks
    Me.Onesource_ComboBox_newfile.AddItem wkb.Name
Next wkb

End Sub

EDIT3: I have made a combobox and it is working.

Code for Combobox

Private Sub Onesource_Copy_Cancel_CommandButton_Click()
Stopped = True
Unload Me
End Sub


Public Sub Onesource_Copy_Start_CommandButton_Click()

PYFILE = Me.Onesource_ComboBox_oldfile.Value
CYFILE = Me.Onesource_ComboBox_newfile.Value
' Calling Macro
Call Test123

Unload Me
End Sub


Private Sub UserForm_Initialize()

Dim wkb As Workbook

Me.Onesource_ComboBox_oldfile.Clear
For Each wkb In Application.Workbooks
    Me.Onesource_ComboBox_oldfile.AddItem wkb.Name
Next wkb

Me.Onesource_ComboBox_newfile.Clear
For Each wkb In Application.Workbooks
    Me.Onesource_ComboBox_newfile.AddItem wkb.Name
Next wkb

End Sub

Start of the Excel code

Declaring the variable as public as this would be required

Public PYFILE As String

Public CYFILE As String

Sub Copy_PreTemplate_to_NewTemplate()
'
' Copy previous Template data to new Template
'
Onesource_Copy_form.Show

End Sub


Sub Test123()

Windows(PYFILE).Activate
Sheets("Non-numeric details").Select
Range("C6:C10").Select
Selection.Copy
Windows(CYFILE).Activate
Sheets("Non-numeric details").Select
Range("C6").Select
ActiveSheet.Paste

End sub

Answer 1

Try the next way, please:

Sub testSelectOpenWorkbook()
  Dim w As Workbook, wbCopy As Workbook, wbDest As Workbook, i As Long, strWb As String
  Dim chooseW As String
  
  For i = 1 To Workbooks.Count
    strWb = strWb & i & " - " & Workbooks(i).Name & vbCrLf
  Next
   chooseW = InputBox("Please choose the number in front of the Workbook to copy data" & vbCrLf & vbCrLf & strWb, _
            "Workbook to copy data selection", 1)
   If chooseW = "" Then MsgBox "You did not choose anything...": Exit Sub
   If Not IsNumeric(chooseW) Then
      MsgBox "You must write the number in front of the workbook name...":Exit Sub
   End If
   Set wbCopy = Workbooks(CLng(chooseW))
   Debug.Print wbCopy.Sheets.Count, wbCopy.Name
   
   'Please, proceed in exactly the same way to select the destination workbook
End Sub

Answer 2

You will need to create userform for such a selection. Another option would be to use InputBox method. Here is a demonstration code.

Dim wkbSrc As Workbook, wkbDst As Workbook
Dim rngSrc As Range, rngDst As Range
Set rngSrc = Application.InputBox("Select Source Workbook", "Select Workbook", , , , , , 8)
Set rngDst = Application.InputBox("Select Destination Workbook", "Select Workbook", , , , , , 8)
Set wkbSrc = rngSrc.Parent.Parent
Set wkbDst = rngDst.Parent.Parent

Answer 3

If the sheet don't change position you can use their numeric indexes instead of sheet names:

Sheets(0).Select

If the sheets do change position iterate through all sheets looking for certain text to indicate which sheet is what.

Otherwise maintain a map of the possible names.

This isn't really a programming problem, it's a problem around the process. A better solution is don't rename the sheets or use a Naming Convention such as sheetName-YYYYMMDD.