Take Excel Worksheet, Divide into Worksheets/Workbooks by Employee/Card #, Email via Gmail to Employee for Input

jennifer7i884 · Aug 1, 2023

Apologies if this is completely basic, but I can't find anything in the forums that directly relates to what I'm trying to accomplish. I am not at all schooled in anything VBA, technical Excel, etc. I just Google to figure things out, and here I am!

Here's my starting point:

I have a workbook with a data dump ("Chase Data") from our credit card company, listing last 3 or 4 digits of the card #, transaction date, posting date, transaction description, category, type, and amount, plus a column for the employee to add their Notes.
I've used vlookups (based on another tab) to pull in the full 4-digit card #, card status, employee name, and employee work email address.

I'd like to:

Split the "Chase Data for Send" tab into separate workbooks by column "Card #" (column I -> like eye, not L)
AND name each workbook as the original file name (Chase - CC Activity - 07.01.23 - 07.31.23 - Data v1.xlsx) PLUS "-" "Card #" (Example: Chase - CC Activity - 07.01.23 - 07.31.23 - Data v1 - 5574.xlsx)
AND (if possible) save to Google Drive -> same location as the original file but in a folder for the separate files
1. OR save to Downloads and I'll save to Google Drive manually
AND (if possible) email each separate file LINK from Google Drive via Gmail using the addresses from column "Employee Email" (column L) to each employee
1. AND CC a specific email address, such as accounting@accounting.com
2. AND include a set message template:
  1. Hello -
    
    Please see link below for June credit card charges. Can you assist with location/category selection?
    
    Chase - CC Activity - 07.01.23 - 07.31.23 - Data v1 - 5574.xlsx <--- Link to File in Google Drive
    
    Thanks,
  2. (Email Signature)

That's not too much to ask, right? *ha*

Chase - CC Activity - 07.01.23 - 07.31.23 - Data v1 (test).xlsx

A

B

C

D

E

F

G

H

I

J

K

L

1

Card

Transaction Date

Post Date

Description

Category

Type

Amount

NOTES

Card #

Card Status

Employee Name

Employee Email

2

2751

7/30/2023

7/31/2023

ZAPIER.COM/CHARGE

Office & Shipping

Sale

(29.99)

3

2373

7/30/2023

7/31/2023

CEBROKER CEBROKER.COM

Office & Shipping

Sale

(39.99)

4

534

7/28/2023

7/30/2023

AMZN Mktp US

Merchandise & Inventory

Return

76.56

5

534

7/28/2023

7/30/2023

WF WAYFAIR3939481283

Merchandise & Inventory

Return

175.62

6

2751

7/27/2023

7/28/2023

GITHUB INC.

Professional Services

Sale

(16.00)

7

2751

7/27/2023

7/28/2023

NPM INC.

Office & Shipping

Sale

(7.00)

8

534

7/23/2023

7/27/2023

SQ *SQUARE HARDWARE

Office & Shipping

Return

191.16

9

2751

7/25/2023

7/26/2023

WEBFLOW.COM

Professional Services

Sale

(45.00)

10

2751

7/24/2023

7/26/2023

WINGATE INN

Travel

Sale

(151.20)

11

534

7/24/2023

7/25/2023

WF WAYFAIR3940855263

Merchandise & Inventory

Return

270.21

12

2751

7/24/2023

7/25/2023

CARIBOU COFFEE A

Food & Drink

Sale

(7.56)

13

2751

7/25/2023

UBER TRIP

Travel

Sale

(30.92)

14

2373

7/23/2023

7/25/2023

GOLDEN EAGLE SUPERMARKET

Food & Drink

Sale

(105.68)

15

534

7/23/2023

7/24/2023

SF WASH

Miscellaneous

Sale

(42.00)

16

2751

7/23/2023

7/24/2023

EXXON GATEWAY CONVENIENCE

Gas

Sale

(43.52)

17

1079

7/22/2023

7/23/2023

SP SITN SLEEP

Repair & Maintenance

Sale

(6,821.69)

18

2751

7/21/2023

7/23/2023

07890 - RESIDENCE INN DEN

Travel

Sale

(39.00)

19

2751

7/21/2023

7/23/2023

PANZANO

Food & Drink

Sale

(13.88)

20

2751

7/21/2023

7/23/2023

KIMPTON HOTEL MONACO DEN

Travel

Sale

(5.00)

21

2751

7/21/2023

7/23/2023

MAD GREENS MG0103

Food & Drink

Sale

(13.84)

22

2751

7/21/2023

7/23/2023

PAY*THE GOLDEN MILL

Food & Drink

Sale

(37.11)

23

2751

7/19/2023

7/21/2023

STAPLES 00111161

Office & Shipping

Sale

(6.94)

24

2751

7/20/2023

7/21/2023

PEETS COFFEE

Merchandise & Inventory

Sale

(10.43)

25

2751

7/20/2023

7/21/2023

HUMBLE HOUSE

Food & Drink

Sale

(5.14)

26

2373

7/19/2023

7/20/2023

THE UPS STORE 6757

Office & Shipping

Sale

(75.27)

27

534

7/4/2023

7/19/2023

OMEGA #680

Merchandise & Inventory

Adjustment

6,460.50

28

2751

7/18/2023

7/19/2023

ZOOM.US 888-799-9666

Bills & Utilities

Sale

(675.48)

29

534

7/17/2023

7/18/2023

AMZN Mktp US*3L0TA1QR3

Merchandise & Inventory

Sale

(59.51)

30

534

7/17/2023

AMZN Mktp US*W869B4L33

Merchandise & Inventory

Sale

(7.03)

31

534

7/16/2023

7/17/2023

SF WASH

Miscellaneous

Sale

(42.00)

32

534

7/16/2023

7/17/2023

SF WASH

Miscellaneous

Sale

(42.00)

33

534

7/17/2023

AMZN Mktp US*FC2BH9RW3

Merchandise & Inventory

Sale

(201.48)

34

534

7/16/2023

7/17/2023

Amazon.com*9I0QX9G43

Merchandise & Inventory

Sale

(16.23)

35

534

7/15/2023

7/17/2023

OMEGA #680

Merchandise & Inventory

Return

