Thread: A better way than copy - paste ?

Hello

I have a bit of codestring containing a copy and paste function, but I would love not to have to use copy - paste function.

The Goal (cooked a bit down):
I have two columns (part of a larger worksheet) one with material numbers and one with description (name) of the material.

For example like this:

Material # Material name
45 Text1
23 Text2
65 Text3
89 Text4
45 Text1
23 Text2
65 Text3
89 Text4
45 Text1
23 Text2
65 Text3
89 Text4
- -
And it continous down

I want to identify when the same materialnumber as the first one, appears in the columns again. And then copy the material numbers and descriptions from the first time the materialnumber appears until the second time it appears.

So that i in another Sheet ie. Sheet2 gets the result:

45 Text1
23 Text2
65 Text3
89 Text4

I have coded the following:

Sub Group_Rawmaterials()
    
Dim RowVar1 As Long
Dim Value1 As Long
    
    Sheets("Sheet1").Select
    Value1 = Range("F2")
    
    RowVar1 = Cells.Find(What:=Value1, After:=[F2], SearchOrder:=xlByColumns, SearchDirection:=xlNext).Row
    
    RowVar1 = RowVar1 - 1
        
    Range("F2", "G" & RowVar1).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A2").Select
    ActiveSheet.Paste
End Sub

The question now is, can this be done without the copy paste function?

I thank you in advance.

/Freakazoid

Try this

Sub Group_Rawmaterials()
    
Dim RowVar1 As Long
Dim Value1 As Long
    
    With Sheets("Sheet2")
    Value1 = .Range("F2").Value
    RowVar1 = .Cells.Find(What:=Value1, After:=[F2], SearchOrder:=xlByColumns, SearchDirection:=xlNext).Row - 1
        
    .Range("F2", "G" & RowVar1).Copy Sheets("Sheet3").Range("A2")
    End With
End Sub

another alternative might be:

Dim rngC As Range
With Sheets("Sheet1")
    Set rngC = .Range(.Cells(2, "F"), .Cells.Find(.Cells(2, "F"), .Cells(2, "F"), , , xlByColumns, xlNext).Offset(-1))
End With
Sheets("Sheet2").Cells(2, "A").Resize(rngC.Rows.Count).Value = rngC.Value

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save