Open a new file using a dialog box pop up without specifying the path of the file

[Exceluser15]

Hello Everyone,

I need help in writing a code which does the following:

1. Open /pop up a dialog box which asks the user to select a file from the computer

2. Copy paste the data from a worksheet in another workbook (say workbook A) to this newly opened workbook

I am not the only person who will be using this macro. It is going to be used by many others.
Hence, specifying the path of the file might not be useful as it might give " Out of range " error.

Please advise.

Thank you ,
Exceluser15

 

[revcanon]

Try this:

Dim FileToOpen As String
Dim wbA, wbB As Workbook
Set wbA = ActiveWorkbook
        FileToOpen= Application.GetOpenFilename("All Excel Files (*.xl*)," & _
        "*.xl*", 1, "Select workbook...", "Open", False)
            If TypeName(FileToOpen) = "Boolean" Then
                MsgBox "No file selected. Process will end."
                Exit Sub
            End If
Set wbB = ActiveWorkbook

This establishes the first workbook as wbA, asks you to open a 2nd workbook and establishes it as wbB.

From there, you can write your code using the wbA & wbB designations for copying and pasting data:

'For example
wbA.Sheets(1).Range("A1:A5").Copy Destination:=wbB.Sheets(1).Range("A1")

 

[Exceluser15]

Thank you Revcanon!