Column Width and Row Height 1

[brianh123]

Hello,

On my excel SS I am copying a range that is formatted nicely from one worksheet to another. But when I paste it on a new worksheet, it loses all of the nice column/row (width/height) formatting. How do I set it back to how it was, I mean so it fits nicely on one page...etc.

 

[brianh123]

One thing to note, I would always no the amount of cells and rows in the paste.

 

[Stoffel24]

You could try copying a pasting the column widths

Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

There doesn't appear to be a constant like xlRowHeights. But I didn't look too hard. Otherwise you may have to create an array and populate this with the row heights on the original and then use this array to format the row heights on the copy. But you will at least have the columnwidths.

 

[TonyJollans]

I just recorded the following code

Cells.Select
Selection.Copy
ActiveSheet.Next.Select
Cells.Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

This took all the formats from the first sheet and applied it to the second. Row heights alone can be copied this way if whole rows are selected, and column widths alone if columns are selected. It doesn't seem to work with ranges which are not entire rows, columns, or sheets.

Enjoy,
Tony

 

[brianh123]

TonyJollans - I tried this but I am using ranges (unfortunately). Could I resize the entire list of rows and columns after I paste?

 

[TonyJollans]

Hi brianh123,

A quick and easy answer. Yes. If formatting the whole sheet fits your need then it's easy. If you want to be a little more precise, then the best way of doing it will depend on exactly what you have and exactly what you want. It is possible to arrange almost anything, but it is not necessarily very efficient!

Enjoy,
Tony

 

[brianh123]

Yeah, formatting the whole sheet is fine. How would I do that?

 

[TonyJollans]

The code posted above should be easy enough to adapt, but I just re-read the thread and am not sure if you are doing this via code or not.

If you are doing this manually, select the sheet with the formatting by clicking the grey square above row 1 and to the left of column A. Select Edit-Copy (or press <Ctrl><C>) to copy it. Now go to the sheet where you want to put the formatting and select it (as above). Finally select Edit-Paste Special and under Paste click on Formats.

If you have the Format Painter icon (a little paintbrush) on your toolbar you can shortcut the process. Select the first sheet, click the icon, go to the second sheet and select it.

If you are using (or want to use) code just record the above process.

Enjoy,
Tony

 

[brianh123]

No, I do not mean a manual resizing. I tried adapting the above code but was unsuccessful.

 

[originalxavier]

If you know the size of what your rows are supposed to be you can write code to change each row one by one. Since you have a set number of rows, this may be what you want to do... Let me know if you need sample Xavier

----------------------------------------
&quot;Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far the Universe is winning.&quot;
Rich Cook
----------------------------------------

 

[TonyJollans]

Hi again,

The following code should do what you want - all you have to do is change the literals where highlighted.

    Dim FromSheet As String, ToSheet As String
    Dim FromCol As String, ToCol As String
    Dim FromRow As String, ToRow As String
    
    FromSheet = "Sheet1"

' Put your From Sheet Name here

    ToSheet = "Sheet2"

' Put your To Sheet Name here

    FromCol = "$B"

' Put your From Column here

    ToCol = "$H"

' Put your To Column here

    FromRow = "$3"

' Put your From Row here

    ToRow = "$11"

' Put your To Row here

    Worksheets(FromSheet).Select
    Range(FromCol & FromRow & ":" & ToCol & ToRow).Select
    Selection.Copy
    
    Worksheets(ToSheet).Select
    Range(FromCol & FromRow & ":" & ToCol & ToRow).Select
    ActiveSheet.Paste
    
    Worksheets(FromSheet).Select
    Range(FromCol & ":" & ToCol).Select
    Selection.Copy
    Worksheets(ToSheet).Select
    Range(FromCol & ":" & ToCol).Select
    Selection.PasteSpecial Paste:=xlFormats, _
                           Operation:=xlNone, _
                           SkipBlanks:=False, _
                           Transpose:=False

    Worksheets(FromSheet).Select
    Range(FromRow & ":" & ToRow).Select
    Selection.Copy
    Worksheets(ToSheet).Select
    Range(FromRow & ":" & ToRow).Select
    Selection.PasteSpecial Paste:=xlFormats, _
                           Operation:=xlNone, _
                           SkipBlanks:=False, _
                           Transpose:=False

Hope it helps,
Tony