Create another sheet when cell value in A changes

tr1face · Jan 7, 2021

Hello everyone,
I have the following VBA code which I use to create a detail section for invoices. My problem is that it can only be used for the crt of 1 invoice at the time and I would like to create multiple. Invoice number is located in column A, it would work just great to create another detailed whenever that invoice number changes. Thank you for all your help!

VBA Code:

Sub InvoiceDetails()

    Dim last_row As Long        ' Number of last row that contains detail data
    Dim r As Long               ' Row counter
    Dim det_cell As Range       ' Tracks where data is to be written on the invoice detail sheet
    Dim info_cells As Range     ' Used to copy PO, MOD, TAG, REF, COST CENTER, CUST DEFINED from template
    Dim location_cells As Range ' Used to copy contract#, addr1, addr2, city/st/zip, tax info from template
    Dim amount_cells As Range   ' Used to copy the amount, tax and total of a transaction from the template
    Dim x As Integer
    Application.ScreenUpdating = False
   
    Sheets("Template").Select
    Sheets("Template").Copy after:=Sheets(2)
    Sheets("Template (2)").Select
    Sheets("Template (2)").Name = "Copy of Template"
  
    Range("L3").Select
    Range(Selection, Selection.End(xlDown)).Select
    last_row = Selection.Count + 2
   
    ' Add "PO# ", "MOD# ", etc. to front of data on template
   
    Range("J3").Select
    For r = 0 To last_row - 3
        Range("J3").Offset(r, 0) = Range("J3").Offset(r, 0)
    Next r
       
    Range("K3").Select
    For r = 0 To last_row - 3
        Range("K3").Offset(r, 0) = Range("K3").Offset(r, 0)
    Next r
   
    Range("L3").Select
    For r = 0 To last_row - 3
        Range("L3").Offset(r, 0) = Range("L3").Offset(r, 0)
    Next r
   
    Range("M3").Select
    For r = 0 To last_row - 3
        Range("M3").Offset(r, 0) = Range("M3").Offset(r, 0)
    Next r
   
    ' Concatenate City, State, Zip on template
   
    Columns("R:R").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("R3").Clear
    Range("R3").FormulaR1C1 = "=RC[1]&"", ""&RC[2]&"" ""&RC[3]"
    Range("R3").Copy Range("R4:R" & last_row)
    Range("R3:R" & last_row).Copy
    Range("R3:R" & last_row).PasteSpecial xlPasteValues
    Columns("S:U").Delete
   
    ' Insert/copy Contract Number column to the left of Shipping Addr1
   
    Columns("P:P").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("P3").Select
    ActiveSheet.Paste
   
    ' Populate invoice detail
   
    Set det_cell = Sheets("Detail Info").Range("A6")
    Set info_cells = Sheets("Copy of Template").Range("J3:O3")      ' PO, Mod, Tax info, Ref, Cost Center, User Def of the first charge
    Set location_cells = Sheets("Copy of Template").Range("P3:T3")  ' Contract#, location, tax info of first charge
    Set amount_cells = Sheets("Copy of Template").Range("G3:I3")    ' Base, tax, total of first charge
   
    For r = 0 To last_row - 3
        info_cells.Copy
        det_cell.PasteSpecial Transpose:=True
       
        location_cells.Copy
        det_cell.Offset(0, 1).PasteSpecial Transpose:=True
       
        'If charge is rental or renewal, concatenate the charge type and billing period. Otherwise, just copy charge description.
        Sheets("Copy of Template").Activate
        If Left(Range("D" & r + 3).Value, 3) = "REN" Or _
           Left(Range("D" & r + 3).Value, 3) = "Ren" Or _
           Left(Range("D" & r + 3).Value, 3) = "ren" Then
            det_cell.Offset(0, 2).Value = Range("D" & r + 3).Value & "  " & Range("F" & r + 3).Value
        Else
            det_cell.Offset(0, 2).Value = Range("D" & r + 3).Value
        End If
   
       
        amount_cells.Copy
        det_cell.Offset(0, 3).PasteSpecial
       
        Set info_cells = info_cells.Offset(1, 0)
        Set location_cells = location_cells.Offset(1, 0)
        Set amount_cells = amount_cells.Offset(1, 0)
        Set det_cell = det_cell.Offset(7, 0)
    Next r
   
    'Formatting
    Sheets("Detail Info").Activate
    Range(Cells(6, 1), Cells((last_row - 2) * 7 + 7, 6)).Select
    Selection.Font.Name = "Museo Sans For Dell"
    Selection.Font.Size = 10
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlBottom
   
    Range(Cells(6, 4), Cells((last_row - 2) * 7 + 7, 6)).Select
    Selection.NumberFormat = "$#,##0.00"
   
    'Enter invoice totals
    Cells((last_row - 2) * 7 + 6, 3).Select
    Selection.Value = "Invoice Total"
    Selection.Font.bold = True
   
    Cells((last_row - 2) * 7 + 6, 4).Select
    Selection.Font.bold = True
    Selection.Value = WorksheetFunction.Sum(Range(Cells(6, 4), Cells((last_row - 2) * 7 + 5, 4)))
    Cells((last_row - 2) * 7 + 6, 5).Select
    Selection.Font.bold = True
    Selection.Value = WorksheetFunction.Sum(Range(Cells(6, 5), Cells((last_row - 2) * 7 + 5, 5)))
    Cells((last_row - 2) * 7 + 6, 6).Select
    Selection.Font.bold = True
    Selection.Value = WorksheetFunction.Sum(Range(Cells(6, 6), Cells((last_row - 2) * 7 + 5, 6)))

    Application.DisplayAlerts = False
    Sheets("Copy of Template").Delete
    Application.DisplayAlerts = True
   
    Application.ScreenUpdating = True
    Sheets("Detail Info").Activate
    Range("G6").Select
   
    'Set print area
    Worksheets("Detail Info").PageSetup.PrintArea = "$A$1:$F$" & (last_row - 2) * 7 + 7

    Dim iX As Double
    Dim y As Double
    iX = 6
    y = 3
    Do While Worksheets("Detail Info").Cells(iX, 6) <> ""
   
        Worksheets("Detail Info").Cells(iX, 1) = "PO# " & Worksheets("Template").Cells(y, 10)
        Worksheets("Detail Info").Cells(iX + 1, 1) = "MOD# " & Worksheets("Detail Info").Cells(iX + 1, 1)
        Worksheets("Detail Info").Cells(iX + 2, 1) = "TAG# " & Worksheets("Detail Info").Cells(iX + 2, 1)
        Worksheets("Detail Info").Cells(iX + 3, 1) = "REF# " & Worksheets("Detail Info").Cells(iX + 3, 1)
        Worksheets("Detail Info").Cells(iX, 2) = Worksheets("Template").Cells(y, 3)
       
    y = y + 1
    iX = iX + 7
    Loop
   
    Worksheets("Detail Info").Cells(1, 1) = "Detail Information"
    Worksheets("Detail Info").Cells(1, 1).Font.Size = 18
   
    Worksheets("Detail Info").Cells(3, 1) = "Equipment"
    Worksheets("Detail Info").Cells(4, 1) = "Information"
    Worksheets("Detail Info").Cells(3, 2) = "Equipment"
    Worksheets("Detail Info").Cells(4, 2) = "Location"
    Worksheets("Detail Info").Cells(3, 3) = "Transaction"
    Worksheets("Detail Info").Cells(4, 3) = "Description"
    Worksheets("Detail Info").Cells(3, 4) = "TRANSACTION"
    Worksheets("Detail Info").Cells(4, 4) = "Amount"
    Worksheets("Detail Info").Cells(4, 5) = "Tax"
    Worksheets("Detail Info").Cells(4, 6) = "Total"
   
    Worksheets("Detail Info").Range("A1:F2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
   
    Worksheets("Detail Info").Range("D3:F3").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
   
    Worksheets("Detail Info").Range("A1:F4").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
   
    With Selection.Borders
        .LineStyle = xlContinuous
        .Color = vbBlack
        .Weight = xlThin
    End With
   
   
   
   
   
End Sub

Takae · Jan 12, 2021

Which sheet has invoice number in columnA?

tr1face · Jan 12, 2021

Takae said:
Which sheet has invoice number in columnA?

Template

tr1face · Jan 12, 2021

Takae said:
Which sheet has invoice number in columnA?

While "Template" sheet is being doubled right from the start leaving code to use "Copy of Template" moving forward. I already have a code that loops through excel sheets from a folder and it would be just as helpful if the above code (can be modif) and it would loop through the workbook in which i've spared the data already in similar sheets, if it can create and add the "detail sheet" for each, next to them. Data will be added in the exact same manner, only in different sheets

? I am sorry I cannot provide the excel example, as I don't have access to instal the plugin

tr1face · Jan 12, 2021

But i assume it would be easier to create those in the same workbook based off same template, i am looking forward to hear your solution as I tried every method and failed

Takae · Jan 13, 2021

I have rewritten all of your code but I was not able to imagine. I guessed you would like to make files/sheets as much as count of data in column A. If the idea is correct, it would be easy. Count data in columnA then loop for making files/sheets. But columnA in Template has invoice number...I confused. Are you going to put other code before posted code?

tr1face · Jan 13, 2021

Takae said:
I have rewritten all of your code but I was not able to imagine. I guessed you would like to make files/sheets as much as count of data in column A. If the idea is correct, it would be easy. Count data in columnA then loop for making files/sheets. But columnA in Template has invoice number...I confused. Are you going to put other code before posted code?

Yes, but I've already tried that way and the excel crashes and I'm not sure if that's the best route. There are 2 sheets, one named Template where the raw data is added, and Detail Info where it's basically transposed. Your idea is pretty close but I would like the code to create as many Detail Info pages as invoice numbers (or as many unique values) are in column A in the sheet Template if this makes any sense. Thank you for all the work you're putting into

tr1face · Jan 13, 2021

Takae said:
I have rewritten all of your code but I was not able to imagine. I guessed you would like to make files/sheets as much as count of data in column A. If the idea is correct, it would be easy. Count data in columnA then loop for making files/sheets. But columnA in Template has invoice number...I confused. Are you going to put other code before posted code?

I am replicating the excel and will add in few minutes

tr1face · Jan 14, 2021

Adding the excel example:

Template:

Detailed Macro (1).xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

Instructions Line

2

Invoice#

System#

Contract #

Charge Type

Date Due

Bill Period

Amt

Tax

Total

Po

Model

Serial Tag(No blanks)

Ref

Asset Id

Cust Defined

Address 1

Address 2

City

State

Zip

Tax Info

3

AB

System1

CN-1111

AA

1/31/2021

30

672

67.2

739.2

PO-11111

RAW DATA

ST1234

No-1234

Asset Id-1

AAAAA

Add1-1

Add2-1

City a

State 1

111-999

RAW DATA

4

AB

System2

CN-2222

BB

1/15/2021

30

430

43

473

PO-22222

RAW DATA

ST45678

No-5678

Asset Id-2

BBBBB

Add2-1

Add2-2

City b

State 2

222-888

RAW DATA

5

BC

System3

CN-3333

CC

1/20/2021

30

377

37.7

414.7

PO-33333

RAW DATA

ST891234

No-9012

Asset Id-3

CCCCC

Add1-2

Add2-3

City c

State 3

333-777

RAW DATA

6

BC

System4

CN-4444

DD

1/25/2021

30

545

54.5

599.5

PO-44444

RAW DATA

ST567

No-4567

Asset Id-4

DDDDD

Add2-2

Add2-4

City d

State 4

444-666

RAW DATA

7

CD

System5

CN-5555

EE

2/10/2021

60

667

66.7

733.7

PO-55555

RAW DATA

ST2346

No-8901

Asset Id-5

EEEEE

Add1-3

Add2-5

City e

State 5

555-555

RAW DATA

8

CD

System6

CN-6666

FF

2/15/2021

60

614

61.4

675.4

PO-66666

RAW DATA

ST20957

No-2345

Asset Id-6

FFFFF

Add2-3

Add2-6

City f

State 6

666-000

RAW DATA

Template

Detail Info:

Detailed Macro (1).xlsm

A

B

C

D

E

F

1

Detail Information

2

3

Equipment

Transaction

TRANSACTION

4

Information

Location

Description

Amount

Tax

Total

5

6

PO# PO-11111

CN-1111

AA

$672.00

$67.20

$739.20

7

MOD# RAW DATA

Add1-1

8

TAG# ST1234

Add2-1

9

REF# No-1234

City a, State 1 111-999

10

Asset Id-1

RAW DATA

11

AAAAA

12

13

PO# PO-22222

CN-2222

BB

$430.00

$43.00

$473.00

14

MOD# RAW DATA

Add2-1

15

TAG# ST45678

Add2-2

16

REF# No-5678

City b, State 2 222-888

17

Asset Id-2

RAW DATA

18

BBBBB

19

20

PO# PO-33333

CN-3333

CC

$377.00

$37.70

$414.70

21

MOD# RAW DATA

Add1-2

22

TAG# ST891234

Add2-3

23

REF# No-9012

City c, State 3 333-777

24

Asset Id-3

RAW DATA

25

CCCCC

26

27

PO# PO-44444

CN-4444

DD

$545.00

$54.50

$599.50

28

MOD# RAW DATA

Add2-2

29

TAG# ST567

Add2-4

30

REF# No-4567

City d, State 4 444-666

31

Asset Id-4

RAW DATA

32

DDDDD

33

34

PO# PO-55555

CN-5555

EE

$667.00

$66.70

$733.70

35

MOD# RAW DATA

Add1-3

36

TAG# ST2346

Add2-5

37

REF# No-8901

City e, State 5 555-555

38

Asset Id-5

RAW DATA

39

EEEEE

40

41

PO# PO-66666

CN-6666

FF

$614.00

$61.40

$675.40

42

MOD# RAW DATA

Add2-3

43

TAG# ST20957

Add2-6

44

REF# No-2345

City f, State 6 666-000

45

Asset Id-6

RAW DATA

46

FFFFF

47

48

Invoice Total

$3,305.00

$330.50

$3,635.50

Detail Info

Create another sheet when cell value in A changes

tr1face

New Member

Excel Facts

Takae

Well-known Member

tr1face

New Member

tr1face

New Member

tr1face

New Member

Takae

Well-known Member

tr1face

New Member

tr1face

New Member

tr1face

New Member

Similar threads

Forum statistics

Share this page

Create another sheet when cell value in A changes

New Member

Excel Facts

Well-known Member

New Member

New Member

New Member

Well-known Member

New Member

New Member

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