Hi,

My code assumes that the book you point it at isn't open, so check that
first. What version of excel do you have? I'm in excel 2003.

"kaler" wrote:

Sam,
Thaks for your help. I tried the following code and it showed a run time
error. Mesage reads, method 'copy' of object'worksheet failed. Any
suggestions? Thank you.


Sub GetSheets()

Dim s As String

Dim fd As FileDialog
Dim ffs As FileDialogFilters
Dim wb As Workbook
Dim xb As Workbook
Set xb = ActiveWorkbook

'Get user to pick file
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
Set ffs = .Filters
With ffs
.Clear
.Add "Excel Files", "*.xls"
End With
.AllowMultiSelect = False
If .Show Then s = .SelectedItems(1)
End With

'Turn off prompts
Application.DisplayAlerts = False

'Open chosen workbook
Set wb = Workbooks.Open(s, False)

Dim ws As Worksheet
For Each ws In wb.Worksheets
ws.Copy xb.Worksheets(1)
Next ws


'Close workbook
wb.Close

'Switch prompts back on
Application.DisplayAlerts = True

End Sub


"kaler" wrote:

I need to import many worksheets from different locations into one workbook.
Can I have a macro set up that will ask the user what file needs to be
imported and as soon as the user enters the filename/filepath name . That
file gets imported in the same format. Please help.