HELP : Open 2nd Workbook, copy, close and paste

[MooseMoney]

Grrrrr! I have been battling with this for a couple of hours now, searched and is still battling!

1. The user is working in a workbook called "SpareParts".
2. I now need him to select and open another workbook form a number of available workbooks in the "C\MyFiles" directory.
3. In the newly opened workbook, the data in "Sheet1!A1..F600" to be copied and this workbook can then to be closed.
4. The copied data to be pasted (as values) at "Sheet13!B2" in the "SpareParts" workbook.

This might be a simple macro but I am really struggling to get this to work.
Your assistance will be appreciated!

 

[andrewkard]

Hello, try:

Sub test()
Dim sPt$, sFl$
sPt = Application.GetOpenFilename()
Workbooks.Open (sPt)
Worksheets("Sheet1").Select
Range("A1:F600").Select
Selection.Copy
Workbooks("SpareParts").Activate
Worksheets("Sheet13").Select
Range("B2").Select
ActiveSheet.Paste
sFl = Mid(sPt, InStrRev(sPt, "\") + 1)
Workbooks(sFl).Activate
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub

 

[MooseMoney]

Thank you for your response!
However, this is also where I get stuck!

The module code breaks at:
Worksheets("Sheet1").Select

 

[MooseMoney]

Can anybody else help with this?

 

[andrewkard]

Hello,

3. In the newly opened workbook, the data in "Sheet1!A1..F600"

Is in newly opened workbook worksheet with name "Sheet1"? If not, change to correct name or use code

Worksheets(1).Select

where 1 is the number position of Worksheet