6,460.50

36

534

7/16/2023

7/17/2023

WESTELM.COM

Repair & Maintenance

Sale

(146.88)

37

534

7/16/2023

7/17/2023

WESTELM.COM

Repair & Maintenance

Sale

(371.51)

38

534

7/16/2023

7/17/2023

AMZN Mktp US*KP1EK4UY3

Merchandise & Inventory

Sale

(8.65)

39

534

7/14/2023

7/16/2023

THE PROMETHEUS GROUP INC

Health & Wellness

Sale

(1,048.00)

40

534

7/14/2023

7/16/2023

TARGET.COM *

Merchandise & Inventory

Sale

(41.93)

41

534

7/14/2023

7/16/2023

Amazon.com*BT6SQ1D53

Merchandise & Inventory

Sale

(41.15)

42

534

7/14/2023

7/16/2023

AMZN Mktp US*R78JS3O23

Merchandise & Inventory

Sale

(28.67)

43

534

7/14/2023

7/16/2023

SP NAKED NUTRITION

Food & Drink

Sale

(121.97)

44

534

7/14/2023

7/16/2023

AMZN Mktp US*SM4RI48X3

Merchandise & Inventory

Sale

(19.46)

45

534

7/15/2023

7/16/2023

AMZN Mktp US*V35RQ4PC3

Merchandise & Inventory

Sale

(22.49)

46

534

7/15/2023

7/16/2023

STORE*BLUE RIBBON SUSH

Food & Drink

Sale

(13.14)

47

534

7/15/2023

7/16/2023

AMZN Mktp US*N10KG1X13

Merchandise & Inventory

Sale

(24.92)

48

534

7/15/2023

7/16/2023

AMZN Mktp US*EO73G5P43

Merchandise & Inventory

Sale

(14.03)

49

534

7/14/2023

7/16/2023

TARGET.COM *

Merchandise & Inventory

Return

41.93

50

534

7/14/2023

7/16/2023

INTIMATE ROSE

Professional Services

Sale

(362.37)

51

534

7/14/2023

7/16/2023

TARGET.COM *

Merchandise & Inventory

Sale

(105.92)

52

534

7/14/2023

7/16/2023

SP NAKED NUTRITION

Food & Drink

Sale

(71.98)

53

2751

7/13/2023

7/16/2023

KIMPTON HOTEL MONACO DEN

Travel

Sale

(365.56)

54

2751

7/11/2023

7/12/2023

ADOBE *800-833-6687

Office & Shipping

Sale

(137.08)

55

2751

7/6/2023

7/7/2023

SANITY.IO

Office & Shipping

Sale

(12.00)

56

2751

7/6/2023

7/7/2023

ALGOLIA 2I2307489569

Office & Shipping

Sale

(5.00)

57

2751

7/5/2023

7/6/2023

ADOBE *800-833-6687

Office & Shipping

Sale

(1,242.22)

58

2751

7/4/2023

7/5/2023

FIGMA MONTHLY RENEWAL

Professional Services

Sale

(175.00)

59

2751

7/2/2023

7/3/2023

Amazon web services

Office & Shipping

Sale

(1,839.15)

60

2751

6/30/2023

7/2/2023

ZAPIER.COM/CHARGE

Office & Shipping

Sale

(29.99)

61

2751

7/1/2023

7/2/2023

GOOGLE*CLOUD GRNBPB

Professional Services

Sale

(8.06)

Chase Data for Send

Cell Formulas
Range	Formula
I2:I61	I2	=VLOOKUP(A2,'Employee VLookup'!B:G,2,FALSE)
J2:J61	J2	=VLOOKUP(A2,'Employee VLookup'!B:G,3,FALSE)
K2:K61	K2	=VLOOKUP(A2,'Employee VLookup'!B:G,5,FALSE)
L2:L61	L2	=VLOOKUP(I2,'Employee VLookup'!C:G,5,FALSE)

John_w · Aug 2, 2023

jennifer7i884 said:
Split the "Chase Data for Send" tab into separate workbooks by column "Card #" (column I -> like eye, not L)

AND name each workbook as the original file name (Chase - CC Activity - 07.01.23 - 07.31.23 - Data v1.xlsx) PLUS "-" "Card #" (Example: Chase - CC Activity - 07.01.23 - 07.31.23 - Data v1 - 5574.xlsx)

AND (if possible) save to Google Drive -> same location as the original file but in a folder for the separate files

OR save to Downloads and I'll save to Google Drive manually

This macro should cover your first 3 requirements, saving the new workbooks in your Downloads folder (the saveInFolder string).

I think you need Google Drive for Desktop to have Google Drive folders available in File Explorer; with that you might be able to change the saveInFolder string to a Google Drive folder and the macro will save the new workbooks directly to Google Drive.

VBA Code:

Public Sub Split_By_Card_Number_to_New_Workbooks()

    Dim saveInFolder As String
    Dim splitSheet As Worksheet
    Dim cardNoDict As Object
    Dim cardNoCell As Range
    Dim cardNos As Variant, i As Long
            
    saveInFolder = Environ("USERPROFILE") & "\Downloads\"
    
    Set splitSheet = ThisWorkbook.Worksheets("Chase Data for Send")
    
    'Create dictionary of unique card numbers in column I
    
    Set cardNoDict = CreateObject("Scripting.Dictionary")
    With splitSheet
        For Each cardNoCell In .Range("I2", .Cells(.Rows.Count, "I").End(xlUp))
            cardNoDict(cardNoCell.Value) = 1
        Next
    End With
       
    Application.ScreenUpdating = False
       
    'Loop through each unique card number
    
    cardNos = cardNoDict.Keys
    For i = LBound(cardNos) To UBound(cardNos)
        'Copy sheet, creating a new workbook and save it with this card number in file name
        splitSheet.Copy
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs saveInFolder & Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1) & " - " & cardNos(i) & ".xlsx", xlOpenXMLWorkbook
        Application.DisplayAlerts = True
        
        With ActiveWorkbook.Worksheets(1).UsedRange
            'Copy and paste as values to remove formulas and links to original workbook
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Cells(1).Select
            Application.CutCopyMode = False
            
            'Autofilter on column I for rows NOT equal to this card number and delete visible rows
            .AutoFilter Field:=9, Criteria1:="<>" & cardNos(i) & "*", Operator:=xlFilterValues         'Note - without "*" filter doesn't work
            .Offset(1).EntireRow.Delete
            .AutoFilter
        End With
        ActiveWorkbook.Close True
    Next
   
    Application.ScreenUpdating = True
    
    MsgBox "Created new workbooks in " & saveInFolder, vbInformation
    
