Copy Range Only into a Specific Cell in another sheet

[MHamid]

Hello,

I've been trying to figure out how to select a range based on the last column and last row of data.

I need a code that will look for the word "Technology' in column L. Once it finds that word, I need it to select all data from column A to P. Copy that data and paste it into cell K22 of Sheet2. For example, range A27:P52 would be selected, but it the location of this range varies which is why I want to select range from first instance of the word technology to the last between columns A and P.

This is the code I have so far, but I am getting an error message "Method 'Range' of object '_Global' failed" in the "Range("rangex").Copy line of the code.

What did I do wrong?

Sub CopyTechnology()
Dim LRow As Long, iRow As Long
Dim LastCol As Long
Dim rangex As Range
With Worksheets("Sheet1")
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    LRow = .Cells(.Rows.Count, "L").End(xlUp).Row
    
    rangex = .Cells(LRow, 1).Resize(, LastCol)
    
    For iRow = LRow To 1 Step -1
        If .Cells(iRow, "L").Value = "Technology" Then
            Range("rangex").Copy
        End If
    Next iRow
End With
End Sub

 

[Coding4Fun]

Maybe? Still work to be done to get to your required result but this fixes the problem you ran into.....sort of

Post back if you need additional assistance.

Sub CopyTechnology()
Dim LRow As Long, iRow As Long
Dim LastCol As Long


With Worksheets("Sheet1")
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    LRow = .Cells(.Rows.Count, "L").End(xlUp).Row
    
    For iRow = LRow To 1 Step -1
        If .Cells(iRow, "L").Value = "Technology" Then
            Range("A" & LRow & ":P" & LRow).Copy
        End If
    Next iRow
End With


End Sub

 

[MHamid]

Hello,

I was on vacation for the past week and a half.
I tested your code today and it only selected the last cell outside of the range that I need.

However, I saw another code that I could use and it works. Let me know if there is an easier way.

Sub CopyTech()
Dim findrow As Long, findrow2 As Long
    
On Error GoTo errhandler
findrow = Range("L:L").Find("Technology", Range("L1")).Row
findrow2 = Range("L:L").Find("L1_Area", Range("L" & findrow)).Row
Range("A" & findrow & ":P" & findrow2 - 1).Copy
Worksheets("Sheet2").Range("J24").PasteSpecial paste:=xlPasteValues
Application.CutCopyMode = False
Exit Sub
errhandler:
    MsgBox "No Cells containing specified text found"
End Sub

Thank you

 

[Coding4Fun]

Hello,

I was on vacation for the past week and a half.
I tested your code today and it only selected the last cell outside of the range that I need.

However, I saw another code that I could use and it works. Let me know if there is an easier way.

Sub CopyTech()
Dim findrow As Long, findrow2 As Long
    
On Error GoTo errhandler
findrow = Range("L:L").Find("Technology", Range("L1")).Row
findrow2 = Range("L:L").Find("L1_Area", Range("L" & findrow)).Row
Range("A" & findrow & ":P" & findrow2 - 1).Copy
Worksheets("Sheet2").Range("J24").PasteSpecial paste:=xlPasteValues
Application.CutCopyMode = False
Exit Sub
errhandler:
    MsgBox "No Cells containing specified text found"
End Sub

Thank you

I was aware of the flaw in the coding thus the comment in my previous post. As for your comment of an "easier way" what makes it difficult that you are looking to make easier?

 

[MHamid]

Hello, Not that I'm looking for something "easier", I just want to ensure that there is nothing extra in the code that is not needed. It looks fine to me, but maybe there is something that I missed.Thank you