| |
'-----------------------------
Sub btn_run_Click()
'-----------------------------
Application.ScreenUpdating = False
Dim shtX As Worksheet:
'기존 시트 삭제
Application.DisplayAlerts = False
For Each shtX In Worksheets
If shtX.Name <> "main" And shtX.Name <> "기타영수증" Then shtX.Delete
Next shtX
Application.DisplayAlerts = True
' data sheet
Set shtX = Worksheets("main")
' template sheet
Dim shtZ As Worksheet: Set shtZ = Worksheets("기타영수증")
' template sheet visible
shtZ.Visible = xlSheetVisible
'-----------------------------------
Dim shtY As Worksheet
'raw data
Dim rngX As Range: Set rngX = shtX.Range("B19").CurrentRegion
Dim rngRow As Range
' filtering-no data
If rngX.Rows.Count = 1 Then MsgBox "No Data": Exit Sub
For r = 2 To rngX.Rows.Count
' template sheet copy
shtZ.Copy After:=Worksheets(Worksheets.Count)
Set shtY = Worksheets(Worksheets.Count)
' data matching
Set rngRow = rngX.Rows.Item(r)
shtY.Name = rngRow.Cells(2).Value & Replace(rngRow.Cells(1).Value, "-", "") & rngRow.Cells(9).Value
'이름
shtY.[E6].Value = rngRow.Cells(9).Value
'Flight
shtY.[K6].Value = rngRow.Cells(7).Value
'차량
shtY.[E7].Value = rngRow.Cells(2).Value
'예약상품
shtY.[E8].Value = rngRow.Cells(4).Value
'Date
shtY.[E9].Value = rngRow.Cells(1).Text & " " & rngRow.Cells(6).Text
'Pax
shtY.[E10].Value = rngRow.Cells(8).Value
'Remark
shtY.[E11].Value = rngRow.Cells(11).Value
Next
shtZ.Visible = xlSheetVeryHidden
shtX.Activate
Application.ScreenUpdating = True
End Sub
'-----------------------------
Sub btn_print()
'-----------------------------
Application.ScreenUpdating = False
Dim shtX As Worksheet, shtY As Worksheet
'기존 시트 삭제
Application.DisplayAlerts = False
For Each shtX In Worksheets
If shtX.Name <> "main" And shtX.Name <> "기타영수증" Then shtX.Delete
Next shtX
Application.DisplayAlerts = True
' data sheet
Set shtX = Worksheets("main")
' template sheet
Set shtY = Worksheets("기타영수증")
' template sheet visible
shtY.Visible = xlSheetVisible
'raw data
Dim rngX As Range: Set rngX = shtX.Range("B19").CurrentRegion
Dim rngRow As Range
For r = 2 To rngX.Rows.Count
Set rngRow = rngX.Rows.Item(r)
'이름
shtY.[E6].Value = rngRow.Cells(9).Value
'Flight
shtY.[K6].Value = rngRow.Cells(7).Value
'차량
shtY.[E7].Value = rngRow.Cells(2).Value
'예약상품
shtY.[E8].Value = rngRow.Cells(4).Value
'Date
shtY.[E9].Value = rngRow.Cells(1).Text & " " & rngRow.Cells(6).Text
'Pax
shtY.[E10].Value = rngRow.Cells(8).Value
'Remark
shtY.[E11].Value = rngRow.Cells(11).Value
'인쇄
shtY.PrintOut
Next
shtY.Visible = xlSheetVeryHidden
shtX.Activate
Application.ScreenUpdating = True
End Sub
'---------------------------------------
Sub btn_print_pdf()
'---------------------------------------
Application.ScreenUpdating = False
Dim shtX As Worksheet, shtY As Worksheet
'기존 시트 삭제
Application.DisplayAlerts = False
For Each shtX In Worksheets
If shtX.Name <> "main" And shtX.Name <> "기타영수증" Then shtX.Delete
Next shtX
Application.DisplayAlerts = True
' data sheet
Set shtX = Worksheets("main")
' template sheet
Set shtY = Worksheets("기타영수증")
' template visible
shtY.Visible = xlSheetVisible
'raw data
Dim rngX As Range: Set rngX = shtX.Range("B19").CurrentRegion
Dim rngRow As Range
'make folder
'-----------------
Dim sFolder As String: sFolder = "D:\pdf_sample"
If Dir(sFolder, vbDirectory) = "" Then MkDir sFolder
Dim sFile As String
For r = 2 To rngX.Rows.Count
Set rngRow = rngX.Rows.Item(r)
'이름
shtY.[E6].Value = rngRow.Cells(9).Value
'Flight
shtY.[K6].Value = rngRow.Cells(7).Value
'차량
shtY.[E7].Value = rngRow.Cells(2).Value
'예약상품
shtY.[E8].Value = rngRow.Cells(4).Value
'Date
shtY.[E9].Value = rngRow.Cells(1).Text & " " & rngRow.Cells(6).Text
'Pax
shtY.[E10].Value = rngRow.Cells(8).Value
'Remark
shtY.[E11].Value = rngRow.Cells(11).Value
'save to pdf
sFile = rngRow.Cells(2).Value & Replace(rngRow.Cells(1).Value, "-", "") & rngRow.Cells(9).Value
shtY.ExportAsFixedFormat xlTypePDF, sFolder & "\" & sFile & ".pdf"
Next
shtY.Visible = xlSheetVeryHidden
shtX.Activate
Application.ScreenUpdating = True
End Sub
|
|
엑셀에 대한 질문과
답변을 올려주세요. 단, 취지에 맞지 않는 글은 운영자가 삭제합니다.