End Sub

jennifer7i884 · Aug 2, 2023

John_w said:

This macro should cover your first 3 requirements, saving the new workbooks in your Downloads folder (the saveInFolder string).

I think you need Google Drive for Desktop to have Google Drive folders available in File Explorer; with that you might be able to change the saveInFolder string to a Google Drive folder and the macro will save the new workbooks directly to Google Drive.

VBA Code:

Public Sub Split_By_Card_Number_to_New_Workbooks()

    Dim saveInFolder As String
    Dim splitSheet As Worksheet
    Dim cardNoDict As Object
    Dim cardNoCell As Range
    Dim cardNos As Variant, i As Long
           
    saveInFolder = Environ("USERPROFILE") & "\Downloads\"
   
    Set splitSheet = ThisWorkbook.Worksheets("Chase Data for Send")
   
    'Create dictionary of unique card numbers in column I
   
    Set cardNoDict = CreateObject("Scripting.Dictionary")
    With splitSheet
        For Each cardNoCell In .Range("I2", .Cells(.Rows.Count, "I").End(xlUp))
            cardNoDict(cardNoCell.Value) = 1
        Next
    End With
      
    Application.ScreenUpdating = False
      
    'Loop through each unique card number
   
    cardNos = cardNoDict.Keys
    For i = LBound(cardNos) To UBound(cardNos)
        'Copy sheet, creating a new workbook and save it with this card number in file name
        splitSheet.Copy
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs saveInFolder & Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1) & " - " & cardNos(i) & ".xlsx", xlOpenXMLWorkbook
        Application.DisplayAlerts = True
       
        With ActiveWorkbook.Worksheets(1).UsedRange
            'Copy and paste as values to remove formulas and links to original workbook
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Cells(1).Select
            Application.CutCopyMode = False
           
            'Autofilter on column I for rows NOT equal to this card number and delete visible rows
            .AutoFilter Field:=9, Criteria1:="<>" & cardNos(i) & "*", Operator:=xlFilterValues         'Note - without "*" filter doesn't work
            .Offset(1).EntireRow.Delete
            .AutoFilter
        End With
        ActiveWorkbook.Close True
    Next
  
    Application.ScreenUpdating = True
   
    MsgBox "Created new workbooks in " & saveInFolder, vbInformation
   
End Sub

HOW ARE YOU SO SMART?! This worked perfectly!!!! Thank you so much!!!! I could cry! Thank you so very much!!

Jennifer

jennifer7i884 · Aug 2, 2023

John_w said:

This macro should cover your first 3 requirements, saving the new workbooks in your Downloads folder (the saveInFolder string).

I think you need Google Drive for Desktop to have Google Drive folders available in File Explorer; with that you might be able to change the saveInFolder string to a Google Drive folder and the macro will save the new workbooks directly to Google Drive.

VBA Code:

Public Sub Split_By_Card_Number_to_New_Workbooks()

    Dim saveInFolder As String
    Dim splitSheet As Worksheet
    Dim cardNoDict As Object
    Dim cardNoCell As Range
    Dim cardNos As Variant, i As Long
           
    saveInFolder = Environ("USERPROFILE") & "\Downloads\"
   
    Set splitSheet = ThisWorkbook.Worksheets("Chase Data for Send")
   
    'Create dictionary of unique card numbers in column I
   
    Set cardNoDict = CreateObject("Scripting.Dictionary")
    With splitSheet
        For Each cardNoCell In .Range("I2", .Cells(.Rows.Count, "I").End(xlUp))
            cardNoDict(cardNoCell.Value) = 1
        Next
    End With
      
    Application.ScreenUpdating = False
      
    'Loop through each unique card number
   
    cardNos = cardNoDict.Keys
    For i = LBound(cardNos) To UBound(cardNos)
        'Copy sheet, creating a new workbook and save it with this card number in file name
        splitSheet.Copy
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs saveInFolder & Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1) & " - " & cardNos(i) & ".xlsx", xlOpenXMLWorkbook
        Application.DisplayAlerts = True
       
        With ActiveWorkbook.Worksheets(1).UsedRange
            'Copy and paste as values to remove formulas and links to original workbook
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Cells(1).Select
            Application.CutCopyMode = False
           
            'Autofilter on column I for rows NOT equal to this card number and delete visible rows
            .AutoFilter Field:=9, Criteria1:="<>" & cardNos(i) & "*", Operator:=xlFilterValues         'Note - without "*" filter doesn't work
            .Offset(1).EntireRow.Delete
            .AutoFilter
        End With
        ActiveWorkbook.Close True
    Next
  
    Application.ScreenUpdating = True
   
    MsgBox "Created new workbooks in " & saveInFolder, vbInformation
   
End Sub

Is there a way that you know of to do this part?

AND (if possible) email each separate file LINK from Google Drive via Gmail using the addresses from column "Employee Email" (column L) to each employee
1. AND CC a specific email address, such as accounting@accounting.com
2. AND include a set message template:
  1. Hello -
    
    Please see link below for June credit card charges. Can you assist with location/category selection?
    
    Chase - CC Activity - 07.01.23 - 07.31.23 - Data v1 - 5574.xlsx <--- Link to File in Google Drive
    
    Thanks,
  2. (Email Signature)

John_w · Aug 2, 2023

Sending an email from a Gmail account directly via Gmail's SMTP server (not via Outlook) can be done using Windows CDO (Collaboration Data Objects). However, the CDO library might not be installed on your machine and you'll have to find it on the web and install it.

Run this macro and if an error occurs it means CDO isn't installed.

VBA Code:

Public Sub CDO_Test()

    Dim iMsg As Object, iConf As Object
    
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
    
    MsgBox "Success - CDO is installed"

End Sub

jennifer7i884 · Aug 2, 2023

