Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.
Naming a range based on uniqueness of another column
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?
GroupingBasedOnAnotherCol.xlsx
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?
GroupingBasedOnAnotherCol.xlsx
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can add a header line and do filtering, like I show in the attached version of your workbook
GroupingBasedOnAnotherCol.xlsx
GroupingBasedOnAnotherCol.xlsx
Please try this....
Sub CopyPasteAsPerSelectionInColumnA()
Dim sws As Worksheet, dws As Worksheet
Dim sRow As Long, eRow As Long
Application.ScreenUpdating = False
Set sws = Sheets("Sheet1")
Set dws = Sheets("Sheet2")
If Selection.Parent.Name <> sws.Name Then
MsgBox "Please select the cell on " & sws.Name & " and then try again...", vbExclamation, "Wrong Sheet Selected!"
Exit Sub
End If
If Selection.Column <> 1 Then
MsgBox "Please select a cell in column A and then try again...", vbExclamation, "Wrong Selection!"
Exit Sub
End If
sRow = sws.Range("A:A").Find(what:=ActiveCell.Value).Row
eRow = sws.Range("A:A").Find(what:=ActiveCell.Value, searchdirection:=xlPrevious).Row
dws.Cells.Clear
sws.Range("B" & sRow & ":C" & eRow).Copy dws.Range("A1")
dws.Columns.AutoFit
dws.Activate
Application.ScreenUpdating = True
End Sub
I don't believe the question mentions filtering in fact the object to this is not to filter. Did not see in the attached named ranges of Qty or CatCode? That's why I have named ranges in the question title. Maybe you misunderstood the question or I am not following your reply? The goal is, from the active cell, name the two ranges, and leave the active cell as it is. Unless the macro filters than names then unfilters? That is why I am even wondering if this can be done.
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?
BTW try the following tweaked code, this will create two named ranges as per the selected group in col. A.
Is this what you are trying to achieve?
BTW try the following tweaked code, this will create two named ranges as per the selected group in col. A.
Is this what you are trying to achieve?
Sub CopyPasteAsPerSelectionInColumnA()
Dim sws As Worksheet, dws As Worksheet
Dim sRow As Long, eRow As Long
Application.ScreenUpdating = False
Set sws = Sheets("Sheet1")
Set dws = Sheets("Sheet2")
If Selection.Parent.Name <> sws.Name Then
MsgBox "Please select the cell on " & sws.Name & " and then try again...", vbExclamation, "Wrong Sheet Selected!"
Exit Sub
End If
If Selection.Column <> 1 Then
MsgBox "Please select a cell in column A and then try again...", vbExclamation, "Wrong Selection!"
Exit Sub
End If
sRow = sws.Range("A:A").Find(what:=ActiveCell.Value).Row
eRow = sws.Range("A:A").Find(what:=ActiveCell.Value, searchdirection:=xlPrevious).Row
dws.Cells.Clear
sws.Range("B" & sRow & ":B" & eRow).Name = "Qtys"
sws.Range("C" & sRow & ":C" & eRow).Name = "CatCode"
sws.Range("B" & sRow & ":C" & eRow).Copy dws.Range("A1")
dws.Columns.AutoFit
dws.Activate
Application.ScreenUpdating = True
End Sub
Neeraj
This is looking very promising. Having issues with: Ln20 dws.Cells.Clear
Can we delete all rows under paste? so if it puts in 6 rows of data, delete all rows under that last one. I chg'd the paste destination to G2 but that should not do much. Is that possible to have a clean paste into the new sheet?
This is looking very promising. Having issues with: Ln20 dws.Cells.Clear
Can we delete all rows under paste? so if it puts in 6 rows of data, delete all rows under that last one. I chg'd the paste destination to G2 but that should not do much. Is that possible to have a clean paste into the new sheet?
Line#20 clears the sheet2 before new data is pasted onto it.
Do you mean pasting onto a new sheet each time the code is executed?
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?
Do you mean pasting onto a new sheet each time the code is executed?
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?
I am going to try a different clear prior to pasting them in.. that may solve the issue. Need to do a little more testing.. Will let you know later today.
There is another method, but it also requires the columns to have headers. Shall I post that solution?
Premium Content
You need an Expert Office subscription to comment.Start Free Trial