Copy/Paste a row with a variable range

Celiaaa · May 29th 2018

Hi everyone,
I am stuck with a piece of code and would really need your help on this. I want my macro to copy the format of a the whole row from a variable range and paste this format to a an other row. However y code does not work on this and I can't find why. Below is the code I used (this is just a part of it, I just put the code that was not working).
I think I do not use the row property properly but I am not sure. Could you please help me on this ?
Thank you

Code

Sub MEF()
Dim Star8 As Integer
Dim Title8 As Range
Dim Rf As String


Rf = Chr(42)
lastRow = Range("A" &amp; Rows.Count).End(xlUp).Row


Set Title8 = Workbooks("try.xlsm").Sheet15.Range("A10")
Star8 = InStrRev(Title8.Value, Ref)


For x = 2 To lastRow - 1
y = InStrRev(Cells(x, 1).Value, Rf)
Select Case y
Case Is = Star8
Title8.Row.Copy
Cells(x, 1).Row.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Display More

**Luke M** · May 30th 2018

You want to use the EntireRow to get the range selected, not the Row property.

Code

Sub MEF()
    Dim Star8 As Integer
    Dim Title8 As Range
    Dim Rf As String
    Dim x As Long
    Dim lastRow As Long


    Rf = Chr(42)
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    'If you use code name of a sheet, don't include workbook object
    Set Title8 = Sheet15.Range("A10")
    'Had a typo here, should just be Rf
    Star8 = InStrRev(Title8.Value, Rf)
    
    For x = 2 To lastRow - 1
        y = InStrRev(Cells(x, 1).Value, Rf)
        Select Case y
            Case Star8
            
            Title8.EntireRow.Copy
            Cells(x, 1).EntireRow.PasteSpecial Paste:=xlPasteFormats
            Application.CutCopyMode = False
            
            'Carry on...

Display More

Copy/Paste a row with a variable range

Participate now!

Share