John_w said:
Sending an email from a Gmail account directly via Gmail's SMTP server (not via Outlook) can be done using Windows CDO (Collaboration Data Objects). However, the CDO library might not be installed on your machine and you'll have to find it on the web and install it.

Run this macro and if an error occurs it means CDO isn't installed.

VBA Code:

Public Sub CDO_Test() Dim iMsg As Object, iConf As Object Set iMsg = CreateObject("CDO.Message") Set iConf = CreateObject("CDO.Configuration") MsgBox "Success - CDO is installed" End Sub

Okay - it didn't work. However, I ended up moving everything to Google Drive manually, then sharing folders and access to folders with specific users.

I have a follow up question about drop-down menus on the sheet......... If I want to create lists in a second tab and then have drop-downs in the first tab link to them, is there a way to have the sheets split by card # on the first tab and bring both that first tab PLUS the full second tab over and stay linked so my drop-downs will stay intact?

I also tried putting the list on the first tab and linking to drop-downs, but when it split the sheets up, it cut the lists up, too..... Hopefully I'm making sense. I can create a test file and send if not!

jennifer7i884 · Aug 3, 2023

jennifer7i884 said:
Okay - it didn't work. However, I ended up moving everything to Google Drive manually, then sharing folders and access to folders with specific users.

I have a follow up question about drop-down menus on the sheet......... If I want to create lists in a second tab and then have drop-downs in the first tab link to them, is there a way to have the sheets split by card # on the first tab and bring both that first tab PLUS the full second tab over and stay linked so my drop-downs will stay intact?

I also tried putting the list on the first tab and linking to drop-downs, but when it split the sheets up, it cut the lists up, too..... Hopefully I'm making sense. I can create a test file and send if not!

John_w said:
Sending an email from a Gmail account directly via Gmail's SMTP server (not via Outlook) can be done using Windows CDO (Collaboration Data Objects). However, the CDO library might not be installed on your machine and you'll have to find it on the web and install it.

Run this macro and if an error occurs it means CDO isn't installed.

VBA Code:

Public Sub CDO_Test() Dim iMsg As Object, iConf As Object Set iMsg = CreateObject("CDO.Message") Set iConf = CreateObject("CDO.Configuration") MsgBox "Success - CDO is installed" End Sub

Okay. I'm back!

How can I take this sheet....

1. Divide up by card number like you did before on column M (was I like "eye" previously) -> Tab is named "Current Month Expenses"
2. Bring a second tab with it named "Category VLookup"
3. So my drop-down's in columns "I" and "J" (eye and jay) will still pull from "Category VLookup"

- I was able to correct the original script you sent me so it's pulling from column "M" and the new tab name.
- It ran successfully.
- However, the drop-downs won't work because the second tab isn't there anymore.
- I tried putting the drop-down info on tab "Current Month Expenses" but it cuts it off based on the lines that move over to the different sheets.

File name: "Chase - CC Activity - 07.01.23 - 07.31.23 v2 (TEST).xlsx"

Tab 1: "Current Month Expenses"

Chase - CC Activity - 07.01.23 - 07.31.23 v2 (TEST).xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

Card

Transaction Date

Post Date

Description

Category

TOW

OPTCA

OPTFL

OPTNY

Class

2

OTHER TRANSACTION (Please Comment)

OTHER/NOT LISTED (Please Comment)

3

Advertising & Marketing:Advertising Analytics

6000-04

Corporate:Business Development

4

Advertising & Marketing:B2B Marketing

6000-08

Corporate:Clinical Operations

5

Advertising & Marketing:Creative Design

6000-09

Corporate:Corporate Operations

6

Advertising & Marketing:Marketing Events

6000-05

Corporate:Finance & Accounting

7

Advertising & Marketing:Other Marketing

6000-99

Corporate:General Corporate

8

Advertising & Marketing:Printed Advertising

6000-10

Corporate:Holdings

9

Advertising & Marketing:Provider Referal

Corporate:Marketing

10

Advertising & Marketing:SEO & Ad Words

6000-01

Corporate:People Ops

11

Advertising & Marketing:Social Media

6000-03

Corporate:Product & Engineering

12

Business Development:Data Analytics/Market Research

6001-01

Corporate:Revenue Cycle Management

13

Business Development:Product & Engineering

6001-03

Corporate:Senior Leadership

14

Business Development:Recruitment Events

6001-04

Corporate:Other (Please Comment)

15

Business Development:Travel to New Markets

6001-02

Medical Clinics:Austin

16

Cost of Goods Sold:Management Expense

5005

Medical Clinics:Brentwood

17

Cost of Goods Sold:Medical Supplies

5020

Medical Clinics:Rollingwood

18

Cost of Goods Sold:Medical Supplies & Product Shipping

5010

Medical Clinics:Round Rock

19

Cost of Goods Sold:Provider Wages, Taxes, & Benefits:Provider Salaries & Wages:Provider Contractors

5300-03

Medical Clinics:San Antonio

20

Cost of Goods Sold:Retail Inventory

5030

Medical Clinics:San Francisco

21

Credit Card Payment

Medical Clinics:Sugar Land

22

Equipment

1560

Medical Clinics:West Hollywood

23

Equipment:Bebe Holding

Medical Clinics:West Loop

24

Facilities:Cleaning

6152

Medical Clinics:Other (Please Comment)

25

Facilities:Common Area Maintenance & RE Tax Share

6151

Remote

26

Facilities:Rent & Lease

6140

27

Facilities:Repairs & Maintenance

6150

28

Facilities:Telephone & Internet

6201

29

Facilities:Utilities

6200

30

General & Administrative:Education & Training:Continuing Medical Education

6550-01

31

General & Administrative:Education & Training:Corporate Training

6550-05

32

General & Administrative:Education & Training:Offsite Meetings

6550-03

33

General & Administrative:Education & Training:Professional Development

6550-04

34

General & Administrative:Meals & Entertainment

6540

35

General & Administrative:Meals & Entertainment:Charitable Contribution

6540-20

36

General & Administrative:Meals & Entertainment:Employee Awards & Gifts

6540-11

37

General & Administrative:Meals & Entertainment:Entertainment

6540-02

38

