Challenging Macro help

N

Natalia

Board Regular
Joined
Feb 20, 2009
Messages
72
Hi, i have a work task which is very manual, i was hoping it could be automated.

I need a macro that will look at Col C in sheet "Raw" and look for single string codes before the first "-" if code found then create a tab for that code and import all data for that code to its own worksheet. If no code found then create a worksheet named "INVESTIGATION" and dump all unidentified items to that worksheet.

Note the single code will always be in Upper case, so its easy for the code to scan. Also once the data has imported to its own worksheet, i need the code to insert a row between each unique Comment, but not for sheet "INVESTIGATION". Please see below example.

This is a monthly task, so if new codes are found then the macro needs to create new worksheet for that code.

Excel Workbook
ABC
5MessageFeedDescription
6Deleted1ANXXEAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
7Deleted1GCSXSAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
8Deleted1GSXXSAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
9Deleted1OGSXOAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
10DeletedWSTBFAAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
11DeletedWTINCAAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
12DeletedWTINCADISTAMPBDMP1-Email Me
13DeletedWCTFUNFEAEXPDMP1-Email You
14DeletedWCTFUNPEAEXPDMP1-Email You
15DeletedWCTFUNPECEXPDMP1-Email You
16DeletedWCTFUNPEZEXPDMP1-Email You
17DeletedWEMFUNFE3EXPCITI-Email Me
18DeletedWEMFUNFEAEXPCITI-Email You
19DeletedWEMFUNFEBEXPCITI-Email You
Raw


Excel Workbook
ABC
1MessageFeedDescription
2DeletedWTINCADISTAMPBDMP1-Email Me
3
4DeletedWCTFUNFEAEXPDMP1-Email You
5DeletedWCTFUNPEAEXPDMP1-Email You
6DeletedWCTFUNPECEXPDMP1-Email You
7DeletedWCTFUNPEZEXPDMP1-Email You
DMP1


Excel Workbook
ABC
1MessageFeedDescription
2DeletedWEMFUNFE3EXPCITI-Email Me
3
4DeletedWEMFUNFEAEXPCITI-Email You
5DeletedWEMFUNFEBEXPCITI-Email You
6
CITI


Excel Workbook
ABC
1MessageFeedDescription
2Deleted1ANXXEAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
3Deleted1GCSXSAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
4Deleted1GSXXSAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
5Deleted1OGSXOAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
6DeletedWSTBFAAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
7DeletedWTINCAAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
INVESTIGATION
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Why do the codes "CITI" and "DMP1" get their own sheet, but "150311 Joe Bloggs" go to Investigation?

Does every code that begins with a numeral go to Investigation?
Do codes for a sheet always have no space before the - ,while codes for Investigation always have a space before the - .

The Investigation codes, are they added to the existing Invistigation sheet. or is that sheet cleared at the beginning of the process?
 
Last edited:
Upvote 0
CITI and DMP1 are separate codes so it will require its own worksheet.

The below will go to investigation becuase i will need to give it a code. As per my explanation "Single string" or 1 word Upper case code needs to be give its own worksheet

"150311 Joe Bloggs" go to Investigation?

If there is no Upper case Single string code before "-" then it needs to go to investigation, because i would then give it a code, and when i run the data the following month it would have a code attached to it.

The code will need to clear investigation sheet each month before new data is imported.

Do codes for a sheet always have no space before the - ,while codes for Investigation always have a space before the - .

It does not matter, if the macro can find a single Upper case code before "-" then thats fine, it all depends how the user input the codes.

mikerickson said:
Why do the codes "CITI" and "DMP1" get their own sheet, but "150311 Joe Bloggs" go to Investigation?

Does every code that begins with a numeral go to Investigation?
Do codes for a sheet always have no space before the - ,while codes for Investigation always have a space before the - .

The Investigation codes, are they added to the existing Invistigation sheet. or is that sheet cleared at the beginning of the process?
Click to expand...
 
Last edited:
Upvote 0
Natalia,


Sample raw data in worksheet Raw:


Excel Workbook
ABC
5MessageFeedDescription
6Deleted1ANXXEAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
7Deleted1GCSXSAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
8Deleted1GSXXSAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
9Deleted1OGSXOAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
10DeletedWSTBFAAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
11DeletedWTINCAAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
12DeletedWTINCADISTAMPBDMP1-Email Me
13DeletedWCTFUNFEAEXPDMP1-Email You
14DeletedWCTFUNPEAEXPDMP1-Email You
15DeletedWCTFUNPECEXPDMP1-Email You
16DeletedWCTFUNPEZEXPDMP1-Email You
17DeletedWEMFUNFE3EXPCITI-Email Me
18DeletedWEMFUNFEAEXPCITI-Email You
19DeletedWEMFUNFEBEXPCITI-Email You
20Deleted1ANXXEAUDREPDMP1-Email You
21Deleted1GCSXSAUDREPJPMORGAN-Email You
22Deleted1GSXXSAUDREPCITI-Email You
23Deleted1OGSXOAUDREPBONY-Email You
24DeletedWSTBFAAUDREPTEST2-Email You
25DeletedWTINCAAUDREPBHP-Email You
26Deleted1ANXXEAUDREPRBS-Email You
27Deleted1GCSXSAUDREPdmp1-Email You
28Deleted1GSXXSAUDREPjpmorgan-Email You
29Deleted1OGSXOAUDREPciti-Email You
30DeletedWSTBFAAUDREPbony-Email You
31DeletedWTINCAAUDREPtest2-Email You
32Deleted1ANXXEAUDREPbhp-Email You
33Deleted1GCSXSAUDREPrbs-Email You
34
Raw





After the macro in their respective worksheets:


