importing multiple files in multiple worksheets with file name

[hondahawkrider]

I have a macro that pulls in multiple (filtered) excel sheets to seperate tab/worksheets

Sub CopySheets()
'no filter sheets
    Application.ScreenUpdating = False
    Dim fd As FileDialog, lRow As Long, vSelectedItem As Variant, srcWB As Workbook, desWB As Workbook
    Set desWB = ThisWorkbook
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .AllowMultiSelect = True
        If .Show = -1 Then
            For Each vSelectedItem In .SelectedItems
                Set srcWB = Workbooks.Open(vSelectedItem)
                Sheets(1).Copy after:=desWB.Sheets(desWB.Sheets.Count)
                srcWB.Close False
            Next
            Else
        End If
    End With
    Application.ScreenUpdating = True
End Sub

It works great and I use it a lot - but I have 2 problems now ... The new files that are getting imported have their worksheet named Report. So when they get imported the multiple files appear as worksheet/tabs named as Report, Report (2), and Report (3) ... Instead of Importing them as report - can the worksheets use the file name instead the worsheet name ? .. My other concern is the files are descriptively names with 27 characters with the date added at the end.. Fairly sure there is a character limitation of the the length, so if the file name can be used, is there a way to count in X number of character or start from the end..

Thanks in advance....

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: importing multiple files in multiple worksheets with file name
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Alex Blakenburg]

There is a 31 character limit.
Taking 31 characters from the right is not ideal but without knowing your filename format specifically the date format it hard to suggest more targetted approach.
Put this before your close statement and give it a try.

                desWB.Sheets(desWB.Sheets.Count).Name = Right(Left(srcWB.Name, InStrRev(srcWB.Name, ".") - 1), 31)

 

[hondahawkrider]

There is a 31 character limit.
Taking 31 characters from the right is not ideal but without knowing your filename format specifically the date format it hard to suggest more targetted approach.
Put this before your close statement and give it a try.

                desWB.Sheets(desWB.Sheets.Count).Name = Right(Left(srcWB.Name, InStrRev(srcWB.Name, ".") - 1), 31)

thx, that gets me going ... much appreciated