General & Administrative:Meals & Entertainment:Gifts & Tips

6540-10

39

General & Administrative:Meals & Entertainment:Holiday Events

6540-03

40

General & Administrative:Meals & Entertainment:Meals

6540-01

41

General & Administrative:Meals & Entertainment:Other Meals & Entertainment

6540-99

42

General & Administrative:Meals & Entertainment:Team Building Meals

6540-04

43

General & Administrative:Professional Service:Bank Charges & Fees

6520-06

44

General & Administrative:Professional Service:Billing & Collections

6520-03

45

General & Administrative:Professional Service:Consulting

6520-09

46

General & Administrative:Professional Service:Credit Card Fees

6520-07

47

General & Administrative:Professional Service:Legal Fees

6520-01

48

General & Administrative:Professional Service:Other Professional Services

6520-99

49

General & Administrative:Recruiting:Other Recruiting

6560-99

50

General & Administrative:Recruiting:Recruiting Ads

6560-01

51

General & Administrative:Recruiting:Recruiting Travel

6560-02

52

General & Administrative:Taxes & Reports:Annual Reports & Filings

6570-01

53

General & Administrative:Travel:Auto Related Expenses

6510-01

54

General & Administrative:Travel:Flights/Trains

6510-04

55

General & Administrative:Travel:Hotel/Lodging

6510-05

56

General & Administrative:Travel:Other Travel

6510-99

57

General & Administrative:Travel:Parking

6510-06

58

General & Administrative:Travel:Ride Share

6510-02

59

Income Taxes:Federal Income Tax

7550-01

60

Income Taxes:Local Income Taxes

7550-03

61

Income Taxes:State Income Tax

7550-02

62

Interest Expense

7500-02

63

Miscellaneous AR

1081

64

Operating:Covid Testing

6400

65

Operating:Dues and Subscriptions

6413

66

Operating:Office Supplies

6411

67

Operating:Postage & Shipping

6412

68

Operating:Printing

6414

69

Operating:Technology Expenses

6430

70

Operating:Technology Expenses:Expensed Tech Equipment

6430-02

71

Operating:Technology Expenses:Internet Subscriptions

6430-04

72

Operating:Technology Expenses:IT

6430-01

73

Operating:Technology Expenses:Other Tech Expenses

6430-99

74

Operating:Technology Expenses:Software as a Service

6430-03

75

Prepaid Expenses

1100

76

Receivables - Purchase

1040

77

Revenue:Discounts/Refunds Given

4500

78

Revenue:Other Revenue:Retail Sales

4020

79

Suspense Transaction (Please Comment)

80

Taxes & Licenses (Please Comment)

81

Uncategorized Asset

1050

82

Uncategorized Expense

6190

83

Wages, Taxes, & Benefits:Benefits

6320

84

Wages, Taxes, & Benefits:Benefits:Other Benefits

6320-99

85

Wages, Taxes, & Benefits:Salaries & Wages:Contractors

6300-04

Category VLookup

jennifer7i884 · Aug 3, 2023

jennifer7i884 said:
Okay. I'm back!

How can I take this sheet....

1. Divide up by card number like you did before on column M (was I like "eye" previously) -> Tab is named "Current Month Expenses"
2. Bring a second tab with it named "Category VLookup"
3. So my drop-down's in columns "I" and "J" (eye and jay) will still pull from "Category VLookup"

- I was able to correct the original script you sent me so it's pulling from column "M" and the new tab name.
- It ran successfully.
- However, the drop-downs won't work because the second tab isn't there anymore.
- I tried putting the drop-down info on tab "Current Month Expenses" but it cuts it off based on the lines that move over to the different sheets.

File name: "Chase - CC Activity - 07.01.23 - 07.31.23 v2 (TEST).xlsx"

Tab 1: "Current Month Expenses"

