Import XLS and rename tabs

Hi Guys,

I am a newbie when it comes to VBA but I am trying to teach myself a bit now. This however is a bit above me and I am hoping for some help.

I have a folder with a load of survey forms stored. Each form is named using the "site reference.XLS". Each file has 4 tabs. One of them is the input worksheet. I have a second workbook with all the site references (and thus file names) on a worksheet. I would like a VBA script that will copy the "input" worksheet from each site survey workbook and paste it into the work book containing the script and rename it with the site reference.

Can anyone offer any assistance.

Many thanks

Rich

For starters, just do it manually for one while recording a new macro. This will generate the vba code to open a file, copy a sheet, and rename the tab. Then all you have to do is tweak it a bit to run through your entire list.

Sub Macro1()
Dim siteName As String
Dim site As Range
Dim filename As String
Dim pathname As String
Dim sheetCount As Integer


pathname = "C:\surveys\" 'Full path name of folder where surveys are kept


Application.ScreenUpdating = False 'This makes it run faster


For Each site In Range("A2:A5") 'range of cells with site names
siteName = site.Value
filename = pathname & siteName & ".xls"
sheetCount = Sheets.Count
Workbooks.Open filename:=filename
Sheets("Input").Copy AFter:=Workbooks("main.xls").Sheets(sheetCount)
ActiveSheet.Name = siteName
Windows(siteName & ".xls").Close
Next
Sheets("siteList").Activate 'activate the sheet with the list on it.
End Sub