Copying and Pasting does not copy the cell borders

[ajoy123rocks]

I am unable to copy the entire cell including all borders and paste it onto the adjacent cell.

The original sheet is shown in 1.jpg
I want to delete P1 from row 18. When this is done U2 will remove to column G along with its border formatting. But I see a blue bottom border in Column G as shown in 2.jpg
The expected result is shown in 3.jpg

Kindly please help..

                           For col_num = col_num To 12
                                        
                                        'MsgBox stored_row & col_num
                                        
                                       
                                         If col_num = 12 Then
                                            Exit For
                                         End If
                                        
                                            If Sheets("DSS").Cells(stored_row, col_num + 1).Value <> "" Then
                                                
                                                Sheets("DSS").Cells(stored_row, col_num + 1).Copy
                                                Sheets("DSS").Cells(stored_row, col_num).PasteSpecial
                                                Application.CutCopyMode = False

                                                Sheets("DSS").Cells(stored_row, col_num + 1).Value = ""
                                            
                                            ElseIf Sheets("DSS").Cells(stored_row, col_num + 1).Value = "" Then
                                                
                                                Sheets("DSS").Cells(stored_row, col_num + 1).Copy
                                                Sheets("DSS").Cells(stored_row, col_num).PasteSpecial
                                                Application.CutCopyMode = False

                                                'Exit For
                                            End If
                                            
                                            If Sheets("DSS").Cells(stored_row - 1, col_num).Value <> "" Then
                                                    Cells(stored_row, col_num).Select
                                                        With Selection.Borders(xlEdgeTop)
                                                            .LineStyle = xlContinuous
                                                            .Color = vbBlue
                                                            .TintAndShade = 0
                                                            .Weight = xlThick
                                                        End With
                                            End If
                                             If Sheets("DSS").Cells(stored_row + 1, col_num).Value <> "" Then
                                                    Cells(stored_row, col_num).Select
                                                        With Selection.Borders(xlEdgeBottom)
                                                            .LineStyle = xlContinuous
                                                            .Color = vbBlue
                                                            .TintAndShade = 0
                                                            .Weight = xlThick
                                                        End With
                                            End If
                                            
                                Next

 

[dougmarkham]

Hi ajoy123rocks,

In order to use PasteSpecial, you must specify the XLPasteType enumeration after the PasteSpecial syntax.

Here is a good guide explaining this:

Wellsr.com: VBA-pastespecial-values-formats-formulas-and-more
Explained on microsoft's page here: excel.xlpastetype

A simple example of this can be found the in the code below:

Sub Paste_Range()
Dim range_to_copy As Range, range_for_pasting As Range
Set range_to_copy = Range("A1:F5")
Set range_for_pasting = Range("A7")

range_to_copy.Copy
range_for_pasting.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End Sub

Range("J1:L4").Copy
Range("J7").PasteSpecial xlPasteFormats

Below are the main options:
• xlPasteAll
• xlPasteAllExceptBorders
• xlPasteAllMergingConditionalFormats
• xlPasteAllUsingSourceTheme
• xlPasteColumnWidths
• xlPasteComments
• xlPasteFormats
• xlPasteFormulas
• xlPasteFormulasAndNumberFormats
• xlPasteValidation
• xlPasteValues
• xlPasteValuesAndNumberFormats

For instance, you could try:

Sheets("DSS").Cells(stored_row, col_num).PasteSpecial xlPasteAll