CODEWITHSUNDEEP
vbaexcel
Developer
Developer

Reputation: 4321

Copy formatted data from one sheet to another

i have preformatted table HEAD in range from A4 to O5, and i need to copy it from my Hidden Settings page to a new created one with all formatting ( text aligment, column sizes etc. ).

I do this:

    Dim WS As Worksheet
    Dim SheetName As String
    SheetName = TextBox1.Text
    Sheets.Add.Name = SheetName
    Sheets("AdminSettings").Range("A4:O5").Copy
    Sheets(SheetName).Paste
    Sheets("Programma").Select

I have found this:

    Sheets(SheetName).PasteSpecial xlPasteFormats

but it fails

This is what i need to see:

enter image description here

this is what i get:

enter image description here

It seems that Text is formatted as needed, but column and row sizes - are not remaining, how to achieve this?

Upvotes: 0

Views: 2388

Answers (1)

Joe
Joe

Reputation: 6827

First, to use .PasteSpecial, you have to paste into a specific range on a worksheet, so:

Sheets(SheetName).Range("A1").PasteSpecial ...

You are attempting to copy column widths and row heights. In order to paste row heights, the entire row must be copied and pasted. In order to paste column widths, a second call to .PasteSpecial xlPasteColumnWidths is required.

So:

Sheets("AdminSettings").Range("4:5").Copy
Sheets(SheetName).Range("A1").PasteSpecial xlPasteAll
Sheets(SheetName).Range("A1").PasteSpecial xlPasteColumnWidths

should do it.

Upvotes: 1

Related Questions