XL 북붙, 지우기, 시트 추가

20201224 2312 

 

00 Intro

One of the most frequenty action when using Excel, is copy & paste, clear contents and add sheets.

(it looks like that the above sentence is wrong)

 

a="test"

worksheets(a).usedrange.clearcontents

worksheets(a).usedrange.copy

worksheets(a).range("a1").pastespecial xlpastevalues

worksheets(a).range("a1").pastespecial xlpasteformats

 

01 codes written on bloomberg pc

Sub zSaveBackupFile()

zPath = [c2]
zName = ActiveSheet.Name
zNow = Format(Now, "yyyymmdd HHMM")
zFile = zName & "_" & zNow
[c1] = zNow

Range([a1], [a1].SpecialCells(xlLastCell)).Select

Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, _
                       Operation:=xlNone, _
                       SkipBlanks:=False, _
                       Transpose:=False
Application.CutCopyMode = False

ActiveWorkbook.SaveAs Filename:=zPath & zFile, _
                      FileFormat:=xlOpenXMLWorkbook, _
                      CreateBackup:=False
ActiveWindow.Close
Sheets(zName).Select

End Sub