<div>
You can add a header line and do filtering, like I show in the attached version of your workbook<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/08_w33/1111014/GroupingBasedOnAnotherCol.xlsx" target="_blank" class="file-inline" title="GroupingBasedOnAnotherCol.xlsx (8 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>GroupingBasedOnAnotherCol.xlsx</a>
</div>


GroupingBasedOnAnotherCol.xlsx

<div>
I don't believe the question mentions filtering in fact the object to this is not to filter. &nbsp; Did not see in the attached named ranges of &nbsp;Qty or CatCode? That's why I have named ranges in the question title. &nbsp;Maybe you misunderstood the question or I am not following your reply? &nbsp;The goal is, from the active cell, name the two ranges, and leave the active cell as it is. &nbsp;Unless the macro filters than names then unfilters? &nbsp;That is why I am even wondering if this can be done.
</div>


<div>
As per your description, I assumed that your ultimate goal is to paste the Qtys and CatCodes of the selected group onto the Sheet2 and to do that creating named ranges is not required. I am not sure why do you want to do that?<br />
<br />
BTW try the following tweaked code, this will create two named ranges as per the selected group in col. A.<br />
<br />
Is this what you are trying to achieve?<br />

<pre><code class="code-10 nolinks" id="code-20-41751544-1"><span>Sub CopyPasteAsPerSelectionInColumnA()</span>
<span>Dim sws As Worksheet, dws As Worksheet</span>
<span>Dim sRow As Long, eRow As Long</span>
<span></span>
<span>Application.ScreenUpdating = False</span>
<span>Set sws = Sheets(&quot;Sheet1&quot;)</span>
<span>Set dws = Sheets(&quot;Sheet2&quot;)</span>
<span></span>
<span>If Selection.Parent.Name &lt;&gt; sws.Name Then</span>
<span>    MsgBox &quot;Please select the cell on &quot; &amp; sws.Name &amp; &quot; and then try again...&quot;, vbExclamation, &quot;Wrong Sheet Selected!&quot;</span>
<span>    Exit Sub</span>
<span>End If</span>
<span>If Selection.Column &lt;&gt; 1 Then</span>
<span>    MsgBox &quot;Please select a cell in column A and then try again...&quot;, vbExclamation, &quot;Wrong Selection!&quot;</span>
<span>    Exit Sub</span>
<span>End If</span>
<span></span>
<span>sRow = sws.Range(&quot;A:A&quot;).Find(what:=ActiveCell.Value).Row</span>
<span>eRow = sws.Range(&quot;A:A&quot;).Find(what:=ActiveCell.Value, searchdirection:=xlPrevious).Row</span>
<span>dws.Cells.Clear</span>
<span>sws.Range(&quot;B&quot; &amp; sRow &amp; &quot;:B&quot; &amp; eRow).Name = &quot;Qtys&quot;</span>
<span>sws.Range(&quot;C&quot; &amp; sRow &amp; &quot;:C&quot; &amp; eRow).Name = &quot;CatCode&quot;</span>
<span>sws.Range(&quot;B&quot; &amp; sRow &amp; &quot;:C&quot; &amp; eRow).Copy dws.Range(&quot;A1&quot;)</span>
<span>dws.Columns.AutoFit</span>
<span>dws.Activate</span>
<span>Application.ScreenUpdating = True</span>
<span>End Sub</span>
</code></pre>
</div>


<div>
Neeraj<br />
This is looking very promising. &nbsp;Having issues with: Ln20 &nbsp;dws.Cells.Clear<br />
<br />
Can we delete all rows under paste? &nbsp;so if it puts in 6 rows of data, delete all rows under that last one. &nbsp;I chg'd the paste destination to &nbsp;G2 but that should not do much. &nbsp;Is that possible to have a clean paste into the new sheet?
</div>


<div>
Line#20 clears the sheet2 before new data is pasted onto it.<br />
Do you mean pasting onto a new sheet each time the code is executed?<br />
<br />
I am having a hard time to understand your description. Can you upload a sample workbook mocking up the desired output mentioning the each step you want to automate?
</div>


<div>
I am going to try a different clear prior to pasting them in.. that may solve the issue. &nbsp;Need to do a little more testing.. &nbsp;Will let you know later today.
</div>


<div>
There is another method, but it also requires the columns to have headers. &nbsp;Shall I post that solution?
</div>


<div>
thanks aikimark.. but I believe the one posted is going to work still testing it.. but all is good so far
</div>


<div>
Excellent!!! &nbsp;Thanks for the help.
</div>


<div>
You're welcome. Thanks for the feedback.
</div>


<div>
<div class="content wysiwyg-content">
The attached sheet has a key in column A, it defines how many are in a range. &nbsp;Using ActiveCell. &nbsp;(assuming the active cell is the first record in a bunch, and I click on it.) &nbsp;So I would like to define a range in column B and column C. &nbsp;So if I place my cursor in A2, it would identify B2 thru B7 and name it &quot;Qtys&quot; &nbsp;and C2 thru C7 as &quot;CatCode&quot; <br />
<br />
I would then like to goto sheet2 and be able it paste in &nbsp;Qtys and Catcode.<br />
<br />
Is there a way to just grab one row, 4 rows or 6 rows as ranges to use when pasting it into another sheet?<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/08_w33/1111005/GroupingBasedOnAnotherCol.xlsx" target="_blank" class="file-inline" title="test1 (9 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>GroupingBasedOnAnotherCol.xlsx</a>
</div>
</div>


The attached sheet has a key in column A, it defines how many are in a range.  Using ActiveCell.  (assuming the active cell is the first record in a bunch, and I click on it.)  So I would like to define a range in column B and column C.  So if I place my cursor in A2, it would identify B2 thru B7 and name it "Qtys"  and C2 thru C7 as "CatCode" 

I would then like to goto sheet2 and be able it paste in  Qtys and Catcode.

Is there a way to just grab one row, 4 rows or 6 rows as ranges to use when pasting it into another sheet?

Naming a range based on uniqueness of another column

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.