CODEWITHSUNDEEP
excelvbarename
Mikkel Astrup
Mikkel Astrup

Reputation: 415

Import sheets change sheet name

Im importing alot of sheetnames from different files using this VBA: 

Sub ImportSheets()

    Dim sPath As String
    Dim sFname As String
    Dim wBk As Workbook
    Dim wSht As Variant


    Application.EnableEvents = False
    Application.ScreenUpdating = False
    sPath = InputBox("Enter a full path to workbooks")
    ChDir sPath
    sFname = InputBox("Enter a filename pattern")
    sFname = Dir(sPath & "\" & sFname & ".xl*", vbNormal)
    wSht = InputBox("Enter a worksheet name to copy")
    Do Until sFname = ""
        Set wBk = Workbooks.Open(sFname)
        Windows(sFname).Activate
        Sheets(wSht).Copy After:=ThisWorkbook.Sheets(1)
       ActiveSheet.Name = ActiveSheet.Range("A9")
        wBk.Close False
        sFname = Dir()
    Loop
    ActiveWorkbook.Save
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Right now the net sheetname is whatever value that is written in A9, is there a way i can change that, so the sheet will be renamed to the filename it is imported from? Alternative solution could be to rename it "Import" & Suffix, however im not sure how to add the suffix 1-1000 ect.

Upvotes: 2

Views: 1368

Answers (2)

Vityata
Vityata

Reputation: 43585

In order to have the same name as the Excel file, then simply try this:

ActiveSheet.Name = wBk.Name

If you want to have the same name as the worksheet, from which you are copying, instead of ActiveSheet.Name = ActiveSheet.Range("A9"), this is the code you need:

ActiveSheet.Name = Worksheets(wSht).Name

It will take exactly the correct name. Or even ActiveSheet.Name = wSht, as far as you are specifying it exactly through the InputBox.

In general, before trying to copy the corresponding worksheet, you may check whether it exists and only do the copy if it is there. This is one way (see the link below for others) to do it:

If WorksheetExists(wSht) Then
    Sheets(wSht).Copy After:=ThisWorkbook.Sheets(1)
    ActiveSheet.Name = ActiveSheet.Range("A9")
End If

Function WorksheetExists(sName As String) As Boolean
    WorksheetExists = Not WorksheetFunction.IsErr(Evaluate("'" & sName & "'!A1"))
End Function

In order to get Import + counter, try something like this in your code:

Option Explicit

Public Sub TestMe()

    Dim importName As String
    Dim cnt

    Do Until cnt = 10
        cnt = cnt + 1
        importName = "Import" & cnt
        Debug.Print importName
    Loop

End Sub

Simply make sure that you always increment +1 the worksheet's name.

Upvotes: 3

user4039065
user4039065

Reputation:

You have the workbook name as sFname. Peel off the extension and use that.

ActiveSheet.Name = left(sFname, instrrev(sFname, chr(46))-1)

Upvotes: 3

Related Questions