CODEWITHSUNDEEP
excelvba
Stupid.Fat.Cat
Stupid.Fat.Cat

Reputation: 11285

VBA:Excel how to copy including formatting and spacing

Right now, I'm doing this to copy a row from one sheet to another sheet and insert it at the end:

            Sheets("Monthly").Range("A1").EntireRow.Copy Destination:= _
            Sheets(Name).Range("A" & rows.Count).End(xlUp).Offset(1)

But this doesn't copy any formatting/cell spacing. Is that possible to do in vba?

Upvotes: 1

Views: 3632

Answers (2)

user3271518
user3271518

Reputation: 628

Using the Macro Recorder will give you the syntax for other formats. 

Range("A1").NumberFormat = "General"

Or 

.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteValues

Ex. of a way to solve it

Sub Copy()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

  ws.Range("A1").EntireColumn.Copy

    With ws.Range("G2").EntireColumn
        .PasteSpecial Paste:=xlPasteFormats
        .PasteSpecial Paste:=xlPasteValues
    End With

End Sub

There are a million ways to do this try

https://www.google.com/search?q=how+to+copy+formatting+in+excel+with+vba&oq=how+to+co&aqs=chrome.0.69i59l2j69i57j69i59j69i60j0.2240j0j7&sourceid=chrome&espv=210&es_sm=93&ie=UTF-8

Upvotes: 1

Dick Kusleika
Dick Kusleika

Reputation: 33145

Copy does copy the formatting. Maybe your formatting isn't what you think. I don't know what cell spacing means, but if it's column width, then Copy won't copy that. You'll have to set that explicitly, like

For i = 1 to rSrce.Cells.Count
    rDest.Cells(i).EntireColumn.ColumnWidth = rSrce.Cells(1).EntireColumn.ColumnWidth
Next i

Upvotes: 4

Related Questions