Macro in excel
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.
|