Need Help with VBA Code, Not sure if its possible.

[Giovanni03]

Hello Everyone!

I've been thinking of a way to improve a certain function that I currently use on on one my of workbooks. Its a copy a paste code that allows me to copy any amount of rows that i select over into another sheet. What I do now is highlight specific words in Column I then with those highlighted cells I'm able to identify what I need to move over onto another sheet. If you're thinking why don't you just had a set up where those words in column I are identified and copied is because they're only a part of a larger order. That's were I'm stuck...

Is it at all possible if cells are highlighted (or Bold) in Column I then it'll copy paste all of the rows based on the order # (Column D) into a new sheet? As well as delete all of the copied information from the first sheet that it copied it from (avoid duplicate data)

So basically, If cells in column I are highlighted then in column D the order number will be the same.

See below of an example of what I'm talking about,

Column D Column I

Order # 324512	​	Customer Name	SERVICE	9410101​	Product Description
Order # 324512	0:40:00​	Customer Name	SERVICE	5246600​	Product Description
Order # 324512	0:40:00​	Customer Name	DELIVERY	3762114​	Product Description (Highlighted Cell)
Order # 324512	0:40:00​	Customer Name	DELIVERY	5770014​	Product Description
Order # 324512	0:40:00​	Customer Name	DELIVERY	5446387​	Product Description
Order # 324512	0:40:00​	Customer Name	DELIVERY	8930357​	Product Description (Highlighted Cell)
Order # 324512	0:40:00​	Customer Name	DELIVERY	9282812​	Product Description
Order # 324512	0:40:00​	Customer Name	DELIVERY	6451044​	Product Description (Highlighted Cell)

 

[davesexcel]

So basically, If cells in column I are highlighted then in column D the order number will be the same.

Everything made sense until this line

Did you just want to copy the rows to another worksheet, what is the name of the other worksheet?

 

[Giovanni03]

Everything made sense until this line

Did you just want to copy the rows to another worksheet, what is the name of the other worksheet?

Lol yea I should of read that out loud before posting it.

I meant that if two rows are highlighted they can be part of a huge order all of which will have duplicated order numbers in column D(that pertain to other products or services). I was able to use advanced filtering to pull the data I wanted but it'll only pull the rows with the descriptions I listed not the entire order.

 

[Akuini]

@Giovanni03
So, basically, for example:
if I10 is highlighted and value in A10 is "Order # 324512" then find all the same order in col D and move the entire row to another sheet.
is that correct?

I suggest:
1. sort data by col D
2. use a helper column say col J, so instead of highlighting col I just put "1" in col J. Dealing with cell value is easier to code and faster than dealing with cell format.

If you can do that, I can help with the code.

 

[Giovanni03]

Sorry for the late reply, I've been swamped at work.

I can absolutely do that. I'm adding it now, anything that would be "highlighted" end up with a number 1 in column J.

 

[Akuini]

I can absolutely do that. I'm adding it now, anything that would be "highlighted" end up with a number 1 in column J.

In col D there must be various order number, could you post an example that includes 2 different order numbers? and also put several "1" in col J and explain what do you want to happen in more detailed.
Also can you post the example using XL2BB tool?

 

[Giovanni03]

In col D there must be various order number, could you post an example that includes 2 different order numbers? and also put several "1" in col J and explain what do you want to happen in more detailed.
Also can you post the example using XL2BB tool?

Sure thing, I listed an example below of what it looks like on my excel, i can post the Xl2BB atm since I'm still at work, i can do it once i get home later today.

Column D Column I Column J

Order# 30814	01:53​	Customer Name A	DELIVERY	6494861​	Product Description
Order# 30814	01:53​	Customer Name A	SERVICE	6419756​	Product Description
Order# 77688	01:53​	Customer Name B	DELIVERY	6493113​	Product Description	1
Order# 77688	01:53​	Customer Name B	SERVICE	6419756​	Product Description
Order# 55084	01:02​	Customer Name C	DELIVERY	6419621​	Product Description	1
Order# 55084	01:02​	Customer Name C	DELIVERY	6419627​	Product Description
Order# 55084	01:02​	Customer Name C	DELIVERY	6532174​	Product Description
Order# 55084	01:02​	Customer Name C	SERVICE	5246401​	Product Description
Order# 55084	01:02​	Customer Name C	SERVICE	5445800​	Product Description
Order# 55084	01:02​	Customer Name C	DELIVERY	5446387​	Product Description	1
Order# 55084	01:02​	Customer Name C	SERVICE	5246500​	Product Description
Order# 55084	01:02​	Customer Name C	DELIVERY	3762114​	Product Description

My goal is to copy and paste an entire order into a new sheet that is identified by column J having the number 1. I've used advanced filtering but that only pulls partial order # based on the product description (It only pulls that row). I'm looking to have the entire order move to a new sheet as well as deleted from the sheet that it was copied from.

 

[jolivanes]

If I understand things right, this might be another possibility.
Change references where required.

Sub Maybe()
Dim i As Long, rng As Range
    For i = 2 To Cells(Rows.Count, 10).End(xlUp).Row
        If Cells(i, 10).Value = 1 Then
            If rng Is Nothing Then Set rng = Cells(i, 4).Resize(, 6) Else Set rng = Union(rng, Cells(i, 4).Resize(, 6))
        End If
    Next i
rng.Copy Sheets("Sheet3").Cells(1, 1)
Set rng = Nothing
End Sub

 

[Giovanni03]

If I understand things right, this might be another possibility.
Change references where required.

Sub Maybe()
Dim i As Long, rng As Range
    For i = 2 To Cells(Rows.Count, 10).End(xlUp).Row
        If Cells(i, 10).Value = 1 Then
            If rng Is Nothing Then Set rng = Cells(i, 4).Resize(, 6) Else Set rng = Union(rng, Cells(i, 4).Resize(, 6))
        End If
    Next i
rng.Copy Sheets("Sheet3").Cells(1, 1)
Set rng = Nothing
End Sub

Thank you for trying, that pulled only the highlighted data and it didn't include the entire row, it pasted from column D.

Basically trying to have it so if any part of an order has a 1 in column J then paste all rows that include that order # into new sheet

 

[Akuini]

Try this:
Data start at row 2
Make sure "Sheet2" exist.

Sub Giovanni03()
Dim i As Long, h As Long, n As Long
Dim va, vb
Dim d As Object

n = Range("D" & Rows.Count).End(xlUp).Row
'Data start at row 2
va = Range("D2:D" & n)
vb = Range("J2:J" & n)

Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
    
    For i = 1 To UBound(vb, 1)
        If vb(i, 1) = 1 Then d(va(i, 1)) = Empty
    Next

    For i = 1 To UBound(va, 1)
        If d.exists(va(i, 1)) Then vb(i, 1) = 1
    Next

Range("K2").Resize(UBound(vb, 1), 1) = vb

Range("A1:K" & n).Sort Key1:=Columns(11), Order1:=xlAscending, Header:=xlYes

h = WorksheetFunction.Sum(Range("K1:K" & n))
'copy data to Sheet2
Range("A2").Resize(h, 9).Copy Sheets("Sheet2").Range("A1")


End Sub