Excel VBA Stuff - Teh Forum

<body> <div id="header"> <span class="left"> <span class="back"><a href="lthread_list.php?webtag=DEFAULT"><span class="image mobile_back" title="Back"></span></a></span> <span class="image mobile_logo" title="Beehive Forum Logo"></span> </span> <ul> <li> <a class="reply_all" href="lpost.php?webtag=DEFAULT&reply_to=30625.0&return_msg=30625.1"> <span class="image mobile_reply_all"></span><span class="text">Reply to All</span> </a> </li> <li> <a class="navigation" href="#"> <span class="image mobile_navigation"></span><span class="text">Show messages</span> </a> </li> <li> <a class="main" href="#"><span class="image mobile_menu" title="Menu"></span> </a> </li> </ul> </div> <div class="menu main"> <ul> <li><a href="lthread_list.php?webtag=DEFAULT">Messages</a></li> <li><a href="lpm.php?webtag=DEFAULT">Inbox</a></li> <li><a href="lsearch.php?webtag=DEFAULT">Search</a></li> <li><a href="llogon.php?webtag=DEFAULT">Login</a></li> </ul> </div> <div class="menu navigation"><a href="lmessages.php?webtag=DEFAULT&msg=30625.1">1–9</a></div> <div id="page_content"> <h3 class="thread_title"><a href="index.php?webtag=DEFAULT&amp;msg=30625.1">Excel VBA Stuff</a> </h3> <div id="messages" data-navigation="30625_1_9_10"> <a name="a30625_1"></a><div class="message" id="message_30625_1"> <div class="message_header"> <div class="message_from"> From: Extrobe (ALDREDD)<span class="message_time">14 Sep 2006 12:50</span> <div class="clearer"></div> </div><div class="message_to">To: ALL<span class="message_count">1 of 9</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=30625.1">30625.1</a></div> <div class="message_body"> I have me some code which inserts a duplication of the selected row, above.<br /> Question 1 - <br /> Using the code below, how do I get it to insert the new row below the selcted row, rather than above?<br /> <br /> <div class="quotetext" id="code-VBA"><b>VBA code:</b></div><pre class="code"> Private Sub CommandButton1_Click() Worksheets("Sheet1").Unprotect Password:="#######" Dim currentRow As Integer Dim currentCol As Integer Dim tpnbNew As Integer 'tpnbNew = InputBox("Please enter your new TPNB") ' where is the cursor currentRow = ActiveCell.Row currentCol = ActiveCell.Column ' insert now row above current row Rows(currentRow & ":" & currentRow).Select Selection.Insert Shift:=xlDown ' select old row (since currentRow was shifted down one this is now currentRow+1, select to end and copy Range("A" & currentRow + 1, "IV" & currentRow + 1).Select Selection.Copy ' select first cell in dest row and paste Range("A" & currentRow).Select ActiveSheet.Paste ' restore cursor to correct column in new row Cells(currentRow, currentCol).Select Worksheets("Sheet1").Protect Password:="######" End Sub</pre><br /> Question 2, how would I tell it to empty selected cells within the new row? The cell column will always be the same, but from the row, there are about 6 cells which need to be copied 'blank'.<br /> <br /> Question 3 - is there a way of populating these newly-emptied cells with values from a user form / or pop-up box (you'll see in the code I've created 1 popup box, and have also created a blank form in vba to use)<br /> <br /> thanks very much!</div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=30625.1&return_msg=30625.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="30625.1"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a30625_2"></a><div class="message" id="message_30625_2"> <div class="message_header"> <div class="message_from"> From: Peter (BOUGHTONP)<span class="message_time">14 Sep 2006 13:26</span> <div class="clearer"></div> </div><div class="message_to">To: Extrobe (ALDREDD) <span><span class="image post_read" title="Read: 14 Sep 2006 13:30"></span></span> <span class="message_count">2 of 9</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=30625.2">30625.2</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=30625.1#a30625_1" target="_self">30625.1</a></div> <div class="message_body"> 1) Why can't you do <div class="quotetext" id="code-"><b> code:</b></div><pre class="code">Rows(currentRow+1 & ":" & currentRow+1).Select</pre> ? </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=30625.2&return_msg=30625.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="30625.2"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a30625_3"></a><div class="message" id="message_30625_3"> <div class="message_header"> <div class="message_from"> From: Extrobe (ALDREDD)<span class="message_time">14 Sep 2006 13:33</span> <div class="clearer"></div> </div><div class="message_to">To: Peter (BOUGHTONP) <span><span class="image post_read" title="Read: 14 Sep 2006 13:36"></span></span> <span class="message_count">3 of 9</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=30625.3">30625.3</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=30625.1#a30625_2" target="_self">30625.2</a></div> <div class="message_body"> <p>that does make sence <span class="emoticon e_unsure" title=":/"><span class="e__">:/</span></span> </p> <p> </p> <p>I've also managed to get it to only copy the cells which have a formula in it, and leave the other cells blank.</p> <p> </p> <p>Just need to figure how to get the value of a prompt into the certain cell, but could be tricky, as the code now asks how many news rows a want to insert! <span class="emoticon e_rabbit" title=":B"><span class="e__">:B</span></span></p></div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=30625.3&return_msg=30625.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="30625.3"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a30625_4"></a><div class="message" id="message_30625_4"> <div class="message_header"> <div class="message_from"> From: Extrobe (ALDREDD)<span class="message_time">14 Sep 2006 13:36</span> <div class="clearer"></div> </div><div class="message_to">To: Peter (BOUGHTONP) <span><span class="image post_read" title="Read: 14 Sep 2006 13:36"></span></span> <span class="message_count">4 of 9</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=30625.4">30625.4</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=30625.1#a30625_2" target="_self">30625.2</a></div> <div class="message_body"> <p>do you know haw to make the worksheet not refresh (or prompt to refresh) external links on startup?</p> <p> </p> <p>The sheet only needs refreshing once a week, and only by me, and dont want others being prompted to do so!</p> <p> </p> <p>thanks</p></div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=30625.4&return_msg=30625.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="30625.4"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a30625_5"></a><div class="message" id="message_30625_5"> <div class="message_header"> <div class="message_from"> From: Peter (BOUGHTONP)<span class="message_time">14 Sep 2006 13:39</span> <div class="clearer"></div> </div><div class="message_to">To: Extrobe (ALDREDD) <span><span class="image post_read" title="Read: 14 Sep 2006 13:50"></span></span> <span class="message_count">5 of 9</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=30625.5">30625.5</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=30625.1#a30625_4" target="_self">30625.4</a></div> <div class="message_body"> I'm afraid I was just making a semi-educated guess - my knowledge of Excel VBA is miniscule. <span class="emoticon e_sad" title=":("><span class="e__">:(</span></span> </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=30625.5&return_msg=30625.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="30625.5"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><div class="message"> Message 30625.6 was deleted</div> <div class="message"> Message 30625.7 was deleted</div> <a name="a30625_8"></a><div class="message" id="message_30625_8"> <div class="message_header"> <div class="message_from"> From: Extrobe (ALDREDD)<span class="message_time">18 Sep 2006 16:55</span> <div class="clearer"></div> </div><div class="message_to">To: Mr (M00RL0CK) <span><span class="image post_read" title="Read: 18 Sep 2006 16:56"></span></span> <span class="message_count">8 of 9</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=30625.8">30625.8</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=30625.1#a30625_7" target="_self">30625.7</a></div> <div class="message_body"> Another one for you, if I may? I've got a spreadsheet which pulls data from another workbook. What my boss wants is a few line below the report, to see an extract of the same report, but just 5 lines of it, being the top 5 results of a sort. I know that makes no sense at all, so I've attached an example of how it might look (I've pasted values on it). At the top is the main report - could be anywhere from 20 - 2000 lines - and mornally about 65 columns. this will always be sorted by Column L in this example. Below all of this are two extracts, one showing the highest cover lines, the other showing the lowest. This report is updated weekly, and the extract needs to reflect this. Can you think of any way of doing this? I've tried creating a Macro to do it, which mmics me copying into a new sheet, sorting & copying back, but this just doesn't give me the flexability I need. Is this even possable? Thanks!</div> <div class="message_attachments"> <span>Attachments:</span> <ul> <li><span class="image attach"></span><a href="get_attachment.php?webtag=DEFAULT&hash=bb7f3c437822c58d9b9bf369105f7def&filename=test.xls" target="_blank">test.xls</a></li> </ul> </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=30625.8&return_msg=30625.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="30625.8"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><div class="message"> Message 30625.9 was deleted</div> </div> </div> </body>