Excel VBA - Retaining format when using a loop code

[AmeliaBedelia]

Hello,
I am using a looping code that copies information from a source range to a destination range. It does not use copy and paste, but uses the code "DestRange.Value = smallrng.Value" It places the value, but does not retain any of the Font formatting (bold, underline, colors of font). I would like to know if there is a way to alter this code so that it copies the font formatting from the source range to the destination range. Or if there are any other suggestions for code that would complete the same function.

Here is a copy of the entire code:


Sheets("Form").Select
'Set the source range to "Comments" - a named range set to grab data from G1, D1, G2, G3, G4 on the Form sheet
Set SourceRange = Sheets("Sheet1").Range("Comments")

'Select Comments Sheet
Sheets("Comments").Select

'Find InsertComments - named range in first empty row in column A, it moves down as rows are inserted to always stay at bottom
Application.GoTo Reference:="InsertComments"

'Insert a row
Selection.EntireRow.Insert

'Set the destination to comments sheet
Set DestSheet = Sheets("Comments")

'Look for first empty row starting in Column A
lr = DestSheet.Cells(Rows.Count, "A").End(xlUp).Row
i = 1

'This now pastes the data into the columns in the same order as we specified in our named range
For Each smallrng In SourceRange.Areas

'We make DestRange the same size as smallrng and use the value property to give DestRange the same values
With smallrng
Set DestRange = DestSheet.Cells(lr + 1, i) _
.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = smallrng.Value
i = i + smallrng.Columns.Count

'It now loops up to the next smallrng until all the specified cells in our named range have been copied over and then moves on

Next smallrng

 

[Rick Rothstein]

Hello,
I am using a looping code that copies information from a source range to a destination range. It does not use copy and paste, but uses the code "DestRange.Value = smallrng.Value" It places the value, but does not retain any of the Font formatting (bold, underline, colors of font).

Given that Copy/Paste retains cell formatting and you want to retain cell formatting, why are you not using Copy/Paste? Equating value properties does exactly that (places a value into a range) and nothing more.

 

[AlphaFrog]

    Sheets("Form").Select
    [color=green]'Set the source range to "Comments" - a named range set to grab data from G1, D1, G2, G3, G4 on the Form sheet[/color]
    [color=darkblue]Set[/color] SourceRange = Sheets("Sheet1").Range("Comments")
    
    [color=green]'Select Comments Sheet[/color]
    Sheets("Comments").Select
    
    [color=green]'Find InsertComments - named range in first empty row in column A, it moves down as rows are inserted to always stay at bottom[/color]
    Application.GoTo Reference:="InsertComments"
    
    [color=green]'Insert a row[/color]
    Selection.EntireRow.Insert
    
    [color=green]'Set the destination to comments sheet[/color]
    [color=darkblue]Set[/color] DestSheet = Sheets("Comments")
    
    [color=green]'Look for first empty row starting in Column A[/color]
    lr = DestSheet.Cells(Rows.Count, "A").End(xlUp).Row
    i = 1
    
    [color=green]'This now pastes the data into the columns in the same order as we specified in our named range[/color]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] smallrng [color=darkblue]In[/color] SourceRange.Areas
    
        [color=green]'We make DestRange the same size as smallrng and use the value property to give DestRange the same values[/color]
[color=green]'        With smallrng[/color]
[color=green]'            Set DestRange = DestSheet.Cells(lr + 1, i) _
'                            .Resize(.Rows.Count, .Columns.Count)[/color]
[color=green]'        End With[/color]
[color=green]'        DestRange.Value = smallrng.Value[/color]
    
[B]        smallrng.Copy Destination:=DestSheet.Cells(lr + 1, i)[/B]
        i = i + smallrng.Columns.Count
    
        [color=green]'It now loops up to the next smallrng until all the specified cells in our named range have been copied over and then moves on[/color]
    
    [color=darkblue]Next[/color] smallrng

 

[AmeliaBedelia]

Thank you AlphaFrog - that worked, but now I am getting picky. Is there anyway to copy specific font settings, but not others? For example I want the Font, Font Style, Color, Effects. But I do not want the borders or Fill color to copy. Is that asking for too much now?

 

[Rick Rothstein]

Thank you AlphaFrog - that worked, but now I am getting picky. Is there anyway to copy specific font settings, but not others? For example I want the Font, Font Style, Color, Effects. But I do not want the borders or Fill color to copy. Is that asking for too much now?

Instead of copying the ones you want and maybe missing one (or more), why not copy the whole range and then remove the borders and fill colors from the range?