Copying Values and Formatting without Copy/PasteSpecial

Hi all,

I've written VBA code to loop through some data and paste the values and formatting into another sheet, and while it works okay, it has to go through a huge amount of data so I'm trying to eliminate the copy/pastespecial step to speed up the code.

Is there a way to write a code like:

Example

Range("A1:C1").Value = Range("A5:C5").Value

that will copy the formatting along with the values?


Here is the code I currently have written:

Dim RR As Long, j As Long, p As Long
        RR = Sheets("Lookups").Range("C" & Rows.Count).End(xlUp).Row
        j = 24
        p = 13
                
        For j = 24 To RR
            If Sheets("Lookups").Range("Q" & j).Value = "On" Then
                Sheets("Lookups").Range("B" & j & ":P" & j).Copy
                Sheets("Main Data").Range("B" & p & ":P" & p).Insert Shift:=xlDown       'inserts new row
                Sheets("Main Data").Range("B" & p).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                p = p + 1
            End If
    Next j

Thanks for your help.

Chris