Macro to consolidate data not working

Kim B

Board Regular
Joined
Jun 16, 2008
Messages
233
Office Version
  1. 365
Hello. A wonderful excel expert created this macro for me that is supposed to go out to 100 workbooks in the same directory and return certain columns into a master sheet.

When I try and run it, I am getting this error highlighted in organge and also it opens up the first workbook in the directory. I do not need the workbooks to be open. There are also multiple formulas and links to other workbooks in the sheets the macro is pulling from. I am not sure if this is messing something up. The result of what the macro is producing is also in this thread. It is on the right track with extracting data, but doesnt quite capture all the information

Thank you

Sub multicopy()
' get first file
fname = Dir("g:\accounting\private\msdacct\planning\2012\plan\budgets\reviewed\*.xl*")
Do Until fname = ""
Workbooks.Open (fname)
lastrow = ActiveWorkbook.Sheets(1).Range("B65536").End(xlUp).Row
targetrow = ThisWorkbook.Sheets(1).Range("B65536").End(xlUp).Offset(1).Row
ActiveWorkbook.Sheets(1).Range("B9:H" & lastrow).Copy _
Destination:=ThisWorkbook.Sheets(1).Range("B" & targetrow)
ThisWorkbook.Sheets(1).Range("A" & targetrow).Resize(targetrow - 9 + 1).Value = _
ActiveWorkbook.Sheets(1).Range("E6").Value
ActiveWorkbook.Close savechanges:=False
fname = Dir
Loop

End Sub

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Tahoma,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 74px"><COL style="WIDTH: 74px"><COL style="WIDTH: 74px"><COL style="WIDTH: 74px"><COL style="WIDTH: 74px"></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></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">33,750 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">33,750 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">33,750 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">33,750 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">135,000 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(190,000)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(190,000)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(190,000)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(190,000)</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">(760,000)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">245,000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">190,000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">190,000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">190,000 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">815,000 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">45,000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">45,000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">45,000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">45,000 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">180,000 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">6,500 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">6,500 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">6,500 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">6,500 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">26,000 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">16,250 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">16,250 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">16,250 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">16,250 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">65,000 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(22,500)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(22,500)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(22,500)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(22,500)</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">(90,000)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(1,514,000)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(1,417,000)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(1,296,000)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(1,449,000)</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">(5,676,000)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(762,000)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(701,000)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(624,000)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(656,000)</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">(2,743,000)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">93,750 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">93,750 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">93,750 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">93,750 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">375,000 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(4,000)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(4,000)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(4,000)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(4,000)</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">(16,000)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">102,500 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">102,500 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">102,500 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">102,500 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">410,000 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">26,500 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">26,500 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">26,500 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">26,500 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">106,000 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(9,750)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(9,750)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(9,750)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid">(9,750)</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">(39,000)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</TD><TD> </TD><TD></TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</TD><TD> </TD><TD>#N/A</TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</TD><TD> </TD><TD>#N/A</TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">31</TD><TD> </TD><TD>#N/A</TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">32</TD><TD> </TD><TD>#N/A</TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">33</TD><TD> </TD><TD>#N/A</TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">34</TD><TD> </TD><TD>#N/A</TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">35</TD><TD> </TD><TD>#N/A</TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">36</TD><TD> </TD><TD>#N/A</TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">37</TD><TD> </TD><TD>#N/A</TD><TD style="BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">38</TD><TD> </TD><TD style="BORDER-BOTTOM: #000000 1px solid">#N/A</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffff99; BORDER-RIGHT: #969696 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">39</TD><TD> </TD><TD style="TEXT-ALIGN: right">512001</TD><TD style="BORDER-RIGHT: #000000 1px solid">DIRECT - SOCIAL SECURITY</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">205 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">205 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">40</TD><TD> </TD><TD style="TEXT-ALIGN: right">512002</TD><TD style="BORDER-RIGHT: #000000 1px solid">DIRECT - UNEMPLOYMENT INSURANC</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">41</TD><TD> </TD><TD style="TEXT-ALIGN: right">512005</TD><TD style="BORDER-RIGHT: #000000 1px solid">DIRECT - MEDICAL</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">42</TD><TD> </TD><TD style="TEXT-ALIGN: right">512006</TD><TD style="BORDER-RIGHT: #000000 1px solid">DIRECT - LIFE INSURANCE</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">43</TD><TD> </TD><TD style="TEXT-ALIGN: right">512101</TD><TD style="BORDER-RIGHT: #000000 1px solid">INDIRECT - SOCIAL SECURITY</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">16 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">16 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">44</TD><TD> </TD><TD style="TEXT-ALIGN: right">512102</TD><TD style="BORDER-RIGHT: #000000 1px solid">INDIRECT - UNEMPLOYMENT INSUR</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">45</TD><TD> </TD><TD style="TEXT-ALIGN: right">512105</TD><TD style="BORDER-RIGHT: #000000 1px solid">INDIRECT - MEDICAL</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">46</TD><TD> </TD><TD style="TEXT-ALIGN: right">512106</TD><TD style="BORDER-RIGHT: #000000 1px solid">INDIRECT - LIFE INSURANCE</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">0 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">47</TD><TD> </TD><TD style="TEXT-ALIGN: right">512201</TD><TD style="BORDER-RIGHT: #000000 1px solid">DIRECT - 401K</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">190 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">190 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">48</TD><TD> </TD><TD style="TEXT-ALIGN: right">512301</TD><TD style="BORDER-RIGHT: #000000 1px solid">INDIRECT - 401K</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">15 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #969696 1px solid">0 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">15 </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
see this statement
Code:
ThisWorkbook.Sheets(1).Range("A" & targetrow).Resize(targetrow - 9 + 1).Value = _
now
Code:
ThisWorkbook.Sheets(1).Range("A" & targetrow).Resize(targetrow - 9 + 1)
gives a range of cells viz.
$A$49:$A$89
which is a number of cells and what can be their value
if you cut off
.value
I think it will work

But do not know what do you want to do.

note
there can be value for one cell but there cannot be a value for a range of number of cells. even in that case for one cell e.g. A1 if you want to find the value of A1
Code:
msgbox range("A1")
will give the value. you need not add .value.
because value is the default of statemnt
Code:
msgbox range("A1")
do some experiments in a fresh sheet using ".value" or not using it.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,578
Messages
6,173,167
Members
452,504
Latest member
frankkeith2233

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