VBA to paste values over formula and then lock range

S

stephenedger

New Member
Joined
Jul 4, 2014
Messages
20
Hi,

I am building a Weekly Sales Tracker, and am using vlookups for each week to pull the data through from an overall weekly sales report (both in Excel 2013). The data in the sales report is never in the same order, so I need the lookups to quickly locate the data when a new report is produced (otherwise I'll spend an hour hunting and pasting the data I need). My problem is: once the lookup has worked, I want to copy and paste the values over the original formula as the sales report will not be kept once I have the values.

So I need the code to do the following:
  • Find the next unprotected column with a formula,
  • Select the data in that column (e.g. E9:E32) and copy and paste values so the formula is removed but the data kept.
  • Protect the range that has just been pasted.
  • Find and select the next unprotected cell (e.g. F9)


Is this even possible. I know a little about VBA but am rubbish with understanding dynamic ranges. I know this seems like a lot of effort for something that could feasibly be performed manually, but it'll make me look great for my boss, so any help is gratefully received :)
7</SPAN>C</SPAN>D</SPAN>E</SPAN>F</SPAN>G</SPAN>H</SPAN>I</SPAN>J</SPAN>K</SPAN>L</SPAN>M</SPAN>N</SPAN>O</SPAN>P</SPAN>Q</SPAN>R</SPAN>
8</SPAN>Name</SPAN>Area</SPAN>05-Jan</SPAN>12-Jan</SPAN>19-Jan</SPAN>26-Jan</SPAN>02-Feb</SPAN>09-Feb</SPAN>16-Feb</SPAN>23-Feb</SPAN>02-Mar</SPAN>09-Mar</SPAN>16-Mar</SPAN>23-Mar</SPAN>30-Mar</SPAN>06-Apr</SPAN>
9</SPAN>Aaron</SPAN>UK</SPAN>30</SPAN>31</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
10</SPAN>Aaron</SPAN>Overseas</SPAN>71</SPAN>71</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
11</SPAN>Billy</SPAN>UK</SPAN>24</SPAN>24</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
12</SPAN>Billy</SPAN>Overseas</SPAN>55</SPAN>55</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
13</SPAN>Carl</SPAN>UK</SPAN>46</SPAN>46</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
14</SPAN>Carl</SPAN>Overseas</SPAN>76</SPAN>76</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
15</SPAN>Danny</SPAN>UK</SPAN>71</SPAN>71</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
16</SPAN>Danny</SPAN>Overseas</SPAN>32</SPAN>32</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
17</SPAN>Eric</SPAN>UK</SPAN>22</SPAN>22</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
18</SPAN>Eric</SPAN>Overseas</SPAN>26</SPAN>26</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
19</SPAN>Frank</SPAN>UK</SPAN>101</SPAN>101</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
20</SPAN>Frank</SPAN>Overseas</SPAN>39</SPAN>39</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
21</SPAN>George</SPAN>UK</SPAN>10</SPAN>10</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
22</SPAN>George</SPAN>Overseas</SPAN>11</SPAN>11</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
23</SPAN>Harry</SPAN>UK</SPAN>3</SPAN>3</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
24</SPAN>Harry</SPAN>Overseas</SPAN>1</SPAN>1</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
25</SPAN>Ivan</SPAN>UK</SPAN>46</SPAN>46</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
26</SPAN>Ivan</SPAN>Overseas</SPAN>19</SPAN>19</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
27</SPAN>James</SPAN>UK</SPAN>29</SPAN>29</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
28</SPAN>James</SPAN>Overseas</SPAN>136</SPAN>136</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
29</SPAN>Kirk</SPAN>UK</SPAN>29</SPAN>29</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
30</SPAN>Kirk</SPAN>Overseas</SPAN>29</SPAN>29</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
31</SPAN>Larry</SPAN>UK</SPAN>71</SPAN>71</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>
32</SPAN>Larry</SPAN>Overseas</SPAN>30</SPAN>30</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>#N/A</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL span=4><COL span=4><COL span=5><COL></COLGROUP>
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
You must log in or register to reply here.

Similar threads

W
  • Question
Personnel Roster using Macro's
Replies
0
Views
180
womeclr
W
P
  • Solved
Update Equally Distributed Formula
2
Replies
19
Views
600
felixstraube
F
J
  • Solved
variable vba per ws
Replies
5
Views
335
kevin9999
K
willow1985
  • Solved
Formula for Last X number of rows if status "closed"
Replies
3
Views
344
Fluff
Fluff
willow1985
  • Solved
Count the most recent x number of dates that meet certain criteria
Replies
3
Views
279
Fluff
Fluff

Forum statistics

Threads
1,215,077
Messages
6,122,995
Members
449,094
Latest member
masterms

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