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.

Try this:

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.

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.

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.

Hi,

The file is closed and it is still showing that run time error. May be it is
the version because I am in excel 2002.

Try re-typing the ws.copy below the line where you get the error and see what
intelli-sense says, it might be different syntax in 2002?


"kaler" wrote:

Hi,

The file is closed and it is still showing that run time error. May be it is
the version because I am in excel 2002.

Sam,

I tried typing in to make sure it was not a syntax error. It is not really
syntax but looks like someting else needs to be changed in the code. Any
input from you will really help. Thanks.