Open specific folder, select file to open and copy then paste

Hi Guys

Been bangin head on wall with this one. I want to open explorer to a specific folder, select a file then copy data from a specific range and paste the data to a specific range in the current work book. Sounds easy

I've tried heaps of code from the web but no winners. My vba is in its infancy so I might have been able to modify some if I had a bit more experiance - unfortunately I got cranky and dumped all the code I've tried so I can't show you what I've tried.

any pointers in the right direction greatly appreciated.

Regards
Kranky

Thanks Norrie

No I can't post what I've tried because I dumped it all in frustration. had tried many permutations of code off the net but it seems noone wants to do this specific task as i couldn't get a hit with it. One of them had Application.GetOpenFilename in there somewhere.

Will give your code a run shortly.

cheers
Kranky

Bingo - worked a treat

thank you Norie

Works really well Norie

Sub CopyRC()
Dim wb As Workbook
Dim strDrive As String
Dim strFolder As String
Dim strFilename As String

    strDrive = "F:"

    strFolder = "F:\Test\Test"

    ChDrive strDrive

    ChDir strFolder

    strFilename = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")

    If strFilename = "False" Then Exit Sub ' user pressed Cancel
    
    If strFilename <> "False" Then
        
        Set wb = Workbooks.Open(strFilename)

        ' code to copy and paste to other workbook
        Range("B6:B27").Copy
        wb.Close SaveChanges:=False
    End If
    Range("D2").Select
    ActiveSheet.Paste
End Sub

changed the *.xls to *.xl* otherwise files didnt show. added the use pressed cancel out of the macro option (not sure if I had to).

this all works very well for what is required.

For the sake of the exercise would it be possible to select cells from the opened file rather than a specific reference?

cheers
Kranky

Originally Posted by Norie

Kranky

Do you want the user to be able to choose the cells, perhaps even the worksheet, to copy from the workbook that's been opened?

.

Yep - just thought it would be useful to be able to do this. I dont have a specific use for it. Pretty silly really to do it through a macro when the file can just be opened and the cells selected etc

Ha ha - thats perfect!!!

Insert superlatives here

Regards
Kranky