Determine Unique List and Create folders etc

[tljenkin]

Hi All,

I have a report which has 4 sheets, lets call it the Master file (MF)

MF has been set to manual calculation because of the amount of calculations required from the formulas within.

Sheet 1-3 are reports; and
Sheet 4 has a 4-column table (Tab1) which is about 2000 rows tall (headers in row 1):

Column A of the table has report names;
Column b is the cost centre code;
Column C is the account number. The combinations for each row based on column b & c are unique and can’t be changed.
Column D has the names of the report managers

Ok I am hoping someone can help me with a macro that can do the following:

1) Use column D to create folders named after each reporting manager in explorer in a folder called “Reporting”. Column D is not sorted so the macro has to determine all unique instances of names and create folders based on that.
2) Run a routine which runs through each combination in columns b & c and changes cells A1 & A2 respectively in sheet 1 e.g. row B2 changes A1 in sheet 1, and C2 changes A2 in sheet 1
3) Hit “shift F9” for each of sheets 1-3 in turn.
4) Save file as the name in column A of Tab1, in the relevant named folder already created from column D
5) Remove all formulas in sheets 1-3.
6) Save file and close
7) Go back to master file and Loop till end.
8) Save MF

Thanks in advance

 

[Momentman]

Can you explain step 2 in more detail

 

[tljenkin]

Hi Momentman,

Re step 2... You have cell A1 and A2 in sheet 1 . These are input cells. They feed off columns B & C respectively from the table. When the values change in A1 & A2, the report refreshes when F9 is pressed. I want the macro to go through each pairing in the table in turn, refresh, create a file, save in correct folder as defined by table column d, and with correct name as defined by table column a.

Does that explain it better?

Thanks in advance

 

[Momentman]

Hi Momentman,

Re step 2... You have cell A1 and A2 in sheet 1 . These are input cells. They feed off columns B & C respectively from the table. When the values change in A1 & A2, the report refreshes when F9 is pressed. I want the macro to go through each pairing in the table in turn, refresh, create a file, save in correct folder as defined by table column d, and with correct name as defined by table column a.

Does that explain it better?

Thanks in advance

I am afraid it doesn't

Could you post sample data showing what the combinations would look like and.....

This code should create the folders using the unique names. Change "Path" to the actual path on your system

Sub MakeNewFolder()
    Dim path As String
    path = "C:\Users\ajas\Desktop\Reporting\"
    Dim wks As Worksheet
    Dim wkstab As Worksheet
    
    
    Set wkstab = Worksheets("Sheet4")
    
    'Copy data from Sheet4 column D to a new sheet
    wkstab.Range("D2:d" & Range("D" & Rows.Count).End(xlUp).Row).Copy
    Set wks = Worksheets.Add(after:=Sheets(Sheets.Count))
    ActiveSheet.Paste
    wks.Name = "UniqueList"
    
    'Remove Duplicates and leave only unique names
    wks.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
    
    'Loop through all Unique names and create folders for each name
    For I = 1 To wks.Range("A" & Rows.Count).End(xlUp).Row
        MkDir path & wks.Range("A" & I).Value
    Next I
    
    wks.Delete
End Sub

If the above works, then we can build from there on

 

[tljenkin]

Thanks Momentman , will run this now and get back.

Re combinations , column b has a one to many relationship with column c eg one cost centre will be legal department and column c will have many accounts within the legal department eg travel, entertainment, subscriptions, postage etc

So for example the first two records will look like this: HEADERS left to right A-D (report name, cost centre, account, manager name)

A2 - LegalPostage2016
B2 - Legal
C2 - Postage
D2 - Paul Fields

------

A3 - LegalTravel2016
B3 - Legal
C3 - Travel
D3 - Paul Fields

And so on...

These cost centres and accounts already exist in the financial management system, hence why no other combinations can be made, we have to use what has been detailed in the table.

Does this explain it better now?

Thank you

TJJ

 

[tljenkin]

<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<tr class="tableizer-firstrow"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr>
<tr><td>1</td><td>Report Name</td><td>Cost Centre</td><td>Account</td><td>Report Manager</td></tr>
<tr><td>2</td><td>LegalTravel2016</td><td>Legal</td><td>Travel</td><td>Paul Fields</td></tr>
<tr><td>3</td><td>LegalSubscriptions2016</td><td>Legal</td><td>Subscriptions</td><td>Paul Fields</td></tr>
<tr><td>4</td><td>LegalPostage2016</td><td>Legal</td><td>Postage</td><td>Paul Fields</td></tr>
<tr><td>5</td><td>LegalEntertainment2016</td><td>Legal</td><td>Entertainment</td><td>Paul Fields</td></tr>
<tr><td>6</td><td>LegalAccommodation2016</td><td>Legal</td><td>Accommodation</td><td>Paul Fields</td></tr>
<tr><td>7</td><td>LegalSundry2016</td><td>Legal</td><td>Sundry</td><td>Paul Fields</td></tr>
<tr><td>8</td><td>HRTravel2016</td><td>HR</td><td>Travel</td><td>Jane Birch</td></tr>
<tr><td>9</td><td>HRSubscriptions2016</td><td>HR</td><td>Subscriptions</td><td>Jane Birch</td></tr>
<tr><td>10</td><td>HRPostage2016</td><td>HR</td><td>Postage</td><td>Jane Birch</td></tr>
<tr><td>11</td><td>HREntertainment2016</td><td>HR</td><td>Entertainment</td><td>Jane Birch</td></tr>
<tr><td>12</td><td>HRAccommodation2016</td><td>HR</td><td>Accommodation</td><td>Jane Birch</td></tr>
<tr><td>13</td><td>HRSundry2016</td><td>HR</td><td>Sundry</td><td>Jane Birch</td></tr>
</table>

 

[tljenkin]

Can anyone else help please ? I'm not getting replies from momentman. Thanks

 

[tljenkin]

Momentman, are you still helping? Anyone else out there that can help with this please?