Chase - CC Activity - 07.01.23 - 07.31.23 v2 (TEST).xlsm
A B C D E F G H I J K L M N O P Q R
1 Card Transaction Date Post Date Description Category Type Amount PAYEE (VENDOR) (Correct if necessary) CATEGORY CLASS / LOCATION COMMENTS / CATEGORY CORRECTION Trans # Card # Line Item # Tran Amt Card Status Employee Name Employee Email
2 9050 6/27/2023 7/4/2023 VIEWLINE RESORT SNOWMASS Travel Return 1,540.86 Viewline Resort Snowmass 1 9050 9050-1 (1,540.86) Open Sarah Smith sarah@email.com
3 8864 6/30/2023 7/2/2023 INTUIT *QBooks Online Office & Shipping Sale (85.00) Intuit QuickBooks Online 2 8864 8864-2 85.00 Open David Jones david@email.com
4 8864 6/30/2023 7/2/2023 INTUIT *QBooks Online Office & Shipping Sale (85.00) Intuit QuickBooks Online 3 8864 8864-3 85.00 Open David Jones david@email.com
5 9050 6/30/2023 7/2/2023 PAPER OR PLASTIC Food & Drink Sale (58.48) Paper or Plastic 4 9050 9050-4 58.48 Open Sarah Smith sarah@email.com
6 9050 7/1/2023 7/2/2023 GOOGLE *GSUITE_theorig Office & Shipping Sale (1,994.00) Google Gsuite 5 9050 9050-5 1,994.00 Open Sarah Smith sarah@email.com
7 9050 7/1/2023 7/2/2023 GOOGLE *SVCStheoriginw Bills & Utilities Sale (72.81) Google SVCs 6 9050 9050-6 72.81 Open Sarah Smith sarah@email.com
8 9050 7/1/2023 7/2/2023 Indeed Jobs Professional Services Sale (272.18) Indeed Jobs 7 9050 9050-7 272.18 Open Sarah Smith sarah@email.com
9 5298 7/2/2023 7/3/2023 AMZN Mktp US*3B8FO1EA3 Merchandise & Inventory Sale (43.42) Amazon.com 8 5298 5298-8 43.42 Open Amy Thompson amy@email.com
10 8864 7/4/2023 7/5/2023 FAXAGE Professional Services Sale (79.80) Faxage 9 8864 8864-9 79.80 Open David Jones david@email.com
11 534 7/4/2023 7/19/2023 OMEGA #680 Merchandise & Inventory Adjustment 6,460.50 Omega 10 0534 0534-10 (6,460.50) Open David Jones david@email.com
12 8864 7/5/2023 7/5/2023 Payment Thank You - Web Payment 22,870.55 Credit Card Payment 11 8864 8864-11 (22,870.55) Open David Jones david@email.com
13 8864 7/5/2023 7/6/2023 YAHOO SMALL BUSINESS Bills & Utilities Sale (24.98) Yahoo Small Business 12 8864 8864-12 24.98 Open David Jones david@email.com
14 9050 7/5/2023 7/6/2023 PAPER OR P* PAPER OR P Food & Drink Sale (45.19) Paper or Plastic 13 9050 9050-13 45.19 Open Sarah Smith sarah@email.com
15 8864 7/5/2023 7/7/2023 ATHENS SERVICES Bills & Utilities Sale (252.66) Athens Services 14 8864 8864-14 252.66 Open David Jones david@email.com
16 8864 7/6/2023 7/7/2023 LINKEDIN *RECRUITER Bills & Utilities Sale (139.99) LinkedIn Recruiter 15 8864 8864-15 139.99 Open David Jones david@email.com
17 9050 7/6/2023 7/9/2023 DELTA AIR 0062125338550 Travel Sale (34.00) Delta Air 16 9050 9050-16 34.00 Open Sarah Smith sarah@email.com
18 7414 7/7/2023 7/7/2023 Payment Thank You - Web Payment 224.00 Credit Card Payment 17 7414 7414-17 (224.00) Open David Jones david@email.com
19 9050 7/8/2023 7/9/2023 Indeed Jobs Professional Services Sale (600.00) Indeed Jobs 18 9050 9050-18 600.00 Open Sarah Smith sarah@email.com
20 9050 7/8/2023 7/9/2023 Amazon.com Merchandise & Inventory Return 52.61 Amazon.com 19 9050 9050-19 (52.61) Open Sarah Smith sarah@email.com
21 8864 7/9/2023 7/10/2023 ADOBE *800-833-6687 Office & Shipping Sale (10.81) Adobe 20 8864 8864-20 10.81 Open David Jones david@email.com
22 5910 7/10/2023 7/11/2023 ALFRED COFFEE ORDER Food & Drink Sale (39.30) Alfred Coffee 21 5910 5910-21 39.30 Open Jennifer Johnson jennifer@email.com
23 9050 7/10/2023 7/11/2023 Indeed Jobs Professional Services Sale (120.00) Indeed Jobs 22 9050 9050-22 120.00 Open Sarah Smith sarah@email.com
24 9050 7/10/2023 7/11/2023 Indeed Jobs Professional Services Sale (300.00) Indeed Jobs 23 9050 9050-23 300.00 Open Sarah Smith sarah@email.com
25 8864 7/10/2023 7/12/2023 DIRECT CARE STORE Merchandise & Inventory Sale (564.10) Direct Care Store 24 8864 8864-24 564.10 Open David Jones david@email.com
26 8864 7/11/2023 7/11/2023 UBER TRIP Travel Sale (8.06) Uber Trip 25 8864 8864-25 8.06 Open David Jones david@email.com
27 9050 7/11/2023 7/11/2023 Indeed Jobs Professional Services Sale (528.19) Indeed Jobs 26 9050 9050-26 528.19 Open Sarah Smith sarah@email.com
28 8864 7/11/2023 7/12/2023 Payment Thank You - Web Payment 35,899.52 Credit Card Payment 27 8864 8864-27 (35,899.52) Open David Jones david@email.com
29 9050 7/11/2023 7/12/2023 Rapha Racing San Franc Merchandise & Inventory Sale (11.25) Rapha Racing SF 28 9050 9050-28 11.25 Open Sarah Smith sarah@email.com
30 9050 7/11/2023 7/12/2023 UBER TRIP Travel Sale (44.39) Uber Trip 29 9050 9050-29 44.39 Open Sarah Smith sarah@email.com
31 9050 7/11/2023 7/12/2023 WHOLEFDS NOE 10379 Food & Drink Sale (35.17) Wholefoods 30 9050 9050-30 35.17 Open Sarah Smith sarah@email.com
32 8864 7/11/2023 7/13/2023 OFFICE DEPOT #2206 Office & Shipping Sale (64.57) Office Depot 31 8864 8864-31 64.57 Open David Jones david@email.com
33 8864 7/11/2023 7/13/2023 DIRECT CARE STORE Merchandise & Inventory Sale (711.40) Direct Care Store 32 8864 8864-32 711.40 Open David Jones david@email.com
34 8864 7/11/2023 7/13/2023 Preferred Therapy Prov Miscellaneous Sale (167.00) Preferred Therapy Prov 33 8864 8864-33 167.00 Open David Jones david@email.com
35 8864 7/11/2023 7/13/2023 Preferred Therapy Prov Miscellaneous Sale (167.00) Preferred Therapy Prov 34 8864 8864-34 167.00 Open David Jones david@email.com
36 9050 7/11/2023 7/13/2023 BI-RITE CATERING Food & Drink Sale (592.45) Bi-Rite Catering 35 9050 9050-35 592.45 Open Sarah Smith sarah@email.com
37 8864 7/12/2023 7/12/2023 VZWRLSS*APOCC VISE Bills & Utilities Sale (112.91) Verizon Wireless 36 8864 8864-36 112.91 Open David Jones david@email.com
38 9050 7/12/2023 7/12/2023 UBER TRIP Travel Sale (38.96) Uber Trip 37 9050 9050-37 38.96 Open Sarah Smith sarah@email.com
Current Month Expenses
Cells with Data Validation
Cell Allow Criteria
K2 Any value
I2:I169 List ='Category VLookup'!$A$2:$A$1048576
J2:J169 List ='Category VLookup'!$F$2:$F$1048576

Tab 2: "Category VLookup"

