Prompt User to Navigate and Open File? 1

[Joycelet]

Hi

I have a spreadsheet where a macro runs the code to copy some cells - I now want the user to be prompted for the file that they want to paste the cells into - the Sheet Name in the file will always be the same and the starting cells for pasting will always be the same but the file name will be different.

I guess I need to call the Open File but I'm not out of depth!

Any help appreciated again

 

[PHV]

Have a look at the Application.GetOpenFilename and Workbooks.Open methods.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Joycelet]

Hi

Thanks for that - I have start the code but I'm struggling to get it to open the file I've specified.

The First Part works but it's falling over at

Sheets("Milestone Schedule IPSL").Select

Heres the full code - I guess I'm missing the goto file part!

    Dim FPath As Variant
    Dim FFilter As String
    
    Range("A2:AY14").Select
    Selection.Copy
    
    FFilter = "Data Files (*.XLS), *.XLS"
    FPath = Application.GetOpenFilename(FFilter, , "Select Data File")
    If FPath = False Then Exit Sub

    Sheets("Milestone Schedule IPSL").Select
    Range("B7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
End Sub

thanks for the help and pointers

 

[PHV]

...
If FPath = False Then Exit Sub
Dim WB As Workbook
Set WB = Workbooks.Open(FPath)
WB.Sheets("Milestone Schedule IPSL").Range("B7").PasteSpecial xlPasteValues
WB.Close True
Set WB = Nothing
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Joycelet]

Works wonderfully - Thank You so much for your help

Deadline met and I can now go to bed...

Thanks again