Board logo

標題: 請問如何修改以下程式碼可以輸出結果多過27行 (1) [打印本頁]
作者: wingchan19f    時間: 2018-3-27 16:45     標題: 請問如何修改以下程式碼可以輸出結果多過27行 (1)

[code]Sheets("Detailed Packing List").Select
    Range("X18").Select
    ActiveCell.FormulaR1C1 = "=RC[-23]&RC[-20]&RC[-19]"
    Range("X18").Select
    ActiveCell.FormulaR1C1 = _
        "=IF((RC[-23]&RC[-20]&RC[-19])="""","""",(RC[-23]&RC[-20]&RC[-19]))"
    Range("X18").Select
    Selection.Copy
    Range("X19:X256").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("X18:X256").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("X17").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Ref"
    With ActiveCell.Characters(Start:=1, Length:=3).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("Y17").Select
    ActiveCell.FormulaR1C1 = "Style"
    With ActiveCell.Characters(Start:=1, Length:=5).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("Z17").Select
    ActiveCell.FormulaR1C1 = "Col"
    With ActiveCell.Characters(Start:=1, Length:=3).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("Y18").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-21]="""",""Mising"",RC[-21])"
    Range("Y18").Select
    Selection.Copy
    Range("Y19:Y256").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("Y18:Y256").Select
    Selection.End(xlUp).Select
    Range("Y18").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="""","""",IF(RC[-21]="""",""Mising"",RC[-21]))"
    Range("Y18").Select
    Selection.Copy
    Range("Y19:Y256").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("Z18").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]="""","""",IF(RC[-21]="""",""Missing"",RC[-21]))"
    Range("Z18").Select
    Selection.Copy
    Range("Y18").Select
    Selection.End(xlDown).Select
    Range("Z19:Z256").Select
    Range("Z256").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("Y18:Z256").Select
    Range("Z256").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Selection.End(xlUp).Select
    Columns("Y:Y").Select
    Range("Y14").Activate
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight
    Range("Y17").Select
    ActiveCell.FormulaR1C1 = "Order"
    With ActiveCell.Characters(Start:=1, Length:=5).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("Y18").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="""","""",IF(RC[-24]="""",""Missing"",RC[-24]))"
    Range("Y18").Select
    Selection.Copy
    Range("Y19:Y256").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("Y18:Y256").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("F16:Q16").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AB16").Select
    ActiveSheet.Paste
    Range("AB18").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-22]*RC3"
    Range("AB18").Select
    Selection.Copy
    Range("AC18:AM18").Select
    ActiveSheet.Paste
    Range("AB18:AM18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("R19").Select
    Selection.End(xlDown).Select
    ActiveWindow.LargeScroll Down:=1
    Range("R52").Select
    ActiveWindow.LargeScroll Down:=1
    Range("R77").Select
    ActiveWindow.LargeScroll Down:=1
    Range("R102").Select
    ActiveWindow.LargeScroll Down:=1
    Range("R127").Select
    ActiveWindow.LargeScroll Down:=1
    Range("R152").Select
    ActiveWindow.LargeScroll Down:=1
    Range("R177").Select
    ActiveWindow.LargeScroll Down:=1
    Range("R202").Select
    ActiveWindow.LargeScroll Down:=1
    Range("R227").Select
    ActiveWindow.LargeScroll Down:=1
    Range("R252").Select
    ActiveWindow.LargeScroll Down:=1
    Range("R277").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AB19:AM256").Select
    Range("AB256").Activate
    ActiveSheet.Paste
    Range("AB18:AM256").Select
    Range("AB256").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("AB18:AM256").Select
    Range("AB256").Activate
    Application.CutCopyMode = False
    Selection.Find(What:="0", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
    Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False
    Selection.End(xlUp).Select
    Range("X18").Select
    Range("X17:X256").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "AO17"), Unique:=True
    Range("AO17:AO256").Select
    Selection.Sort Key1:=Range("AO18"), Order1:=xlDescending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("Y17:AA17").Select
    Selection.Copy
    Range("AP17").Select
    ActiveSheet.Paste
    Range("AP18").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],C[-18]:C[-17],2,FALSE))"
    Range("AP18").Select
    Selection.Copy
    Range("AP19:AP256").Select
    ActiveSheet.Paste
    Range("AP18:AP256").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("AQ18").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]="""","""",VLOOKUP(RC[-2],C[-19]:C[-17],3,FALSE))"
    Range("AQ18").Select
    Selection.Copy
    Range("AQ19:AQ256").Select
    ActiveSheet.Paste
    Range("AQ18:AQ256").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("AR18").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-3]="""","""",VLOOKUP(RC[-3],C[-20]:C[-17],4,FALSE))"
    Range("AR18").Select
    Selection.Copy
    Range("AR19:AR256").Select
    ActiveSheet.Paste
    Range("AR18:AR256").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("AB16:AM16").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AS16").Select
    ActiveSheet.Paste
    Range("AS18").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUMIF(R18C24:R256C24,RC41,R18C[-17]:R256C[-17])"
    Range("AS18").Select
    Selection.Copy
    Range("AS19:AS256").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("AS18:AS256").Select
    Selection.Copy
    Range("AS18").Select
    Selection.End(xlDown).Select
    Range("AT18:BD256").Select
    Range("AT256").Activate
    ActiveSheet.Paste
    Selection.End(xlUp).Select
    Range("AS18:BD256").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("BD18").Select
    Selection.End(xlToLeft).Select
    Columns("X:AM").Select
    Range("AM6").Activate
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("AC18:AN256").Select
    Selection.Find(What:="0", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
    Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False
    Range("AO18").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
    Range("AO18").Select
    Selection.Copy
    Range("AO19:AO256").Select
    ActiveSheet.Paste
    Range("AO18:AO256").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Find(What:="0", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
    Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False
    Range("AO16").Select
    ActiveCell.FormulaR1C1 = "Total"
    Range("Y14").Select
    ActiveCell.FormulaR1C1 = "Lookup Table For Summary GRN Sheet Only"
    Range("Y14").Select
    Selection.Font.Bold = True
    Range("Y14").Select
    ActiveCell.FormulaR1C1 = "Lookup Table For Summary GRN Sheet Only "
    Range("Y13").Select
    ActiveCell.FormulaR1C1 = "Do not Touch"
    Range("Y13").Select
    Selection.Font.Bold = True
    Range("Y13").Select
    ActiveCell.FormulaR1C1 = "! Do not Touch !"
    Range("Y256").Select
    Selection.Cut
    Range("Y13:AO256").Select
    Range("Y256").Activate
    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    Selection.Interior.ColorIndex = 16
    Range("Y255").Select
    Selection.End(xlUp).Select
    Range("Y13:AO256").Select
    Selection.Font.ColorIndex = 15
    Range("W5").Select
    Range("AP18").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("AP19").Select
    ActiveCell.FormulaR1C1 = "4"
    Range("AP20").Select
    ActiveCell.FormulaR1C1 = "7"
    Range("AP21").Select
    ActiveCell.FormulaR1C1 = "10"
    Range("AP22").Select
    ActiveCell.FormulaR1C1 = "13"
    Range("AP23").Select
    ActiveCell.FormulaR1C1 = "16"
    Range("AP24").Select
    ActiveCell.FormulaR1C1 = "19"
    Range("AP18:AP24").Select
    Selection.AutoFill Destination:=Range("AP18:AP256"), Type:=xlFillDefault
    Range("AP18:AP256").Select
    Range("AP256").Select
    Selection.End(xlUp).Select
    Range("AQ18").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("AR18").Select
    ActiveCell.FormulaR1C1 = "3"
    Range("AQ19").Select
    ActiveCell.FormulaR1C1 = "5"
    Range("AR19").Select
    ActiveCell.FormulaR1C1 = "6"
    Range("AQ20").Select
    ActiveCell.FormulaR1C1 = "8"
    Range("AR20").Select
    ActiveCell.FormulaR1C1 = "9"
    Range("AQ21").Select
    ActiveCell.FormulaR1C1 = "11"
    Range("AR21").Select
    ActiveCell.FormulaR1C1 = "12"
    Range("AQ22").Select
    ActiveCell.FormulaR1C1 = "14"
    Range("AR22").Select
    ActiveCell.FormulaR1C1 = "15"
    Range("AQ23").Select
    ActiveCell.FormulaR1C1 = "17"
    Range("AR23").Select
    ActiveCell.FormulaR1C1 = "18"
    Range("AQ24").Select
    ActiveCell.FormulaR1C1 = "20"
    Range("AR24").Select
    ActiveCell.FormulaR1C1 = "21"
    Range("AQ18:AR24").Select
    Range("AR18").Activate
    Selection.AutoFill Destination:=Range("AQ18:AR256")
    Range("AQ18:AR256").Select
    Range("AQ20").Select
    Selection.End(xlDown).Select
    Selection.End(xlUp).Select
    Range("AQ18:AQ256").Select
    Selection.Cut
    Range("AP18").Select
    Selection.End(xlDown).Select
    Range("AP257").Select
    ActiveSheet.Paste
    Range("AP256").Select
    Selection.End(xlUp).Select
    Range("AR18:AR256").Select
    Selection.Cut
    Range("AP18").Select
    Selection.End(xlDown).Select
    Range("AP496").Select
    ActiveSheet.Paste
    Range("AP495").Select
    Selection.End(xlUp).Select
    Range("AP17").Select
    ActiveCell.FormulaR1C1 = "sort"
    With ActiveCell.Characters(Start:=1, Length:=4).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("Y17:AP734").Select
    Selection.Sort Key1:=Range("AP18"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("AA19").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA43").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA68").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA93").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA118").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA143").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA168").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA193").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA218").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA243").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA268").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA293").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA318").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA343").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA368").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA393").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA418").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA443").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA468").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA493").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA518").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA543").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA568").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA593").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA618").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA643").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA668").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA693").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA719").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA748").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA777").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA806").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA835").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA864").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA893").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA922").Select
    ActiveWindow.LargeScroll Down:=1
    Range("AA951").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AA922").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AP893").Select
    Selection.End(xlUp).Select
    Range("AO734").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AO707").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AO681").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AO656").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AO631").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AO606").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AO581").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AO556").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AO531").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AO506").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AO481").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AO456").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AO431").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AO406").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AO381").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AO356").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AO331").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("AO306").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("Y257:AO257").Select
    Range("AO257").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("AP258").Select
    Selection.End(xlDown).Select
    Range("Y13:AO734").Select
    Range("AO734").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
