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.
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
“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein
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.
Code:
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
Please let me know if I'm just missing something hidden in the new and better ribbon system.
Thanks,
Mike
(Mike's Rant) said::
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