Create another sheet when cell value in A changes

T

tr1face

New Member
Joined
Jan 7, 2021
Messages
18
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Takae said:
Which sheet has invoice number in columnA?
Click to expand...
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 :(
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
Solution
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?
Click to expand...
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
 
Upvote 0
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?
Click to expand...
I am replicating the excel and will add in few minutes
 
Upvote 0
Adding the excel example:

Template:
Detailed Macro (1).xlsm
ABCDEFGHIJKLMNOPQRSTU
1Instructions Line
2Invoice#System#Contract #Charge TypeDate DueBill PeriodAmt TaxTotalPoModelSerial Tag(No blanks)RefAsset IdCust DefinedAddress 1Address 2City State ZipTax Info
3ABSystem1CN-1111AA1/31/20213067267.2739.2PO-11111RAW DATAST1234No-1234Asset Id-1AAAAAAdd1-1Add2-1City aState 1111-999RAW DATA
4ABSystem2CN-2222BB1/15/20213043043473PO-22222RAW DATAST45678No-5678Asset Id-2BBBBBAdd2-1Add2-2City bState 2222-888RAW DATA
5BCSystem3CN-3333CC1/20/20213037737.7414.7PO-33333RAW DATAST891234No-9012Asset Id-3CCCCCAdd1-2Add2-3City cState 3333-777RAW DATA
6BCSystem4CN-4444DD1/25/20213054554.5599.5PO-44444RAW DATAST567No-4567Asset Id-4DDDDDAdd2-2Add2-4City dState 4444-666RAW DATA
7CDSystem5CN-5555EE2/10/20216066766.7733.7PO-55555RAW DATAST2346No-8901Asset Id-5EEEEEAdd1-3Add2-5City eState 5555-555RAW DATA
8CDSystem6CN-6666FF2/15/20216061461.4675.4PO-66666RAW DATAST20957No-2345Asset Id-6FFFFFAdd2-3Add2-6City fState 6666-000RAW DATA
Template


Detail Info:
Detailed Macro (1).xlsm
ABCDEF
1Detail Information
2
3EquipmentEquipmentTransactionTRANSACTION
4InformationLocationDescriptionAmountTaxTotal
5
6PO# PO-11111CN-1111AA$672.00$67.20$739.20
7MOD# RAW DATAAdd1-1
8TAG# ST1234Add2-1
9REF# No-1234City a, State 1 111-999
10Asset Id-1RAW DATA
11AAAAA
12
13PO# PO-22222CN-2222BB$430.00$43.00$473.00
14MOD# RAW DATAAdd2-1
15TAG# ST45678Add2-2
16REF# No-5678City b, State 2 222-888
17Asset Id-2RAW DATA
18BBBBB
19
20PO# PO-33333CN-3333CC$377.00$37.70$414.70
21MOD# RAW DATAAdd1-2
22TAG# ST891234Add2-3
23REF# No-9012City c, State 3 333-777
24Asset Id-3RAW DATA
25CCCCC
26
27PO# PO-44444CN-4444DD$545.00$54.50$599.50
28MOD# RAW DATAAdd2-2
29TAG# ST567Add2-4
30REF# No-4567City d, State 4 444-666
31Asset Id-4RAW DATA
32DDDDD
33
34PO# PO-55555CN-5555EE$667.00$66.70$733.70
35MOD# RAW DATAAdd1-3
36TAG# ST2346Add2-5
37REF# No-8901City e, State 5 555-555
38Asset Id-5RAW DATA
39EEEEE
40
41PO# PO-66666CN-6666FF$614.00$61.40$675.40
42MOD# RAW DATAAdd2-3
43TAG# ST20957Add2-6
44REF# No-2345City f, State 6 666-000
45Asset Id-6RAW DATA
46FFFFF
47
48Invoice Total$3,305.00$330.50$3,635.50
Detail Info
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Object required error
Replies
2
Views
139
Boston_Paul
B
L
  • Question Question
Compile Error when Adding Attachments
Replies
5
Views
182
6StringJazzer
6StringJazzer
L
  • Question Question
Skip Sending an Email If Status is Yes (already sent)
Replies
3
Views
224
Trebor76
T
I
  • Question Question
Value not shown in ComboBox1 when code run
Replies
1
Views
43
ipbr21054
I
H
  • Question Question
Export Grouped Worksheets Based on Data Set
Replies
1
Views
210
mumps
mumps

Forum statistics

Threads
1,223,627
Messages
6,173,425
Members
452,515
Latest member
Alicedonald9

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