作者: wingchan19f    時間: 2018-3-27 16:45

Range("AP731").Select
    Selection.End(xlUp).Select
    Range("AP17:AP734").Select
    Selection.ClearContents
    Range("AC15").Select
    Sheets("Summary GRN").Select
    Range("B21").Select
    ActiveCell.FormulaR1C1 = "='Detailed Packing List'!R[-3]C[24]"
    Range("B21").Select
    Selection.Copy
    Range("C21:P21").Select
    ActiveSheet.Paste
    Range("B21:P21").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B24").Select
    ActiveSheet.Paste
    Range("B27").Select
    ActiveSheet.Paste
    Range("B30").Select
    ActiveSheet.Paste
    Range("B33").Select
    ActiveSheet.Paste
    Range("B36").Select
    ActiveSheet.Paste
    Range("B39").Select
    ActiveSheet.Paste
    Range("B42").Select
    ActiveSheet.Paste
    Range("B45").Select
    ActiveSheet.Paste
    Range("B48").Select
    ActiveSheet.Paste
    Range("B51").Select
    ActiveSheet.Paste
    Range("B54").Select
    ActiveSheet.Paste
    Range("B57").Select
    ActiveSheet.Paste
    Range("B60").Select
    ActiveSheet.Paste
    Range("B63").Select
    ActiveSheet.Paste
    Range("B66").Select
    ActiveSheet.Paste
    Range("B69").Select
    ActiveSheet.Paste
    Range("B72").Select
    ActiveSheet.Paste
    Range("B75").Select
    ActiveSheet.Paste
    Range("B78").Select
    ActiveSheet.Paste
    Range("B81").Select
    ActiveSheet.Paste
    Range("B84").Select
    ActiveSheet.Paste
    Range("B87").Select
    ActiveSheet.Paste
    Range("B90").Select
    ActiveSheet.Paste
    Range("B93").Select
    ActiveSheet.Paste
    Range("B96").Select
    ActiveSheet.Paste
    Range("B99").Select
    ActiveSheet.Paste
    Range("A1").Select
    Sheets("Detailed Packing List").Select
    Columns("Y:AO").Select
    Range("Y5").Activate
    Application.CutCopyMode = False
    Selection.Locked = True
    Selection.FormulaHidden = False
    Selection.EntireColumn.Hidden = True
    Range("A1").Select
    Sheets("Summary GRN").Select
    Range("B21:P21").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B24:P24").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B27:P27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B30:P30").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B33:P33").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B36:P36").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B39:P39").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B42:P42").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B45:P45").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B48:P48").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B51:P51").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B54:P54").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B57:P57").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B60:P60").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B63:P63").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B66:P66").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B69:P69").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B72:P72").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B75:P75").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B78:P78").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B81:P81").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B84:P84").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B87:P87").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B90:P90").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B93:P93").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B96:P96").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B99:P99").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select
    Range("B21:P21").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B24:P24").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B27:P27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B30:P30").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B33:P33").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B36:P36").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B39:P39").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B42:P42").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B45:P45").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B48:P48").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B51:P51").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B54:P54").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B57:P57").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B60:P60").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B63:P63").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B66:P66").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B69:P69").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B72:P72").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B75:P75").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B78:P78").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B81:P81").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B84:P84").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B87:P87").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B90:P90").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B93:P93").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B96:P96").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B99:P99").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        Application.CutCopyMode = False
    Selection.ClearContents
    Range("A4:B4").Select
End Sub[/code]
作者: seasky    時間: 2018-3-28 09:37

提示: 作者被禁止或刪除 內容自動屏蔽
作者: hkce    時間: 2018-3-28 17:28

全部都係hardcode 左26行output... 一大堆redundant code...要改就繼續hardcode第27行output或重新寫過啦。




歡迎光臨 電腦領域 HKEPC Hardware (https://h2.hkepc.com/forum/) Powered by Discuz! 7.2