반복적인 작업은 매크로를 세팅해놓으면 도움이 된다. 전체자동화보다는 각각 사용할수 있게 모듈로 나누어놓는게 나은듯

절대참조, 상대참조를 이해하고 간단하게 기록한 다음 불필요한 구문을 삭제,수정하면 된다.

(code를 포스팅 할경우 ``` 는   의 형식을 갖게 되고, <pre> </pre>는 제일 하단 서식의 형식을 갖게된다. 

입찰대비표 data

1. 양식변환

<br />Sub 입찰대비표()
'
' 입찰대비표 매크로
'

'
    Columns("H:I").Select
    Range("I1").Activate
    Selection.EntireColumn.Hidden = True
    Rows("4:5").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A6").Select
    ActiveWindow.DisplayZeros = False
End Sub

2. 비율등 입력

<br />Sub 합계1위3칸아래에서실행()
'
' 합계1위3칸아래에서실행 매크로
'

'
    ActiveCell.FormulaR1C1 = "=R[-3]C/R[-3]C[-10]"
    ActiveCell.Offset(2, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-5]C[-10]-R[-5]C"
    ActiveCell.Offset(-2, 8).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-3]C/R[-3]C[-18]"
    ActiveCell.Offset(2, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-5]C[-18]-R[-5]C"
    ActiveCell.Offset(-2, 8).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-3]C/R[-3]C[-26]"
    ActiveCell.Offset(2, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-5]C[-26]-R[-5]C"
    ActiveCell.Offset(-2, 8).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-3]C/R[-3]C[-34]"
    ActiveCell.Offset(2, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-5]C[-34]-R[-5]C"
    ActiveCell.Offset(-2, 8).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-3]C/R[-3]C[-42]"
    ActiveCell.Offset(2, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-5]C[-42]-R[-5]C"
    ActiveCell.Offset(-2, 0).Rows("1:1").EntireRow.Select
    Selection.Style = "Percent"
    Selection.NumberFormatLocal = "0.0%"
    ActiveCell.Select
    Range("F6").Select
    ActiveWindow.FreezePanes = True
End Sub

분석 및 서식 추가 필요

자주쓰는 것들

날짜의 경우는 data 함수를 사용해서 yy, mm, dd 를 지정해주는 방법이 더 나았다.

<br />Sub zero()
'
' zero 매크로
'

'
    ActiveWindow.DisplayZeros = False
End Sub

Sub 사업자번호양식10자리()
'
' 사업자번호양식10자리 매크로
'

'
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],3)&-MID(RC[-1],4,2)&-RIGHT(RC[-1],5)"

End Sub


Sub 날짜8자리변환()
'
' 날짜8자리변환 매크로
'

'
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],4)&-MID(RC[-1],5,2)&-RIGHT(RC[-1],2)"

End Sub

출력

출력시 하단에 파일명, 탭명, 페이지 입력

<br />'
' PRINT_A3 매크로
'

'
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftFooter = "&F"
        .CenterFooter = "&A"
        .RightFooter = "&P / &N"
        .Orientation = xlLandscape
        .PaperSize = xlPaperA3
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
    End With
    Application.PrintCommunication = True
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$4:$5"
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = "&F"
        .CenterFooter = "&A"
        .RightFooter = "&P / &N"
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA3
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
    Application.PrintCommunication = True
    Range("A2").Select
End Sub

출력 두번째

Sub setting_printer()
'
' setting_printer 매크로
'

'
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$4:$5"
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = "&F"
        .CenterFooter = "&A"
        .RightFooter = "&P / &N"
        .LeftMargin = Application.InchesToPoints(0.708661417322835)
        .RightMargin = Application.InchesToPoints(0.708661417322835)
        .TopMargin = Application.InchesToPoints(0.748031496062992)
        .BottomMargin = Application.InchesToPoints(0.748031496062992)
        .HeaderMargin = Application.InchesToPoints(0.31496062992126)
        .FooterMargin = Application.InchesToPoints(0.31496062992126)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA3
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
End Sub

같은 값 위아래 셀 합치기

매크로 (출처: seekseek 블로그)

Sub mergecell()
'
' AutoMerge Macro
'
' 바로 가기 키: Ctrl+k
'
On Error Resume Next
'ActiveCell.Select
rowcnt = ActiveCell.Row
colcnt = ActiveCell.Column
a = Cells(rowcnt, colcnt).Value
rowcnt2 = rowcnt + 1

'빈칸이면 끝
While Cells(rowcnt2, colcnt).Value  ""
    While a = Cells(rowcnt2, colcnt).Value '같은 값이면 계속증가
       Cells(rowcnt2, colcnt).Value = "" '같으니까 값을 지워주자 ㅎㅎㅎ
       '그래야 값이 둘인데 하나는 없어진다는 메시지 창이 뜨지 않는다.
       rowcnt2 = rowcnt2 + 1
    Wend
       '이제 같은 값구역을 병합
       Range(Cells(rowcnt, colcnt), Cells(rowcnt2 - 1, colcnt)).Select

       With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Selection.Merge (False)
        
        '초기화

        rowcnt = rowcnt2
        a = Cells(rowcnt, colcnt).Value
        rowcnt2 = rowcnt + 1
Wend
'
End Sub