Chase - CC Activity - 07.01.23 - 07.31.23 v2 (TEST).xlsm
A B C D E F
1 Category TOW OPTCA OPTFL OPTNY Class
2 OTHER TRANSACTION (Please Comment) OTHER/NOT LISTED (Please Comment)
3 Advertising & Marketing:Advertising Analytics 6000-04 6000-04 6000-04 6000-04 Corporate:Business Development
4 Advertising & Marketing:B2B Marketing 6000-08 6000-08 6000-08 6000-08 Corporate:Clinical Operations
5 Advertising & Marketing:Creative Design 6000-09 6000-09 6000-09 6000-09 Corporate:Corporate Operations
6 Advertising & Marketing:Marketing Events 6000-05 6000-05 6000-05 6000-05 Corporate:Finance & Accounting
7 Advertising & Marketing:Other Marketing 6000-99 6000-99 6000-99 6000-99 Corporate:General Corporate
8 Advertising & Marketing:Printed Advertising 6000-10 6000-10 6000-10 6000-10 Corporate:Holdings
9 Advertising & Marketing:Provider Referal Corporate:Marketing
10 Advertising & Marketing:SEO & Ad Words 6000-01 6000-01 6000-01 6000-01 Corporate:People Ops
11 Advertising & Marketing:Social Media 6000-03 6000-03 6000-03 6000-03 Corporate:Product & Engineering
12 Business Development:Data Analytics/Market Research 6001-01 6001-01 6001-01 6001-01 Corporate:Revenue Cycle Management
13 Business Development:Product & Engineering 6001-03 6001-03 6001-03 6001-03 Corporate:Senior Leadership
14 Business Development:Recruitment Events 6001-04 6001-04 6001-04 6001-04 Corporate:Other (Please Comment)
15 Business Development:Travel to New Markets 6001-02 6001-02 6001-02 6001-02 Medical Clinics:Austin
16 Cost of Goods Sold:Management Expense 5005 5005 5005 5005 Medical Clinics:Brentwood
17 Cost of Goods Sold:Medical Supplies 5020 5020 5020 5020 Medical Clinics:Rollingwood
18 Cost of Goods Sold:Medical Supplies & Product Shipping 5010 5010 5010 5010 Medical Clinics:Round Rock
19 Cost of Goods Sold:Provider Wages, Taxes, & Benefits:Provider Salaries & Wages:Provider Contractors 5300-03 5300-03 5300-03 5300-03 Medical Clinics:San Antonio
20 Cost of Goods Sold:Retail Inventory 5030 5030 5030 5030 Medical Clinics:San Francisco
21 Credit Card Payment Medical Clinics:Sugar Land
22 Equipment 1560 1560 1560 1560 Medical Clinics:West Hollywood
23 Equipment:Bebe Holding Medical Clinics:West Loop
24 Facilities:Cleaning 6152 6152 6152 6152 Medical Clinics:Other (Please Comment)
25 Facilities:Common Area Maintenance & RE Tax Share 6151 6151 6151 6151 Remote
26 Facilities:Rent & Lease 6140 6140 6140 6140
27 Facilities:Repairs & Maintenance 6150 6150 6150 6150
28 Facilities:Telephone & Internet 6201 6201 6201 6201
29 Facilities:Utilities 6200 6200 6200 6200
30 General & Administrative:Education & Training:Continuing Medical Education 6550-01 6550-01 6550-01 6550-01
31 General & Administrative:Education & Training:Corporate Training 6550-05 6550-05 6550-05 6550-05
32 General & Administrative:Education & Training:Offsite Meetings 6550-03 6550-03 6550-03 6550-03
33 General & Administrative:Education & Training:Professional Development 6550-04 6550-04 6550-04 6550-04
34 General & Administrative:Meals & Entertainment 6540 6540 6540 6540
35 General & Administrative:Meals & Entertainment:Charitable Contribution 6540-20 6540-20 6540-20 6540-20
36 General & Administrative:Meals & Entertainment:Employee Awards & Gifts 6540-11 6540-11 6540-11 6540-11
37 General & Administrative:Meals & Entertainment:Entertainment 6540-02 6540-02 6540-02 6540-02
38 General & Administrative:Meals & Entertainment:Gifts & Tips 6540-10 6540-10 6540-10 6540-10
39 General & Administrative:Meals & Entertainment:Holiday Events 6540-03 6540-03 6540-03 6540-03
40 General & Administrative:Meals & Entertainment:Meals 6540-01 6540-01 6540-01 6540-01
41 General & Administrative:Meals & Entertainment:Other Meals & Entertainment 6540-99 6540-99 6540-99 6540-99
42 General & Administrative:Meals & Entertainment:Team Building Meals 6540-04 6540-04 6540-04 6540-04
43 General & Administrative:Professional Service:Bank Charges & Fees 6520-06 6520-06 6520-06 6520-06
44 General & Administrative:Professional Service:Billing & Collections 6520-03 6520-03 6520-03 6520-03
45 General & Administrative:Professional Service:Consulting 6520-09 6520-09 6520-09 6520-09
46 General & Administrative:Professional Service:Credit Card Fees 6520-07 6520-07 6520-07 6520-07
47 General & Administrative:Professional Service:Legal Fees 6520-01 6520-01 6520-01 6520-01
48 General & Administrative:Professional Service:Other Professional Services 6520-99 6520-99 6520-99 6520-99
49 General & Administrative:Recruiting:Other Recruiting 6560-99 6560-99 6560-99 6560-99
50 General & Administrative:Recruiting:Recruiting Ads 6560-01 6560-01 6560-01 6560-01
51 General & Administrative:Recruiting:Recruiting Travel 6560-02 6560-02 6560-02 6560-02
52 General & Administrative:Taxes & Reports:Annual Reports & Filings 6570-01 6570-01 6570-01 6570-01
53 General & Administrative:Travel:Auto Related Expenses 6510-01 6510-01 6510-01 6510-01
54 General & Administrative:Travel:Flights/Trains 6510-04 6510-04 6510-04 6510-04
55 General & Administrative:Travel:Hotel/Lodging 6510-05 6510-05 6510-05 6510-05
56 General & Administrative:Travel:Other Travel 6510-99 6510-99 6510-99 6510-99
57 General & Administrative:Travel:Parking 6510-06 6510-06 6510-06 6510-06
58 General & Administrative:Travel:Ride Share 6510-02 6510-02 6510-02 6510-02
59 Income Taxes:Federal Income Tax 7550-01 7550-01 7550-01 7550-01
60 Income Taxes:Local Income Taxes 7550-03 7550-03 7550-03 7550-03
61 Income Taxes:State Income Tax 7550-02 7550-02 7550-02 7550-02
62 Interest Expense 7500-02 7500-02 7500-02 7500-02
63 Miscellaneous AR 1081 1081 1081 1081
64 Operating:Covid Testing 6400 6400 6400 6400
65 Operating:Dues and Subscriptions 6413 6413 6413 6413
66 Operating:Office Supplies 6411 6411 6411 6411
67 Operating:Postage & Shipping 6412 6412 6412 6412
68 Operating:Printing 6414 6414 6414 6414
69 Operating:Technology Expenses 6430 6430 6430 6430
70 Operating:Technology Expenses:Expensed Tech Equipment 6430-02 6430-02 6430-02 6430-02
71 Operating:Technology Expenses:Internet Subscriptions 6430-04 6430-04 6430-04 6430-04
72 Operating:Technology Expenses:IT 6430-01 6430-01 6430-01 6430-01
73 Operating:Technology Expenses:Other Tech Expenses 6430-99 6430-99 6430-99 6430-99
74 Operating:Technology Expenses:Software as a Service 6430-03 6430-03 6430-03 6430-03
75 Prepaid Expenses 1100 1100 1100 1100
76 Receivables - Purchase 1040 1040 1040 1040
77 Revenue:Discounts/Refunds Given 4500 4500 4500 4500
78 Revenue:Other Revenue:Retail Sales 4020 4020 4020 4020
79 Suspense Transaction (Please Comment)
80 Taxes & Licenses (Please Comment)
81 Uncategorized Asset 1050 1050 1050 1050
82 Uncategorized Expense 6190 6190 6190 6190
83 Wages, Taxes, & Benefits:Benefits 6320 6320 6320 6320
84 Wages, Taxes, & Benefits:Benefits:Other Benefits 6320-99 6320-99 6320-99 6320-99
85 Wages, Taxes, & Benefits:Salaries & Wages:Contractors 6300-04 6300-04 6300-04 6300-04
Category VLookup

