Solved: Copying data from another workbook which name is unknown

[NomalAnomaly]

Hi,

My situation is as follows:

I have 2 open workbooks and I'd like to copy data from one workbook to the other workbook. Assuming that I don't know the name of the workbook from which I want to copy the data, is there a simple way to do this?

Thanks!

[GTO]

Which is copying to which?

[NomalAnomaly]

Copy data from the workbook which name is unknown, to the workbook which name is known.

[lynnnow]

Why don't you try opening the unknown workbook from the known workbook and then the file name will be "known" to VBA

[NomalAnomaly]

How can I open it if I can't refer to it by its name?

[lynnnow]

Use the FileOpenDialog box to select the file and then transfer that value to a variable. Then VBA will know the name of the file.

[GTO]

Copy data from the workbook which name is unknown, to the workbook which name is known.

Okay, here is a very simple example. We could use a userform (see attached) with a listbox to list any other open workbooks.

The userform and code goes in the 'known' wb.

Option Explicit
    
Private Sub cmdCancel_Click()
    Unload Me
End Sub
    
Private Sub cmdOK_Click()
Dim wb As Workbook
    
    Set wb = Workbooks(lstOpenWBs.Value)
    
    'do whatever copying
    wb.Worksheets(1).Copy _
        After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    Unload Me
End Sub
    
Private Sub UserForm_Initialize()
Dim wb As Workbook
    
    cmdOK.Enabled = False
    
    If Workbooks.Count > 1 Then
        For Each wb In Workbooks
            If Not wb.Name = ThisWorkbook.Name Then
                lstOpenWBs.AddItem wb.Name
            End If
        Next
        lstOpenWBs.ListIndex = 0
        cmdOK.Enabled = True
    Else
        Me.Caption = "No Other Workbooks Open!"
    End If
End Sub

Hope that helps,

Mark

[NomalAnomaly]

Hi Mark

Thank you very much for your reply.

The above code will certainly do it.

NormalAnomaly

[GTO]

Glad to help :-)