Copy and Paste based on Cell Values

BwBisel

New Member
Joined
Oct 18, 2011
Messages
2
I tried to assemble this myself through lurking on the forums, but it seems like its just outside my skill level right now.

I have a download that I get that shows activity for the company by Accounting Unit. With Sub Accts, Desc, Amt, and Date. I would like to try and develop a macro that copies the rows based on the Value in AcctUnit Column to the appropriate tab (the first 3 numbers in Acct Unit to match its tab) There will be times where there are multiple entries for one acctunit (rows 5&6 as example) An added bonus would be if i could get the Amount to paste in the correct columns on the corresponding tabs. Since this will be a monthly process where items do not always clear i don’t want this to paste over data that exists in the individual tabs.

I have attached a small portion of what i am working with for a better Visualization. I really appreciate any help available.

Thanks,
Brett

Data Dump

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=0 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 75px"><COL style="WIDTH: 100px"><COL style="WIDTH: 84px"><COL style="WIDTH: 103px"><COL style="WIDTH: 87px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">ACCTUNIT</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">SUB-ACCOUNT</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">DESCRIPTION</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">BASE-AMOUNT</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">EFFECT-DATE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">3000000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD style="TEXT-ALIGN: right">-17887.47</TD><TD style="TEXT-ALIGN: right">9/1/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">3010000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD style="TEXT-ALIGN: right">-26297.63</TD><TD style="TEXT-ALIGN: right">9/1/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">3020000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD style="TEXT-ALIGN: right">54256.3</TD><TD style="TEXT-ALIGN: right">9/1/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">3030000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD style="TEXT-ALIGN: right">-25122.63</TD><TD style="TEXT-ALIGN: right">9/1/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">3030000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD style="TEXT-ALIGN: right">-25121.63</TD><TD style="TEXT-ALIGN: right">9/1/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">3040000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD style="TEXT-ALIGN: right">-66298.9</TD><TD style="TEXT-ALIGN: right">9/1/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">5010000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD style="TEXT-ALIGN: right">-8626.09</TD><TD style="TEXT-ALIGN: right">9/1/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">5020000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD style="TEXT-ALIGN: right">-25512.75</TD><TD style="TEXT-ALIGN: right">9/1/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">5030000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD style="TEXT-ALIGN: right">-18402.58</TD><TD style="TEXT-ALIGN: right">9/1/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">5040000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD style="TEXT-ALIGN: right">-21291.12</TD><TD style="TEXT-ALIGN: right">9/1/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right">5050000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD style="TEXT-ALIGN: right">-6404.14</TD><TD style="TEXT-ALIGN: right">9/1/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right">5060000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD style="TEXT-ALIGN: right">-12181.17</TD><TD style="TEXT-ALIGN: right">9/1/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: right">5070000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD style="TEXT-ALIGN: right">-24179.61</TD><TD style="TEXT-ALIGN: right">9/1/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: right">5080000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD style="TEXT-ALIGN: right">-17513.79</TD><TD style="TEXT-ALIGN: right">9/1/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: right">5090000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD style="TEXT-ALIGN: right">-26401.54</TD><TD style="TEXT-ALIGN: right">9/1/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: right">5100000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD style="TEXT-ALIGN: right">-25734.95</TD><TD style="TEXT-ALIGN: right">9/1/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: right">5130000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD style="TEXT-ALIGN: right">-13069.94</TD><TD style="TEXT-ALIGN: right">9/1/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: right">5140000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD style="TEXT-ALIGN: right">-12847.75</TD><TD style="TEXT-ALIGN: right">9/1/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: right">5150000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD style="TEXT-ALIGN: right">-398.88</TD><TD style="TEXT-ALIGN: right">9/1/2011</TD></TR></TBODY></TABLE>

301

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=0 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 83px"><COL style="WIDTH: 35px"><COL style="WIDTH: 184px"><COL style="WIDTH: 78px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 76px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 65px"><COL style="WIDTH: 71px"><COL style="WIDTH: 71px"><COL style="WIDTH: 71px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial; COLOR: #808000; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Health Care Facility</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial; COLOR: #808000; FONT-SIZE: 10pt; FONT-WEIGHT: bold">301</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">ST Due From Related Party</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">3010000-143100-0000</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">September 30, 2011</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt">Trial Balance </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial; FONT-SIZE: 10pt">Variance</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold"> - </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">(26,297.63)</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">YTD Balance</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: Arial; COLOR: #c0c0c0; FONT-SIZE: 10pt">Account-Unit</TD><TD style="FONT-FAMILY: Arial; COLOR: #c0c0c0; FONT-SIZE: 10pt">SubAcct</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">DESCRIPTION</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">12/31/2010</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">1/31/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">2/28/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">3/31/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">4/30/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">5/31/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">6/30/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">7/31/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">8/31/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">9/30/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">10/31/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">11/30/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">12/31/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">3010000</TD><TD style="TEXT-ALIGN: right">824</TD><TD>Audit Fees</TD><TD> </TD><TD> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: right"> (26,298)</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">(26,298)</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">0</TD></TR></TBODY></TABLE>
 

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.
I have trunacated the sample data as follows