File name correction: "Chase - CC Activity - 07.01.23 - 07.31.23 v2 (TEST).xlsm"

Script correction to column "M":

Public Sub Split_By_Card_Number_to_New_Workbooks()

Dim saveInFolder As String
Dim splitSheet As Worksheet
Dim cardNoDict As Object
Dim cardNoCell As Range
Dim cardNos As Variant, m As Long

saveInFolder = Environ("USERPROFILE") & "\Downloads\"

Set splitSheet = ThisWorkbook.Worksheets("Current Month Expenses")

'Create dictionary of unique card numbers in column M

Set cardNoDict = CreateObject("Scripting.Dictionary")
With splitSheet
For Each cardNoCell In .Range("M2", .Cells(.Rows.Count, "M").End(xlUp))
cardNoDict(cardNoCell.Value) = 1
Next
End With

Application.ScreenUpdating = False

'Loop through each unique card number

cardNos = cardNoDict.Keys
For m = LBound(cardNos) To UBound(cardNos)
'Copy sheet, creating a new workbook and save it with this card number in file name
splitSheet.Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs saveInFolder & Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1) & " - " & cardNos(m) & ".xlsx", xlOpenXMLWorkbook
Application.DisplayAlerts = True

With ActiveWorkbook.Worksheets(1).UsedRange
'Copy and paste as values to remove formulas and links to original workbook
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
Application.CutCopyMode = False

'Autofilter on column M for rows NOT equal to this card number and delete visible rows
.AutoFilter Field:=13, Criteria1:="<>" & cardNos(m) & "*", Operator:=xlFilterValues 'Note - without "*" filter doesn't work
.Offset(1).EntireRow.Delete
.AutoFilter
End With
ActiveWorkbook.Close True
Next

Application.ScreenUpdating = True

MsgBox "Created new workbooks in " & saveInFolder, vbInformation

End Sub

John_w · Aug 5, 2023

jennifer7i884 said:
2. Bring a second tab with it named "Category VLookup"
3. So my drop-down's in columns "I" and "J" (eye and jay) will still pull from "Category VLookup"

Simply replace:

VBA Code:

        'Copy sheet, creating a new workbook and save it with this card number in file name
        splitSheet.Copy

with:

VBA Code:

        'Copy 2 sheets, creating a new workbook and save it with this card number in file name
        Worksheets(Array(splitSheet.Name, "Category VLookup")).Copy

PS please use VBA code tags - the VBA icon in the message editor.

jennifer7i884 · Aug 5, 2023

John_w said:

Simply replace:

VBA Code:

        'Copy sheet, creating a new workbook and save it with this card number in file name
        splitSheet.Copy

with:

VBA Code:

        'Copy 2 sheets, creating a new workbook and save it with this card number in file name
        Worksheets(Array(splitSheet.Name, "Category VLookup")).Copy

PS please use VBA code tags - the VBA icon in the message editor.

You are my hero. My absolute HERO. I'm so excited about this I could cry. Thank you for helping me so, so much!!! You made this easy for someone like me who can do computer/software stuff but definitely isn't technically inclined on your level. I truly appreciate your help!!

PS - I added "vba" and "vba code" to the tags at the top, too.

Take Excel Worksheet, Divide into Worksheets/Workbooks by Employee/Card #, Email via Gmail to Employee for Input

jennifer7i884

New Member

Excel Facts

John_w

MrExcel MVP

jennifer7i884

New Member

jennifer7i884

New Member

John_w

MrExcel MVP

jennifer7i884

New Member

jennifer7i884

New Member

jennifer7i884

New Member

John_w

MrExcel MVP

jennifer7i884

New Member

Similar threads

Forum statistics

Share this page

Cells with Data Validation
Cell	Allow	Criteria
K2	Any value
I2:I169	List	='Category VLookup'!$A$2:$A$1048576
J2:J169	List	='Category VLookup'!$F$2:$F$1048576

Take Excel Worksheet, Divide into Worksheets/Workbooks by Employee/Card #, Email via Gmail to Employee for Input

New Member

Excel Facts

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

New Member

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock