Excel 2007 Paste Special > Format

[herkiefan]

So I get back from vacation and on my monitor is a sticky note saying "You system has been upgraded to Office 2007. Please call if you have any questions." Ya, I have questions but the PC Support specialist certainly can't answer them. That's why I come to the gurus here.

I have a macro that basically copies two worksheets from one workbook and pastes them in to another workbook. It then formats the worksheets and sets a print area. It then saves the file.

All parts of the macro work except for part of the formatting. What doesn't work are hiding rows (some rows are hidden for presentation purposes) and autofitting the columns (when the values are pasted they are wider than the columns). Those two areas are highligted below.

Sub new_DIR_macro()

Sheets(Array("DOR", "DIR")).Select
    Sheets("DOR").Activate
    Cells.Select
    Selection.Copy
    Workbooks.Add
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    [COLOR=red yellow]Selection.PasteSpecial Paste:=xlPasteFormats[/color]
    Sheets("Sheet1").Name = "DOR"
    Sheets("Sheet2").Name = "DIR"
    ActiveWindow.DisplayGridlines = False
    ActiveWindow.Zoom = 80
    Sheets("DIR").Select
    Cells.Select
    [COLOR=red yellow]Cells.EntireColumn.AutoFit[/color]
    Range("A2").Select
    Sheets("DOR").Select
    With ActiveSheet.PageSetup
        .PrintArea = "$A$1:$K$82"
        .Orientation = xlPortrait
        .PaperSize = xlPaperLetter
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
        .Zoom = False
    End With
    ActiveWindow.DisplayGridlines = False
    Range("A2").Select
  
    fName = InputBox("Enter the file name date....   yyyymmdd", "DIR Filename")
    ChDir "R:\DOR REPORTS\2009\200906-JUN"
    ActiveWorkbook.SaveAs Filename:= _
        "R:\DOR REPORTS\2009\200906-JUN\DIR_" & fName & ".xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWindow.Close
    Sheets("DIR").Select
    Range("A2").Select
    Sheets("DOR").Select
    Range("A2").Select
End Sub

Is the new and upgraded piece o s&^t Excel not able to paste column and row formats?

Please let me know if I'm just missing something hidden in the new and better ribbon system.

Thanks,

Mike

:
And by the way, I had all of the Alt shortcuts memorized. Oh do I really hate that Alt F T does not set a print area. Does Microsoft hate end-users that would rather use their key board or have they been brow-beaten by the mouse-only idiots in to destroying a perfectly good spreadsheet program? Oh Lotus...how I miss thee.

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[PHV]

Please, define "Doesn't work"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[herkiefan]

This is the exact problem:

I have a worksheet that has hidden columns, and grouped rows.

When I copy an entire worksheet then paste special > formats in to a new workbook, the hidden columns are shown, the grouped rows are no longer grouped, and the column widths are not the same width as the original sheet.



“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[herkiefan]

Please ignore my question.
I figured it out.

It had to do with going from a 2003 file to a 2007 file.

I fixed it by making everything 2007.

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein