I wouldn't really put the name of the file in a cell -- raises the risk
of a typo...

Use something like:

Option Explicit

Sub testIt()
Dim ThisWB As Workbook, OpenedWB As Workbook, _
OpenFileName As Variant
Set ThisWB = ThisWorkbook
OpenFileName = Application.GetOpenFilename()
If LCase(TypeName(OpenFileName)) = "boolean" Then
Else
Set OpenedWB = Workbooks.Open(OpenFileName)
OpenedWB.Sheets(1).Range("A1:Z100").Copy
ThisWB.Worksheets("sheet1").Range("b1").PasteSpeci al _
xlPasteValuesAndNumberFormats
OpenedWB.Close False
End If
End Sub

Note that you did not specify which worksheet within the respective
workbooks were the source/destination. I made some assumptions. <g

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , sowetoddid
says...
I would like to have a cell in Book1 that I can input a file name
into...Cell A1.

When the specific file name (C:\Book2.xls) is placed in A1, the macro
should be activated by a command button, open 'Book2', copy cells
A1:Z100, and paste them into Book 1 starting at cell B1.


That's a mouth full.

Originally, I wanted to record a macro that clicks the open file button
and lets me select the file to open, and then have the macro continue
with the rest of its recorded copy and paste. But, the mix between
macro automation and user input does not seem viable.

Thank you.


---
Message posted from http://www.ExcelForum.com/