Copy range from unopen workbook to current workbook

[jameswilliammitchell]

I have a command button on sheet 7 of a workbook. I need to attach a code to this command button that will open up the open file dialog box and allow the user to select one of a number of previously saved files. Then automatically copy range A2:D41 from sheet 3 of this loaded workbook to sheet 7 of the initial workbook. I have no idea how to do this and any help will be greatly appreciated.

James

 

[Trevor G]

James try this out

Sub openCopy()
Application.Dialogs(xlDialogOpen).Show
Sheets("Sheet3").Select
Range("A2:D41").Copy
ActiveWorkbook.Close
Sheets("sheet7").Select
Range("A2").PasteSpecial xlPasteAll
End Sub

 

[jameswilliammitchell]

I also need to disable a userform that automatically opens when the file is loaded, how do I add this in?

 

[jameswilliammitchell]

Nevermind already fixed that, the macros works however when it copies the data it is blank, I think this is because the workbook being copied from is closed before the data is copied over