Import XLS and rename tabs

RichDenman

New member
Joined
Dec 14, 2011
Messages
7
Reaction score
0
Points
0
Excel Version(s)
2016
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
 
Last edited:

nevadaDave

New member
Joined
Jul 24, 2012
Messages
9
Reaction score
0
Points
0
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.
 

nevadaDave

New member
Joined
Jul 24, 2012
Messages
9
Reaction score
0
Points
0
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
 
Top