返回列表 發帖
wingchan19f

Rank: 1
1# 跳轉到 » 倒序看帖
打印
tT
發表於 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
收藏 分享

0

wingchan19f

Rank: 1
2#
發表於 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]

TOP

seasky

3#
發表於 2018-3-28 09:37 | 只看該作者
提示: 作者被禁止或刪除 內容自動屏蔽

TOP

hkce

Rank: 3Rank: 3Rank: 3
4#
發表於 2018-3-28 17:28 | 只看該作者
全部都係hardcode 左26行output... 一大堆redundant code...要改就繼續hardcode第27行output或重新寫過啦。

TOP

返回列表