Importing External Worksheets and Renaming Them

[IlanvB]

Can someone please point me in the right direction. I have dozens of workbooks in a local folder where I need to import a specific worksheet "General Ledger" into the current workbook. The code I wrote does open the workbooks but seems to break when it gets to "total" even though when checking the value it does show the correct number of worksheets. If you skip this line then it will bring in the "General Ledger" worksheet but does not rename it as I tried to using the Case. The external file names will always be the same starting with "WP_" and will always be an "xlsx" file BUT the workbooks will have a date component in the file name (for example "WP_Test1_06012019.xlsx").

Any suggestions and/or help in solving my issue is appreciated. Here is what I have:




Option Explicit
Sub CombineWorkbooks()
Dim directory As String, fileName As String, sFilter As String, NewSheetName As String
Dim total As Integer


Application.ScreenUpdating = False
Application.DisplayAlerts = False


directory = ThisWorkbook.Path & ""
sFilter = "WP_*.xlsx"
fileName = Dir(directory & sFilter)


Do While fileName <> ""
Workbooks.Open (directory & fileName), ReadOnly:=True
total = Workbooks(ThisWorkbook.Name).Worksheets.Count
Workbooks(fileName).Worksheets("General Ledger").Copy _
After:=Workbooks(ThisWorkbook.Name).Worksheets(total)
Select Case fileName
Case "WP_Test1_*.xlsx"
NewSheetName = "TEST-1"
Case "WP_Test2_*.xlsx"
NewSheetName = "TEST-2"
Case "WP_Test3_*.xlsx"
NewSheetName = "TEST-3"
Case "WP_Test4_*.xlsx"
NewSheetName = "TEST-4"
End Select
Workbooks(fileName).Close
fileName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[sijpie]

Hi IlvanvB,
welcome to the forum.

First a remark: if you post code, then please use code tags (as shown below in red) in order to get your code nicely displayed.

I am looking at your code. There is nothing thare that I can see which would cause the Total to not get the correct number of sheet.
However I do notice you use a roundabout way of getting this number:
You get the Workbook object with the name of the current workbook and then get the worksheet count for it. You can also immediately get the worksheet count for thecurrent workbook:

 total = Thisworkbook.Worksheets.Count

The same with After parameter:

Workbooks(fileName).Worksheets("General Ledger").Copy _
         After:=ThisWorkbook.Worksheets(total)

Then in the Select Case construction you have 4 cases, but you mention dozens of workbooks. This will cause a problem if for instance you have two workbooks "WP_Test4_123.xlsx"and "WP_Test4_124.xlsx". It would seem it would be more efficient to write a little code to generate the sheet name:

    NewSheetName = UCase( Mid(filename,4,Len(filename)-(4+5)) ' New sheet name is Test + rest of the workbookname