Macro in excel

[kaler]

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 Wilson]

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]

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.

 

[Sam Wilson]

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]

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 Wilson]

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]

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.