Excel Workbook
ABCDE
1ACCTUNITSUB-ACCOUNTDESCRIPTIONBASE-AMOUNTEFFECT-DATE
23000000824Audit Fees-17887.59/1/2011
33010000824Audit Fees-26297.69/1/2011
43020000824Audit Fees54256.39/1/2011
53030000824Audit Fees-25122.69/1/2011
63030000824Audit Fees-25121.69/1/2011
data dump



already insert four workh sheets named 300,301,302 303
with row no. 6 from A`1 as follows
Excel Workbook
ABCDEFGHIJKLMNOPQ
6Account-UnitSubAcctDESCRIPTION12/31/20101/31/20112/28/20113/31/20114/30/20115/31/20116/30/20117/31/20118/31/20119/30/201110/31/201111/30/201112/31/2011YTDbalance22011
300


now try this macro test and see whether you get what you want. You can modify the macro to suit you

Code:
Sub test()
Dim r As Range, unit As Range, cunit, rfull As Range, mmonth As Long
Dim cfind As Range, balance As Double, r1 As Range, c1 As Range, k As Long
With Worksheets("data dump")
Set r = Range(.Range("A1"), .Range("A1").End(xlDown))
Set unit = .Range("A1").End(xlDown).Offset(5, 0)
r.AdvancedFilter xlFilterCopy, , unit, True
Set unit = Range(unit.Offset(1, 0), unit.End(xlDown))
For Each cunit In unit

Set rfull = .Range("a1").CurrentRegion
rfull.AutoFilter field:=1, Criteria1:=cunit.Value
rfull.Offset(1, 0).Resize(rfull.Rows.Count - 1, rfull.Columns.Count) _
    .SpecialCells(xlCellTypeVisible).Copy
    
With Worksheets(Left(cunit.Value, 3))
.Range("a7").PasteSpecial
k = .Range("a6").End(xlToRight).Column
Set r1 = Range(.Range("A7"), .Cells(Rows.Count, "A").End(xlUp))
For Each c1 In r1
  mmonth = Month(c1.End(xlToRight))
  Set cfind = .Rows("6:6").Find(what:=mmonth, lookat:=xlPart)
  c1.End(xlToRight).Clear
  c1.End(xlToRight).Cut Cells(c1.Row, cfind.Column)
  balance = WorksheetFunction.Sum(Cells(c1.Row, "d"), .Cells(c1.Row, Columns.Count).End(xlToLeft))
  
  .Cells(c1.Row, k) = balance
  Next c1
End With
rfull.AutoFilter
Next cunit
End With
End Sub

Code:
Sub undo()
Dim j As Long
For j = 1 To Worksheets.Count
If Worksheets(j).Name <> "data dump" Then
With Worksheets(j)
Range(.Range("a7"), .Cells(Rows.Count, "A")).EntireRow.Delete
End With
End If
Next j
End Sub
<table border="0" cellpadding="0" cellspacing="0" width="1177"><col span="3" width="64"><col width="105"><col span="9" width="64"><col width="72"><col width="76"><col width="84"><col width="72"><tr height="51"> <td style="height:38.25pt;width:48pt" height="51" width="64">
</td><td style="width:48pt" width="64">
</td><td style="width:48pt" width="64">
</td><td class="xl22" style="width:79pt" align="right" width="105">
</td><td class="xl22" style="width:48pt" align="right" width="64">
</td><td class="xl22" style="width:48pt" align="right" width="64">
</td><td class="xl22" style="width:48pt" align="right" width="64">
</td><td class="xl22" style="width:48pt" align="right" width="64">
</td><td class="xl22" style="width:48pt" align="right" width="64">
</td><td class="xl22" style="width:48pt" align="right" width="64">
</td><td class="xl22" style="width:48pt" align="right" width="64">
</td><td class="xl22" style="width:48pt" align="right" width="64">
</td><td class="xl22" style="width:48pt" align="right" width="64">
</td><td class="xl22" style="width:54pt" align="right" width="72">
</td><td class="xl22" style="width:57pt" align="right" width="76">
</td><td class="xl22" style="width:63pt" align="right" width="84">
</td><td class="xl23" style="width:54pt" width="72">
</td></tr></table>
 
Upvote 0
I really appreciate it.

I am going to play around with this and I will let you know how it turns out.

Thanks again.

Brett
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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