VBA Excel: replicate a formatting
Solvedyg_be Posted messages 23437 Registration date Status Contributor Last intervention -
Hello,
I want to replicate the formatting from a range of cells Source to another range of cells Destination using the following code:
Sub Reproduce_Formatting()
Dim NJ, Source, Destination As Range
Set NJ = ActiveCell
Set Source = NJ.Offset(-1, 0) : NJ.Offset(-1, 8)
Source.Copy
Set Destination = NJ : NJ.Offset(10,8)
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
The line Set Source is causing a bug: I've probably coded the source range to copy incorrectly?
It's probably the same for the line Set Destination ...
2 answers
-
Hello,
You have already used the range() function, have you forgotten it?
Set Origine = range ( NJ.Offset(-1, 0) , NJ.Offset(-1, 8) )
-
Hello,
It seems that you have some errors in your code regarding the definition of the source and destination ranges. Here is a corrected version of your code:Sub Reproduce_Formatting()
Dim NJ As Range, Source As Range, Destination As Range
'Definition of the active cell as the starting point
Set NJ = ActiveCell
'Definition of the source range (one cell above and 8 columns to the right of NJ)
Set Source = NJ.Offset(-1, 0).Resize(1, 8)
'Copying the formatting from the source range
Source.Copy
'Definition of the destination range (active cell and 10 rows down and 8 columns to the right of NJ)
Set Destination = NJ.Offset(10, 8)
'Special paste of the formatting into the destination range
Destination.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Disabling copy mode
Application.CutCopyMode = False
End Sub
There you go.
