Tweak code to copy range formatting too (instead of just copying values)

**Andrew.Trevayne** · 02-04-2017, 01:57 AM

Hi all,
Need some help in tweaking the below codes.
Currently it is only copying the values from my "Data Source" sheeet.
Can you help me modify it so that it copies the formatting as well?

Sub Selection()
Application.ScreenUpdating = False

Dim cRng As Range, St As String, Cl As Range, Rng As Range
With Sheets("By Site")
    St = .Range("VV8").Value
    Range("B32:P69").ClearContents
    Set cRng = Sheets("Data Source").Rows("160:160").SpecialCells(xlCellTypeConstants)

    For Each Cl In cRng
        If Cl = St Then
            Set Rng = Cl.Offset(, 2).CurrentRegion
            Rng.Offset(3).Resize(Rng.Rows.Count - 3).Copy
           .Range("B32").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        End If
    Next Cl
End With
End Sub

I tried the below but no dice.

    .Range("B32").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False

Thank you in advance.

**jindon** · 02-04-2017, 02:04 AM

Perhaps

        If Cl = St Then
            Set Rng = Cl.Offset(, 2).CurrentRegion
            Rng.Offset(3).Resize(Rng.Rows.Count - 3).Copy
           .Range("B32").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        End If

to

        If Cl = St Then
            Set Rng = Cl.Offset(, 2).CurrentRegion
            Rng.Offset(3).Resize(Rng.Rows.Count - 3).Copy .Range("B32")
        End If

**Andrew.Trevayne** · 02-04-2017, 03:01 AM

Originally Posted by jindon

Perhaps

        If Cl = St Then
            Set Rng = Cl.Offset(, 2).CurrentRegion
            Rng.Offset(3).Resize(Rng.Rows.Count - 3).Copy
           .Range("B32").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        End If

to

        If Cl = St Then
            Set Rng = Cl.Offset(, 2).CurrentRegion
            Rng.Offset(3).Resize(Rng.Rows.Count - 3).Copy .Range("B32")
        End If

Thank you sir. Working perfectly.

Tweak code to copy range formatting too (instead of just copying values)

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Tweak code to copy range formatting too (instead of just copying values)

Re: Tweak code to copy range formatting too (instead of just copying values)

Re: Tweak code to copy range formatting too (instead of just copying values)

Thread Information

Users Browsing this Thread

Similar Threads

Tweak to VBA code for copying from Excel into Powerpoint

[SOLVED] Tweak my code so it does not copy rows that are in the shaded area

[SOLVED] Copy range values without copying the formatting

Code tweak needed: Copy macro not copying formats only values (with getobject)

Copying Values and Formatting without Copy/PasteSpecial

Copy Table Code - Tweak

code tweak to copy data from sheet1 to sheet2

Bookmarks

Bookmarks

Posting Permissions