VBA or Macro Question - formula and labels with varying ranges on multiple worksheets

  • I have multiple worksheets with varying rows of data, one worksheet may have 10 rows of data, the next may have 100 rows of data. Each worksheet is named based on the "Client" field data.


    I am trying to run a VBA or macro that will add labels and formulas to multiple worksheets based on the range of data for each respective worksheet and insert the data after the last row of data.


    Example - worksheet 1 (named boy) has 5 rows of data. I have 4 rows that need to be added to the bottom of each worksheet after the last row of data. Each row has a different formula.
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="class: xl63, width: 29"] [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 736"]

    [tr]


    [TD="class: xl65, width: 29"] [/TD]
    [TD="class: xl65, width: 29"]A[/TD]
    [TD="class: xl66, width: 31"]B[/TD]
    [TD="class: xl65, width: 50"]C[/TD]
    [TD="class: xl65, width: 77"]D[/TD]
    [TD="class: xl65, width: 104"]E[/TD]
    [TD="class: xl65, width: 40"]F[/TD]
    [TD="class: xl65, width: 215"]G[/TD]
    [TD="class: xl65, width: 75"]H[/TD]
    [TD="class: xl65, width: 40"]I[/TD]
    [TD="class: xl65, width: 72"]J[/TD]
    [TD="class: xl65, width: 81"]K[/TD]
    [TD="class: xl65, width: 80"]L[/TD]
    [TD="class: xl65, width: 86"]M[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, width: 29"]1[/TD]
    [TD="class: xl65, width: 29"]Rep[/TD]
    [TD="class: xl66, width: 31"]Recruiter[/TD]
    [TD="class: xl65, width: 50"]Client[/TD]
    [TD="class: xl65, width: 77"]Date[/TD]
    [TD="class: xl65, width: 104"]Name[/TD]
    [TD="class: xl65, width: 40"]Inv Num[/TD]
    [TD="class: xl65, width: 215"]Item[/TD]
    [TD="class: xl65, width: 75"]Memo[/TD]
    [TD="class: xl65, width: 40"]Qty[/TD]
    [TD="class: xl65, width: 72"]Price[/TD]
    [TD="class: xl65, width: 81"]Amount[/TD]
    [TD="class: xl65, width: 80"]Pay[/TD]
    [TD="class: xl65, width: 86"]Rate[/TD]

    [/tr]


    [tr]


    [TD="class: xl67"]2[/TD]
    [TD="class: xl67"] [/TD]
    [TD="class: xl67"] [/TD]
    [TD="class: xl67"] [/TD]
    [TD="class: xl67"] [/TD]
    [TD="class: xl67"] [/TD]
    [TD="class: xl67"] [/TD]
    [TD="class: xl67"] [/TD]
    [TD="class: xl67"] [/TD]
    [TD="class: xl68"] [/TD]
    [TD="class: xl69"] [/TD]
    [TD="class: xl69"] [/TD]
    [TD="class: xl69"] [/TD]
    [TD="class: xl67"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl70"]3[/TD]
    [TD="class: xl70"]Z[/TD]
    [TD="class: xl70"]Y[/TD]
    [TD="class: xl70"]mom[/TD]
    [TD="class: xl70"]1/1/19[/TD]
    [TD="class: xl70"]ABC Co.[/TD]
    [TD="class: xl70"]1[/TD]
    [TD="class: xl70"]misc hourly[/TD]
    [TD="class: xl70"]Hourly Rate[/TD]
    [TD="class: xl71, align: right"]1[/TD]
    [TD="class: xl72, align: right"]10.00[/TD]
    [TD="class: xl72, align: right"]10.00[/TD]
    [TD="class: xl73, align: right"]100.00[/TD]
    [TD="class: xl70"]100.00[/TD]

    [/tr]


    [tr]


    [TD="class: xl70"]4[/TD]
    [TD="class: xl70"]Z[/TD]
    [TD="class: xl70"]Y[/TD]
    [TD="class: xl70"]mom[/TD]
    [TD="class: xl70"]1/2/19[/TD]
    [TD="class: xl70"]ABC Co.[/TD]
    [TD="class: xl70"]2[/TD]
    [TD="class: xl70"]misc hourly[/TD]
    [TD="class: xl70"]Hourly Rate[/TD]
    [TD="class: xl71, align: right"]1[/TD]
    [TD="class: xl72, align: right"]10.00[/TD]
    [TD="class: xl72, align: right"]10.00[/TD]
    [TD="class: xl73, align: right"]100.00[/TD]
    [TD="class: xl70"]100.00[/TD]

    [/tr]


    [tr]


    [TD="class: xl70"]5[/TD]
    [TD="class: xl70"]Z[/TD]
    [TD="class: xl70"]Y[/TD]
    [TD="class: xl70"]mom[/TD]
    [TD="class: xl70"]1/3/19[/TD]
    [TD="class: xl70"]ABC Co.[/TD]
    [TD="class: xl70"]3[/TD]
    [TD="class: xl70"]misc hourly[/TD]
    [TD="class: xl70"]Hourly Rate[/TD]
    [TD="class: xl71, align: right"]1[/TD]
    [TD="class: xl72, align: right"]10.00[/TD]
    [TD="class: xl72, align: right"]10.00[/TD]
    [TD="class: xl73, align: right"]100.00[/TD]
    [TD="class: xl70"]100.00[/TD]

    [/tr]


    [tr]


    [TD="class: xl70"]6[/TD]
    [TD="class: xl70"]Z[/TD]
    [TD="class: xl70"]Y[/TD]
    [TD="class: xl70"]mom[/TD]
    [TD="class: xl70"]1/4/19[/TD]
    [TD="class: xl70"]ABC Co.[/TD]
    [TD="class: xl70"]4[/TD]
    [TD="class: xl70"]misc hourly[/TD]
    [TD="class: xl70"]Hourly Rate[/TD]
    [TD="class: xl71, align: right"]1[/TD]
    [TD="class: xl72, align: right"]10.00[/TD]
    [TD="class: xl72, align: right"]10.00[/TD]
    [TD="class: xl73, align: right"]100.00[/TD]
    [TD="class: xl70"]100.00[/TD]

    [/tr]


    [tr]


    [TD="class: xl70"]7[/TD]
    [TD="class: xl70"]Z[/TD]
    [TD="class: xl70"]Y[/TD]
    [TD="class: xl70"]mom[/TD]
    [TD="class: xl70"]1/5/19[/TD]
    [TD="class: xl70"]ABC Co.[/TD]
    [TD="class: xl70"]5[/TD]
    [TD="class: xl70"]travel[/TD]
    [TD="class: xl70"]travel[/TD]
    [TD="class: xl71, align: right"]1[/TD]
    [TD="class: xl72, align: right"]500.00[/TD]
    [TD="class: xl72, align: right"]500.00[/TD]
    [TD="class: xl73, align: right"]500.00[/TD]
    [TD="class: xl70"]500.00[/TD]

    [/tr]


    [tr]


    [TD="class: xl70"]8[/TD]
    [TD="class: xl70"]Z[/TD]
    [TD="class: xl70"]Y[/TD]
    [TD="class: xl70"]mom[/TD]
    [TD="class: xl70"]1/5/19[/TD]
    [TD="class: xl70"]ABC Co.[/TD]
    [TD="class: xl70"]6[/TD]
    [TD="class: xl70"]lodging[/TD]
    [TD="class: xl70"]lodging[/TD]
    [TD="class: xl71, align: right"]1[/TD]
    [TD="class: xl72, align: right"]200.00[/TD]
    [TD="class: xl72, align: right"]0.00[/TD]
    [TD="class: xl73, align: right"]0.00[/TD]
    [TD="class: xl70"]0.00[/TD]

    [/tr]


    [tr]


    [TD="class: xl70"]9[/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"]Billings[/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl71, align: right"] [/TD]
    [TD="class: xl72, align: right"] [/TD]
    [TD="class: xl72"]=sum(K3:K8)[/TD]
    [TD="class: xl73, align: right"] [/TD]
    [TD="class: xl70"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl70"]10[/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"]Insurance[/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl71, align: right"]4[/TD]
    [TD="class: xl72, align: right"]5.00[/TD]
    [TD="class: xl72"]=(-I10*J10)[/TD]
    [TD="class: xl73"]=sum(L3:L8)[/TD]
    [TD="class: xl70"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl70"]11[/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"]Pay[/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl71, align: right"] [/TD]
    [TD="class: xl72, align: right"] [/TD]
    [TD="class: xl72"]=-L9[/TD]
    [TD="class: xl73"] [/TD]
    [TD="class: xl70"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl70"]12[/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl70"] [/TD]
    [TD="class: xl71, align: right"] [/TD]
    [TD="class: xl72, align: right"]FF[/TD]
    [TD="class: xl72"]=sum(K9:K11)[/TD]
    [TD="class: xl73"]=(K12/K9)[/TD]
    [TD="class: xl70"] [/TD]

    [/tr]


    [/TABLE]
    [/TD]
    [TD="class: xl64, width: 31"] [/TD]
    [TD="class: xl63, width: 50"] [/TD]
    [TD="class: xl63, width: 77"] [/TD]
    [TD="class: xl63, width: 104"] [/TD]
    [TD="class: xl63, width: 40"] [/TD]
    [TD="class: xl63, width: 215"] [/TD]
    [TD="class: xl63, width: 75"] [/TD]
    [TD="class: xl63, width: 40"] [/TD]
    [TD="class: xl63, width: 72"] [/TD]
    [TD="class: xl63, width: 81"] [/TD]
    [TD="class: xl63, width: 80"] [/TD]
    [TD="class: xl63, width: 86"] [/TD]

    [/tr]


    [/TABLE]

    First row under data (Row 9) will sum the amounts in the "Amount" column and the "Pay" column
    Second row under data (Row 10) will have an if formula based on data pulled from another table and entered into the "Qty" and "Price" column then formula to multiply those two numbers and include result in the "Amount" column
    Third row under data (Row 11) will subtract second row from first row
    Fourth row under data (Row 12) will sum all four figures in the "Amount" column and the result is then divided by the sum amount in the first formula row with result in the "Pay" column.


    Can this be done? Is there a way to build a vba/macro that would insert these formulas and labels at the end of every worksheet?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!