CODEWITHSUNDEEP
vbaexcel
Kulis
Kulis

Reputation: 1010

Copying cells using VBA

I want copy content of cells and make border around these. This is simple example:

example

I tried to write macro, which copy top value, marge lower cells and make borders:

example2

For i = 0 To M_NAME.find(What:="*", After:=M_NAME, _
            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column 

            M_NAME.Offset(1, i) = M_NAME.Offset(0, i)
            M_NAME.Offset(1, i).HorizontalAlignment = xlCenter
            M_NAME.Offset(1, i).Font.Color = rgbWhite

            With M_NAME.Offset(1, i).Borders
                .LineStyle = xlContinuous
                .Color = rgbWhite
                .Weight = 3
            End With
        Next i

M_NAME is reference to first top cell.

Upvotes: 0

Views: 89

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

Your source cells seem to already have the borders, so there is no need to create them again. You also need to consider the problems involved with working with merged cells. The Find method will only find the upper left cell of the merged area, so that will cause a problem with your code.

There is no need to copy cell by cell.

If you want to copy the first row, borders, merged cells, and all, you could use something like:

Option Explicit
Sub CopyRow()
    Dim rToCopy As Range, rDest As Range
    Dim M_NAME As Range

Set M_NAME = Range("M_NAME")
Set rToCopy = Range(M_NAME, Cells.Rows(M_NAME.Row).Find(what:="*", after:=M_NAME, _
    LookIn:=xlValues, searchdirection:=xlPrevious).MergeArea)

Set rDest = rToCopy.Offset(rowoffset:=1)
rToCopy.Copy rDest
End Sub

Upvotes: 1

Related Questions