Excel Workbook
ABC
1MessageFeedDescription
2Deleted1ANXXEAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
3Deleted1GCSXSAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
4Deleted1GSXXSAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
5Deleted1OGSXOAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
6DeletedWSTBFAAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
7DeletedWTINCAAUDREP150311 Joe Bloggs - there is a separate holdings in these bank accounts
8Deleted1GCSXSAUDREPdmp1-Email You
9Deleted1GSXXSAUDREPjpmorgan-Email You
10Deleted1OGSXOAUDREPciti-Email You
11DeletedWSTBFAAUDREPbony-Email You
12DeletedWTINCAAUDREPtest2-Email You
13Deleted1ANXXEAUDREPbhp-Email You
14Deleted1GCSXSAUDREPrbs-Email You
15
INVESTIGATION





Excel Workbook
ABC
1MessageFeedDescription
2DeletedWTINCADISTAMPBDMP1-Email Me
3DeletedWCTFUNFEAEXPDMP1-Email You
4DeletedWCTFUNPEAEXPDMP1-Email You
5DeletedWCTFUNPECEXPDMP1-Email You
6DeletedWCTFUNPEZEXPDMP1-Email You
7Deleted1ANXXEAUDREPDMP1-Email You
8
DMP1





Excel Workbook
ABC
1MessageFeedDescription
2DeletedWEMFUNFE3EXPCITI-Email Me
3DeletedWEMFUNFEAEXPCITI-Email You
4DeletedWEMFUNFEBEXPCITI-Email You
5Deleted1GSXXSAUDREPCITI-Email You
6
CITI





Excel Workbook
ABC
1MessageFeedDescription
2Deleted1GCSXSAUDREPJPMORGAN-Email You
3
JPMORGAN





Excel Workbook
ABC
1MessageFeedDescription
2Deleted1OGSXOAUDREPBONY-Email You
3
BONY





Excel Workbook
ABC
1MessageFeedDescription
2DeletedWSTBFAAUDREPTEST2-Email You
3
TEST2





Excel Workbook
ABC
1MessageFeedDescription
2DeletedWTINCAAUDREPBHP-Email You
3
BHP





Excel Workbook
ABC
1MessageFeedDescription
2Deleted1ANXXEAUDREPRBS-Email You
3
RBS





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub Natalia()
' hiker95, 04/22/2011
' http://www.mrexcel.com/forum/showthread.php?t=545411
Dim wR As Worksheet, wI As Worksheet, ws As Worksheet
Dim c As Range, NR As Long
Dim Sp, H As String, a As Long, b As Long
Application.ScreenUpdating = False
Set wR = Worksheets("Raw")
For Each c In wR.Range("C6", wR.Range("C" & Rows.Count).End(xlUp))
  Sp = Split(c, "-")
  H = Trim(Sp(0))
  If InStr(H, " ") > 0 Then
    If Not Evaluate("ISREF(INVESTIGATION!A1)") Then
      Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "INVESTIGATION"
      Set wI = Worksheets("INVESTIGATION")
      wI.Range("A1:C1") = [{"Message","Feed","Description"}]
      NR = wI.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      wI.Range("A" & NR).Resize(, 3).Value = c.Offset(, -2).Resize(, 3).Value
      wI.UsedRange.Columns.AutoFit
    Else
      Set wI = Worksheets("INVESTIGATION")
      NR = wI.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      wI.Range("A" & NR).Resize(, 3).Value = c.Offset(, -2).Resize(, 3).Value
      wI.UsedRange.Columns.AutoFit
    End If
  Else
    b = 0
    For a = 1 To Len(H) Step 1
      If IsNumeric(Mid(H, a, 1)) = True Then
        'do nothing
      ElseIf Asc(Mid(H, a, 1)) >= 97 And Asc(Mid(H, a, 1)) <= 122 Then
        b = b + 1
      End If
    Next a
    If b = 0 Then
      If Not Evaluate("ISREF(" & H & "!A1)") Then
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = H
        Set ws = Worksheets(H)
        ws.Range("A1:C1") = [{"Message","Feed","Description"}]
        NR = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
        ws.Range("A" & NR).Resize(, 3).Value = c.Offset(, -2).Resize(, 3).Value
        ws.UsedRange.Columns.AutoFit
      Else
        Set ws = Worksheets(H)
        NR = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
        ws.Range("A" & NR).Resize(, 3).Value = c.Offset(, -2).Resize(, 3).Value
        ws.UsedRange.Columns.AutoFit
      End If
    Else
      If Not Evaluate("ISREF(INVESTIGATION!A1)") Then
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "INVESTIGATION"
        Set wI = Worksheets("INVESTIGATION")
        wI.Range("A1:C1") = [{"Message","Feed","Description"}]
      Else
        Set wI = Worksheets("INVESTIGATION")
        NR = wI.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
        wI.Range("A" & NR).Resize(, 3).Value = c.Offset(, -2).Resize(, 3).Value
        wI.UsedRange.Columns.AutoFit
      End If
    End If
  End If
Next c
wR.Activate
Application.ScreenUpdating = True
End Sub


Then run the Natalia macro.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
I need help with Module to copy data and put into a new workbook
Replies
6
Views
425
smozgur
smozgur
W
  • Question Question
Macro - Auto-email for monthly KPIs updates across all departments
Replies
2
Views
394
cwstudio2020
C
A
  • Solved
Copy Entire Workbook, save as sheet name with current date, hide all sheets except one, & attach to email
Replies
2
Views
613
Amo840812
A
W
  • Question Question
Macro - Auto-email for monthly KPIs updates across all departments
Replies
1
Views
199
whiteswan
W
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
242
Pingaware
P

Forum statistics

Threads
1,224,548
Messages
6,179,448
Members
452,915
Latest member
hannnahheileen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top