±Þ¿©¸í¼¼¼ Ãâ·ÂÇÒ¶§ Àüüº¸±â¿¡¼ ¿ùº°·Î ÁöÁ¤Çؼ °¡·Î·Î µÎ¸í¾¿ Ãâ·ÂÀ» ÇÏ·Á°í Çϴµ¥
¿¡·¯°¡ ³ª´Âµ¥~ ÀÌ ÀÌ»óÀº Á¦ ¸Ó¸®¿¡ ÇÑ°è°¡ ¿À³×¿ä~
¾î¶»°Ô Çϸé ÁÁÀ»Áö ´äº¯ Á» ºÎŹµå·Á¿ä~
Sub ¸í¼¼¼Àμâ() Dim rng½ÃÀÛ As Range Dim i As Long Dim iPage As Integer, iÈ®ÀÎ As Integer Dim nmÀ̸§ As Name Set rng½ÃÀÛ = Range("nmÃâ·Â¹üÀ§").Range("A1").Offset(1, 0) If rng½ÃÀÛ = "" Then MsgBox "ÀμâÇÒ ³»¿ëÀÌ ¾ø½À´Ï´Ù.", vbInformation, "ÀÚ·á¾øÀ½" Exit Sub End If iPage = (Range("nmÃâ·Â¹üÀ§").CurrentRegion.Rows.Count - 1) '--// ¿¢¼¿ 2003 ÀÌÀü¿¡¼´Â 65536 Çà±îÁö¸¸ Áö¿øÇϳª 2007À̻󿡼´Â 1048576Çà±îÁö Áö¿ø If iPage > (1048576 / 31) Then MsgBox "Çѹø¿¡ ÀμâÇÒ ¼ö ÀÖ´Â ÆäÀÌÁö ¼ö°¡ ³Ñ½À´Ï´Ù." Exit Sub End If iÈ®ÀÎ = MsgBox("Àüü ÀμâÇÒ ÆäÀÌÁö ¼ö´Â ¡¼" & _ iPage & "¡½ÆäÀÌÁö ÀÔ´Ï´Ù. Àμâ¹æ¹ýÀ» ¼±ÅÃÇϼ¼¿ä." _ & String(2, vbCr) & _ "[¿¹] - ¹Ì¸®º¸±â ½ÇÇà" & vbCr & _ "[¾Æ´Ï¿À] - ¹Ì¸®º¸±â ¾øÀÌ ¹Ù·Î Àμâ" & vbCr & _ "[Ãë¼Ò] - Àμâ ÀÛ¾÷ Ãë¼Ò" _ , vbYesNoCancel + vbQuestion, "¹Ì¸®º¸±â") If iÈ®ÀÎ <> vbYes And iÈ®ÀÎ <> vbNo Then Exit Sub Application.ScreenUpdating = False For Each nmÀ̸§ In Names If Not (nmÀ̸§.Name Like "nm*") Then nmÀ̸§.Delete End If Next With Sheets("±Þ¿©_DB") Range(.Range("A4"), .Range("A4").End(xlToRight).End(xlDown)).CreateNames _ Top:=True, Left:=False, Bottom:=False, Right:=False End With Sheets("±Þ¿©¸í¼¼¼").Rows("31:65536").Delete Do While rng½ÃÀÛ.Offset(i, 0) <> "" With Sheets("±Þ¿©¸í¼¼¼") If i > 0 Then .Rows("1:31").Copy .Rows(31 * i + 1).PasteSpecial End If .Range("B" & 1 + 31 * i) = rng½ÃÀÛ.Offset(i, 0) & "³â " & rng½ÃÀÛ.Offset(i, 1) & "¿ù ºÐ ±Þ/»ó¿©¸í¼¼¼" .Range("j" & 1 + 31 * i) = rng½ÃÀÛ.Offset(i, 0) & "³â " & rng½ÃÀÛ.Offset(i, 1) & "¿ù ºÐ ±Þ/»ó¿©¸í¼¼¼" .Range("B" & 2 + 31 * i) = "'" & rng½ÃÀÛ.Offset(i, 0) & rng½ÃÀÛ.Offset(i, 1) & rng½ÃÀÛ.Offset(i, 2) .Range("j" & 2 + 31 * i) = "'" & rng½ÃÀÛ.Offset(i, 0) & rng½ÃÀÛ.Offset(i, 1) & rng½ÃÀÛ.Offset(i, 2) End With i = i + 1 Loop Sheets("ÀüüÀÛ¾÷").Select Application.CutCopyMode = False Application.ScreenUpdating = True Select Case iÈ®ÀÎ Case vbYes: Sheets("±Þ¿©¸í¼¼¼").PrintOut Preview:=True Case vbNo: Sheets("±Þ¿©¸í¼¼¼").PrintOut Preview:=False End Select End Sub
|