Excel VBA 원하는 행,열 추출하여 정리하기.

아래 예시파일을 보시고 활용하세요.

Sub MakeTable()
    Dim srcRowData
    Dim destRowData
    Dim destRowWidth
    Dim i As Integer
    Dim rowCount As Integer
    Dim strBuf As String
    Dim mArr As String
    srcRowData = Array("A", "E", "G", "J", "K", "AE", "P")
    destRowData = Array("A", "B", "C", "D", "E", "F", "G")
    destRowWidth = Array(12.13, 22.5, 15, 20.88, 10.88, 20.38, 10.88)
    rowCount = ActiveWorkbook.ActiveSheet.UsedRange.Rows.Count
   
    Set srcBook = ActiveWorkbook
    Set NewBook = Workbooks.Add
   
  
   
    strBuf = "A2:F" & (rowCount + 1)
    With NewBook.ActiveSheet.Range(strBuf)
        .Font.Name = "맑은 고딕"
        .Font.Size = 10
        .HorizontalAlignment = xlCenter
        .Borders.LineStyle = xlContinuous
        .Borders.Weight = xlThin
        .Borders.ColorIndex = 0
        .BorderAround ColorIndex:=0, Weight:=xlThin
    End With
   
    With NewBook.ActiveSheet.Range("A2:F2")
        .Font.Bold = True
        .Interior.Color = RGB(0, 32, 96)
        .Borders.LineStyle = xlContinuous
        .Borders.Weight = xlThin
        .Font.Color = RGB(255, 255, 255)
    End With
   

    For i = LBound(srcRowData) To UBound(srcRowData)
        strBuf = srcRowData(i) & "1:" & srcRowData(i) & rowCount
        srcBook.ActiveSheet.Range(strBuf).Copy
        strBuf = destRowData(i) & "2"
        NewBook.ActiveSheet.Range(strBuf).PasteSpecial (xlPasteValues)
        NewBook.ActiveSheet.Range(strBuf).ColumnWidth = destRowWidth(i)
    Next i


   
    NewBook.ActiveSheet.Range("A2").Value = "Invoice No"
    NewBook.ActiveSheet.Range("B2").Value = "ProjectCode"
    NewBook.ActiveSheet.Range("C2").Value = "거래처"
    NewBook.ActiveSheet.Range("D2").Value = "기간"
    NewBook.ActiveSheet.Range("E2").Value = "금액(\)"
    NewBook.ActiveSheet.Range("F2").Value = "비고"
    strBuf = "E3:E" & (rowCount + 1)

   
   
End Sub