×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Combine Files by Manager

Combine Files by Manager

Combine Files by Manager

(OP)
Hi There

I have created two reports for each manager. They come from separate systems so are in separate workbooks. All the Reports are in the same folder. They are listed by Manager Name. So for example in the folder I have got a report PersonA_InspectionStatus and a report PersonA_IncidentStatus. Then There is a report PersonB_InspectionStatus and a report PersonB_IncidentStatus. I want to end up with a report PersonA_ActionStatus which will consist of the spreadsheet from PersonA_InspectionStatus and the Spreadsheet from PersonA_IncidentStatus. Same for Person B, C etc.

So far I have got the code below but im not sure firstly how to list all the names in the report without having to hardcode them and also how to save the mergedreport with the persons name in the title.

Can anyone help?


CODE -->

Sub combineFilesbyManager4()
Dim wb As Workbook, Nwb As Workbook, sh As Worksheet, nmAry As Variant, fName As String, fPath As String
Set sh = ThisWorkbook.Sheets(1) 'Edit sheet name
nmAry = Array("PersonA", "PersonB", "PersonC")
fPath = ThisWorkbook.Path 'Edit path if other workbooks are not in same directory as master.
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
    For i = LBound(nmAry) To UBound(nmAry)
        Set Nwb = Workbooks.Add
        fName = Dir(fPath & "*.xl*")
        Do
            If InStr(fName, "_") <> 0 Then
                If Left(fName, InStr(fName, "_") - 1) = nmAry(i) Then
                    Set wb = Workbooks.Open(fPath & fName)
                    Set sh = wb.Sheets(1)
                    sh.Copy After:=Nwb.Sheets(Nwb.Sheets.Count)
                    ActiveSheet.Name = Left(wb.Name, Len(wb.Name) - 5)
                    wb.Close False
                End If
            End If
            fName = Dir
        Loop While fName <> ""
        Nwb.SaveAs Left(fName, InStr(fName, "_") - 1) & ".xlsx"
        Nwb.Close False
    Next
End Sub 

RE: Combine Files by Manager

Elsie,

Quote:

I have created two reports for each manager. They come from separate systems so are in separate workbooks.
Not necessarily so. I have routinely queried or imported data from multiple sources, all from one workbook.

I really don't understand the need for all those workbooks. I often had lists of recipients, usually eMail addresses. But your procedure could loop thru a list of names, query multiple sources to generate multiple reports for each recipient and send to recipient all from one workbook.


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Combine Files by Manager

(OP)
Oops that was a typo. They are two separate worksheets within a master workbook that Iā€™m then querying to get the reports for each manager. Not really sure how to get it so that the filtered data from the two worksheets end up in a single workbook which will have the managers name in the file name

RE: Combine Files by Manager

Rather than this...

CODE

nmAry = Array("PersonA", "PersonB", "PersonC") 
...make a list/table of Persons on a sheet, and loop thru. If your Persons list is a Structured Table named tPersons with a column named Person, then

CODE

Sub combineFilesbyManager4()
Dim wb As Workbook, Nwb As Workbook, sh As Worksheet, nmAry As Variant, fName As String, fPath As String
Dim rPerson as Range
Set sh = ThisWorkbook.Sheets(1) 'Edit sheet name
nmAry = Array("PersonA", "PersonB", "PersonC")
fPath = ThisWorkbook.Path 'Edit path if other workbooks are not in same directory as master.
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
    For Each rPerson in [tPersons[Person]]
        Set Nwb = Workbooks.Add
        fName = Dir(fPath & "*.xl*")
        Do
            If InStr(fName, "_") <> 0 Then
                If Left(fName, InStr(fName, "_") - 1) = rPerson.Value Then
                    Set wb = Workbooks.Open(fPath & fName)
                    Set sh = wb.Sheets(1)
                    sh.Copy After:=Nwb.Sheets(Nwb.Sheets.Count)
                    ActiveSheet.Name = Left(wb.Name, Len(wb.Name) - 5)
                    wb.Close False
                End If
            End If
            fName = Dir
        Loop While fName <> ""
        Nwb.SaveAs Left(fName, InStr(fName, "_") - 1) & ".xlsx"
        Nwb.Close False
    Next
End Sub 

Just focusing on the Person list. Still have reservations about your workbooks approach, but I probably don't understand what you're doing.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Combine Files by Manager

(OP)
Thank you so much.

Worked like a dream and given me exactly what I am looking for

RE: Combine Files by Manager

šŸ‘

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close