Macro to consolidate data not working

K

Kim B

Board Regular
Joined
Jun 16, 2008
Messages
221
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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
315
VBAEXCELNew
V
E
  • Question
Adding a custom message.
Replies
1
Views
429
Micron
M
P
  • Solved
Compile error: method or data member not found
Replies
6
Views
244
Patriot2879
P
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
829
gvsumich
G
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
349
esben
E

Forum statistics

Threads
1,214,923
Messages
6,122,283
Members
449,075
Latest member
staticfluids

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