Results 1 to 3 of 3

Thread: Import XLS and rename tabs

  1. #1
    Seeker RichDenman's Avatar
    Join Date
    Dec 2011
    Excel Version

    Import XLS and rename tabs

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

    Last edited by RichDenman; 2012-07-25 at 03:32 PM.

  2. #2
    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.

  3. #3
    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
    Sheets("siteList").Activate 'activate the sheet with the list on it.
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts