simplehope

¦^´_ 12# ­ã´£³¡ªL

·PÁ­ã¤jµo¥\±Ï¥@¡I
­ã¤jªº·§©À¬O¹ï¿é¥X­¶°µ­×§ï¡A«ÜÅå³Yµ{¦¡½X³º¯à³o¼Ë¼g¡I¤Ó¼F®`¡I´Nºâ§Ú·Q¯}ÀY¤]·Q¤£¥X¨Ó¡K
µ{¦¡½X§¹¥þ¨S°ÝÃD¡A±©¤@ªº°ÝÃD¬O¤p§Ì¬Ý¤£¤ÓÀ´¥H¤U¡G

.SpecialCells(xlCellTypeConstants, 22).EntireRow.Delete '§R°£¡e¤å¦r¡f®æ¾ã¦C
¬A¸¹¤º¬°¦ó­n¥[ ,22³o°Ñ¼Æ? F1¬d¸ß¨S¬Ý¨ì¦³»¡©ú22

¤U¸ü (29.97 KB)

2016-10-9 17:41

µ{¦¡½X³Ì«á¥[ªºOn Error GoTo 0¡A¥Î·N¬°¦ó¡H
F1¬d¸ß: °±¤î²{¦bµ{§ÇùØ¥ô¦ó¤w±Ò°Êªº¿ù»~³B²zµ{¦¡¡C
·|«Øij¥ô¦óµ{¦¡½X¡A ³£¦bµ²§À¥[¤W"On Error GoTo 0" ¶Ü¡H

­ã´£³¡ªL

¨Sª`·N­ìªí¤½¦¡°Ñ·Ó®æ¦b½Æ»s½d³ò¤§¥~¡A­×§ï¤@¤U¡G

With xSht
¡@¡@¡@.Name = SHN:  .Cells.Clear '­«©R¦W, ²M°£¤º®e
¡@¡@¡@.[BK3] = vSht.[BK3].Value
¡@¡@¡@.[AV1] = vSht.[AV1].Value
¡@¡@¡@.[BI3] = vSht.[BI3].Value
¡@¡@¡@R = Val(vSht.[AT51]) * 52: If R = 0 Then Exit Sub '¨ú±o­¶¼Æ
¡@¡@¡@vSht.Range("A1:AM" & R).Copy .[A1] '¶K¤W¸ê®Æ
¡@¡@¡@.Range("A16:A" & R) = "=TEXT(COUNT(AK$16:AK16),""'000"")" '§Ç¸¹¤½¦¡
¡@¡@¡@.Range("A1:AM" & R) = .Range("A1:AM" & R).Value '¥þ³¡¤º®e¶K¦¨­È
¡@¡@¡@For Each vR In vSht.[A1:AM1]
¡@¡@¡@¡@¡@.Range(vR.Address).ColumnWidth = vR.ColumnWidth 'Äæ¼e
¡@¡@¡@Next
¡@¡@¡@.[BK3] = "": .[AV1] = "": .[BI3] = ""
End With

simplehope

¦^´_ 11# GBKEE

­º¥ý·PÁÂG¤jªá¨º»ò¦h®É¶¡¡AÁÙ³o»ò§Ö¦^À³¡A¶W·P°Êªº¡I
°õ¦æ«á·|¦³"Åܼƥ¼©w¸q"¿ù»~¡Aµ{¦¡Åã¥Ü¦b For i = 0 To .HPageBreaks.Count ¡A·í¤¤ªºi ¤Ï¥Õ

¤p§Ì¥Î¦Û¤w­ì¥»ªºVBA½X¡A¤g¬¶¸Ñ¨M°ÝÃD¦p¤U¡G
¦]¯à¤O¤£¨¬±q¿é¥X­¶(¨Ó·½­¶)§ï½Æ»s½d³ò¡A´N´«±q¿é¤J­¶(¶×¥X­¶) ¤U¤â
­ì¥»§PÂ_[A16]¦V¤U¨ì³Ì«á¤@¦C¦Aoffset¤@¦C, §ï¥Ñ±q[F16]¶}©l§PÂ_¡A¦V¤U¨ì³Ì«á¤@¦C¦Aoffset¨ìAÄæ¡A
¦]FÄæ¦b¿é¥X­¶­YªÅ¥Õ¡A­ì¥»´NµL¤½¦¡(µL¸ê®Æ)¡A©Ò¥H¨ì¤F¶×¥X­¶¤]¬OµL¸ê®Æ¡A¥Î¥H¤W¤èªk«K¥i¥H¦³½Æ»s¸ê®Æ³sÄò©Ê

«Ü¨ØªA«D¨Ï¥ÎªÌªºG¤j¡A¯à¼g¥X²Å¦X¹ê»Ú¥Î³~¤S¦p¦¹Â²¬ù¦³®Ä²vªºµ{¦¡½X¡I
¤p§Ì¥\¤O©|²L¼g¥Xªºµ{¦¡«Ü²ÊÁW¡A¹ïG¤jµ{¦¡½X¼È®É¥u¯à±æ¦Ó¿³¹Ä¡AºCºC¬ã¨s°Ú

1. Sub ¶×¥X¦a½S¸ê®Æ¨ì·s¤u§@ªí()
   
2.    
   
3.     shn = ActiveSheet.Name
   
4.         
   
5.     '¨¾§b1
   
6.     For e = 2 To Sheets.Count
   
7.         If shn & "¶×¥X" = Sheets(e).Name Then
   
8.             Application.DisplayAlerts = False
   
9.             Sheets(shn & "¶×¥X").Delete
   
10.             Application.DisplayAlerts = True
    
11.         Exit For
    
12.     End If
    
13.     Next
    
14. 
    
15.    
    
16.     Application.ScreenUpdating = False
    
17.    
    
18. 
    
19. 
    
20.     Worksheets.Add after:=Worksheets(Sheets.Count)
    
21.     Worksheets(Sheets.Count).Name = shn & "¶×¥X"
    
22. 
    
23.     '§ì¨úÄæ¦ì ·s¼W
    
24.     Worksheets(shn).Select
    
25.     Range("A1:AM15").Select
    
26.     Range("A1:AM15").Copy
    
27.     Worksheets(Sheets.Count).Select
    
28.     Range("A1").Select
    
29.     Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
    
30.     SkipBlanks:=False, Transpose:=False
    
31.     ActiveSheet.Paste
    
32. 
    
33.     '§ì¨ú¨C­¶¸ê®Æ¤º®e(¨Ï¥Î°j°é)
    
34.     Worksheets(shn).Select
    
35.     Dim i As Integer, j As Integer
    
36.     j = Range("AT51").Value
    
37.    
    
38.     For i = 16 To 16 + j * 52 Step 52 'À³­nJ-1, ¦ý­Y¥u¦³¤@­¶·|¦³¿ù¡A¦h¶×¥X¤@­¶¨S®t
    
39.         Worksheets(shn).Select
    
40.         Range("a" & i & ":am" & i).Select
    
41.         Range(Selection, Selection.End(xlToRight)).Select
    
42.         Range(Selection, Selection.End(xlDown)).Select
    
43.         Selection.Copy
    
44.         Worksheets(Sheets.Count).Select
    
45. 
    
46.     If Worksheets(Sheets.Count).Range("F16") = "" Then
    
47.         Range("A16").Select
    
48.         ActiveSheet.Paste '¥ý¶K¤@¦¸§t¤½¦¡
    
49.         Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, _
    
50.         SkipBlanks:=False, Transpose:=False '¦A¶K¤@¦¸§â¤½¦¡®³±¼
    
51.         Range("F16").End(xlDown).Offset(1, -5).Clear '§R°£AÄæ¸ê®Æ,¥H§Q¶K¤W¸ê®Æ³sÄò
    
52.     Else
    
53.         Worksheets(Sheets.Count).Range("F16").End(xlDown).Offset(1, -5).Select
    
54.         ActiveSheet.Paste '¥ý¶K¤@¦¸§t¤½¦¡
    
55.         Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    
56.         xlNone, SkipBlanks:=False, Transpose:=False '¦A¶K¤@¦¸§â¤é´ÁÅܬ°¤å¦r
    
57.       
    
58.     End If
    
59. Next
    
60. 
    
61. end sub

½Æ»s¥N½X

­ã´£³¡ªL

1. Sub ¶×¥X¦a½S¸ê®Æ¨ì·s¤u§@ªí()
   
2. Dim vSht As Worksheet, R&, vR As Range, SHN$, xSht As Worksheet
   
3. 
   
4. Set vSht = ActiveSheet: SHN = vSht.Name & "¶×¥X"
   
5. On Error Resume Next: Set xSht = Sheets(SHN): On Error GoTo 0
   
6. If xSht Is Nothing Then Set xSht = Sheets.Add(after:=Sheets(Sheets.Count))
   
7. 
   
8. With xSht
   
9.      .Name = SHN:  .Cells.Clear '­«©R¦W, ²M°£¤º®e
   
10.      R = Val(vSht.[AT51]) * 52: If R = 0 Then Exit Sub '¨ú±o­¶¼Æ
    
11.      vSht.Range("A1:AM" & R).Copy .[A1] '¶K¤W¸ê®Æ
    
12.      .Range("A16:A" & R) = "=TEXT(COUNT(AK$16:AK16),""'000"")" '§Ç¸¹¤½¦¡
    
13.      .Range("A1:AM" & R) = .Range("A1:AM" & R).Value '¥þ³¡¤º®e¶K¦¨­È
    
14.      For Each vR In vSht.[A1:AM1]
    
15.          .Range(vR.Address).ColumnWidth = vR.ColumnWidth 'Äæ¼e
    
16.      Next
    
17. End With
    
18. 
    
19. On Error Resume Next
    
20. With xSht.Range("AK16:AK" & R)
    
21.      .SpecialCells(xlCellTypeConstants, 22).EntireRow.Delete '§R°£¡e¤å¦r¡f®æ¾ã¦C
    
22.      .SpecialCells(xlCellTypeBlanks).EntireRow.Delete  '§R°£¡eªÅ¥Õ®æ¡f¾ã¦C
    
23. End With
    
24. On Error GoTo 0
    
25. 
    
26. xSht.Select
    
27. End Sub

½Æ»s¥N½X

¡@
Xl0000046.rar (144.56 KB)

¤U¸ü¦¸¼Æ: 6

2016-10-9 10:33

¡@
¡@

GBKEE

¦^´_ 9# simplehope
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub ¶×¥X¦a½S¸ê®Æ¨ì·s¤u§@ªí()
   
3.     Dim Sh(1 To 2), Rng(1 To 2) As Range, xCol As Integer, R As Integer
   
4.     Application.ScreenUpdating = False
   
5.     Set Sh(1) = Sheets("Mom (38P) (2)")    '**¨¾¤î¥X¿ù: «ü©w¤u§@ªí¦WºÙ***
   
6.     'Set Sh(1) = ActiveSheet ''§ì¥Ø«e¤u§@ªí¦WºÙ
   
7.     '¨¾§b1
   
8.     For Each Sh(2) In Sheets
   
9.         If InStr(Sh(2).Name, "¶×¥X") Then
   
10.             Application.DisplayAlerts = False
    
11.             Sh(2).Delete
    
12.             Application.DisplayAlerts = True
    
13.             Exit For
    
14.         End If
    
15.     Next
    
16.     With Sheets.Add(, Sheets(Sheets.Count))
    
17.         .Name = Sh(1).Name & "¶×¥X"
    
18.         Set Sh(2) = ActiveSheet
    
19.     End With
    
20.     Sh(1).Select
    
21.     Sh(1).Range("A1:AM15").Copy
    
22.     MyCopy Sh(2).Range("A1")
    
23.     With Sh(1)
    
24.         xCol = .VPageBreaks(1).Location.Column - 1
    
25.         For i = 0 To .HPageBreaks.Count
    
26.             If i = 0 Then
    
27.                 Set Rng(1) = .Range("A16")
    
28.             Else
    
29.                 Set Rng(1) = .HPageBreaks(i).Location.Range("A16")
    
30.             End If
    
31.             If Rng(1).Cells(1, 6) <> "" Then
    
32.                 With Rng(1)
    
33.                     R = .Cells(1, 6).End(xlDown).Row - .Row
    
34.                     If R < 25 Then R = R + 1
    
35.                     Rng(1).Resize(R, xCol).Copy
    
36.                 End With
    
37.                 With Sh(2).Range("A" & Rows.Count).End(xlUp)(2)      ' (2)= .Offset(1) = .Cells(2)
    
38.                     If .Row < 16 Then        'A13:A15 ¬°¦X¨ÖÀx¦s®æ : .Offset(1)-> = A14
    
39.                         Set Rng(2) = .Parent.Range("A16")
    
40.                     Else
    
41.                         Set Rng(2) = .Cells
    
42.                     End If
    
43.                 End With
    
44.                 MyCopy Rng(2)
    
45.             Else
    
46.                 Exit For
    
47.             End If
    
48.         Next
    
49.     End With
    
50.     Application.ScreenUpdating = True
    
51.     MsgBox ("¶×¥X§¹¦¨")
    
52. End Sub
    
53. Sub MyCopy(Rng As Range)   'µ{¦¡(¶Ç»¼°Ñ¼Æ)  : ¬Û¦Pªºµ{¦¡½X¥i¥Î
    
54.     With Rng
    
55.         .PasteSpecial Paste:=xlPasteValues              '­È
    
56.         .PasteSpecial Paste:=xlPasteColumnWidths 'Äæ¼e
    
57.         .PasteSpecial Paste:=xlPasteFormats            '®æ¦¡
    
58.     End With
    
59. End Sub

½Æ»s¥N½X

simplehope

¦^´_ 8# GBKEE


·PÁÂG¤j¡A¨ü±Ð¤F~~¥i¥H¥Î¤F!

simplehope

¤§«e¼g¤F­ÓVBA¡A°µ¶×¥X¨ì·s¤u§@ªí¥i¥H¦¨³s³e¸ê®Æ¡A
­ì¥»AÄæ(No.ªºÄæ¦ì)µL¤½¦¡¡A¦ý¦]¬°­n¦Û°Ê¬y¤ô¸¹»Ý¨D¡A§âAÄæ(No.ªºÄæ¦ì)®M¤½¦¡°µ¬y¤ô¸¹¤§«á¡A
¦p¤U¹Ï¥Ü

¤U¸ü (40.65 KB)

2016-10-8 18:52

¦A¶×¥X«á´N¦³¸ê®Æ¤£³s³e°ÝÃD
¦p¤U¹Ï¥Ü

¤U¸ü (58.55 KB)
err2

2016-10-8 18:52

¤p§Ì¾q¶w¹ê¦b¸Õ¤£¥X¤èªk¸Ñ¨M¡A¥u¦n¤S¨Ó½Ð¤j¤jÀ°À°¦£¤F~~ÁÂÁÂ
WB2.zip (790.07 KB)

¤U¸ü¦¸¼Æ: 4

2016-10-8 18:51

GBKEE

¦^´_ 7# simplehope

5# ªºµ{¦¡½X¤w­×¥¿,½Ð¦A¸Õ¸Õ

'** ¨úA1¨ì ²Ä¤@­Ó««ª½¤À­¶½uªº³Ì¥kÃ䪺Äæ¦ì)¦A¦V¤U¨ì¤u§@­¶ªº ³Ì«á¤@­ÓÀx¦s®æªº¦C¸¹******
Range(Rng, .Range("A" & .Cells.SpecialCells(xlCellTypeLastCell).Row)).Resize(, xCol).Delete xlUp
'AJÄæ­ì¥»¤½¦¡=IF(AT14="","",$AT$74) , §ï¤½¦¡ =Á`­¶¼Æ
'SpecialCells : expression.SpecialCells(Type, Value)
'SpecialCells ¤èªk    ¶Ç¦^ Range ª«¥ó¡A¦¹ª«¥ó¥Nªí»P«ü©w«¬ºA¤Î­È¬Û²Å¦Xªº©Ò¦³Àx¦s®æ¡CRange ª«¥ó
'Type     ¥²¿ïªº XlCellType¡C­n¥]§tªºÀx¦s®æ¡C
'xlCellTypeLastCell¡C¤w¥Î½d³òªº³Ì«á¤@­ÓÀx¦s®æ=>¤u§@­¶ªº ³Ì«á¤@­ÓÀx¦s®æ

ÁÙ¦³³o¥y¬O
Sh.Names.Add Name:="Á`­¶¼Æ", RefersToR1C1:=Sh.HPageBreaks.Count + 1
**·s¼W¤@­Ó¦WºÙ½d³ò¥s "Á`­¶¼Æ"¡A¨úR1C1®æ¦¡¡A¬Ý¦³´X­ÓHPageBreak ¦A+ 1 **
¨ºAJÄæ­n«ç»ò³s¨ì"Á`­¶¼Æ"©O?
**** ¤£¬O¦³½Ð§A ¦bAJÄæ­ì¥»¤½¦¡=IF(AT14="","",$AT$74) , §ï¤½¦¡ =Á`­¶¼Æ****

simplehope

¦^´_ 5# GBKEE

G¤j¤£¦n·N«ä¡A°õ¦æ¨ì¤@¥b·|¥X²{¿ù»~¡G"°}¦C¯Á¤Þ¶W¥X½d³ò"¦p¤U¹Ï¡G
   

¤U¸ü (125.67 KB)

2016-10-8 09:20

¤U¸ü (39.64 KB)
error2

2016-10-8 09:23

²q°ÝÃD¥X¦b xCol = .VPageBreaks(1).Location.Column  ¦ý¤£·|§ï....

¹ï¥H¤U³o¥y¤]¤£À´¡G
Range(Rng, .Range("A" & .Cells.SpecialCells(xlCellTypeLastCell).Row)).Resize(, xCol).Delete xlUp
'AJÄæ­ì¥»¤½¦¡=IF(AT14="","",$AT$74) , §ï¤½¦¡ =Á`­¶¼Æ

ÁÙ¦³³o¥y¬O
Sh.Names.Add Name:="Á`­¶¼Æ", RefersToR1C1:=Sh.HPageBreaks.Count + 1
·N«ä¬O·s¼W¤@­Ó¦WºÙ½d³ò¥s "Á`­¶¼Æ"¡A¨úR1C1®æ¦¡¡A¬Ý¦³´X­ÓHPageBreak ¦A+ 1 ¶Ü?
¨ºAJÄæ­n«ç»ò³s¨ì"Á`­¶¼Æ"©O?
¸£³U¦³ÂIÂण¹L¨Ó

simplehope

ÁÂÁÂG¤j¡A³o°÷¤p§Ì¬ã¨s¦n¤@°}¤l¤F¡G)
«Ü°ª¯Åªº¼gªk¡K¤p§Ìªº¤g¬¶¹ê¦bºF·\µLªk¬Û¤ñ


¸òwith ¡E¡E¡Eend with
¸òfor each...in
¸òif not ±ø¥ó¦¡ ¯uªº¬O«Ü¤£¼ô°Ú¡K