Collation of worksheets

**akulka58** · 04-17-2013, 06:02 PM

Hi,

Request you to please help me in below issue.

I need to collate data from multiple workbooks. I have near about 50 workbooks in my folder. In sample screen shot I have provided few. I need to collate all worksheet into from 50 files into one workbook. The naming conventions of files are like xxx-abc-xx. Please refer screen shot. The problem I am facing is each file contains same worksheet

table

data2

data3

data4

I need to collate these worksheets from all files with small change that is while collating data from workbooks it also put file name before that. Please see below tabs

abc - table

abc - data2

abc - data3

abc - data4

if I did not do this then I will never come to know from which file code has taken data. Code should not take blank sheets from files.

The last thing is that apart from above mentioned tabs if there is another tab then code should collate that tab in our base sheet.

Please help me in this

Thanks

**patel45** · 04-18-2013, 02:02 AM

Sub OpenfileRenameSheets()
Dim strFile As String
mFolder = "E:\test\"
strFile = Dir(mFolder & "*.xls*")
Do While strFile <> ""
    Workbooks.Open mFolder & strFile
    exname = Mid(ActiveWorkbook.Name, 5, 3)
    For Each sh In ActiveWorkbook.Sheets
      sh.Name = exname & " - " & sh.Name
    Next
    ActiveWorkbook.Close False
    strFile = Dir
Loop
End Sub

**akulka58** · 04-18-2013, 03:06 AM

hi, i need to combine data data from all workbooks into one.

**patel45** · 04-18-2013, 03:27 AM

Sub OpenfileRenameSheetsCopy()
Dim strFile As String
mFolder = "E:\test\"
strFile = Dir(mFolder & "*.xls*")
Set wbmaster = ActiveWorkbook
Do While strFile <> ""
    Set WB = Workbooks.Open(mFolder & strFile)
    exname = Mid(ActiveWorkbook.Name, 5, 3)
    For Each sh In ActiveWorkbook.Sheets
      sh.Name = exname & " - " & sh.Name
      sh.Copy After:=wbmaster.Sheets(1)
    Next
    WB.Close False
    strFile = Dir
Loop
End Sub