将整个列(基于特定的标头)从一个纸复制到另一张(按其他顺序)不起作用，无法在Excel 365上使用

Question

遇到了一个我不能独自克服的问题：

以下代码在我的Win 8.1运行Excel 2013 Pro(32位(上正常工作。它在Excel 365(也32位(的Win 10上失败了。

VBA控制台正在突出显示我要分配给列的名称(SRO，DESC，状态，项目Ect。(，blue和'private sub generate((in黄色。

Private Sub Generate()
    Application.EnableEvents = False
    Application.ScreenUpdating = False
        ActiveWorkbook.Sheets("Imported Data").Activate
'tis where You select what columns You want to copy and name them
            SRO = WorksheetFunction.Match("SroNum", Rows("1:1"), 0)
            Desc = WorksheetFunction.Match("Description", Rows("1:1"), 0)
            Status = WorksheetFunction.Match("Status", Rows("1:1"), 0)
            Project = WorksheetFunction.Match("srouf_platform", Rows("1:1"), 0)
            SROlead = WorksheetFunction.Match("Name", Rows("1:1"), 0)
            OpenDate = WorksheetFunction.Match("CreateDate", Rows("1:1"), 0)
            CloseDate = WorksheetFunction.Match("Close Date", Rows("1:1"), 0)
            TPT = WorksheetFunction.Match("SroTPTinDays", Rows("1:1"), 0)
            STATUSnew = WorksheetFunction.Match("srouf_intel_sro_status", Rows("1:1"), 0)
            WRKstat = WorksheetFunction.Match("Status Code", Rows("1:1"), 0)
            OpCode = WorksheetFunction.Match("OperationCode", Rows("1:1"), 0)
            Priority = WorksheetFunction.Match("Priority Code", Rows("1:1"), 0)
            OpPartner = WorksheetFunction.Match("OperationPartnerName", Rows("1:1"), 0)
            DUT = WorksheetFunction.Match("LineSerialNum", Rows("1:1"), 0)
            OpDesc = WorksheetFunction.Match("OperationDescription", Rows("1:1"), 0)
            OpStatus = WorksheetFunction.Match("OperationStatus", Rows("1:1"), 0)
            CreatedBy = WorksheetFunction.Match("CreatedBy", Rows("1:1"), 0)

'adding new sheet - CROPPED DATA
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Cropped Data"
'tis where You copy selected columns to CROPPED DATA in specific A-Q order
            Sheets("Imported Data").Columns(SRO).Copy Destination:=Sheets("Cropped Data").Range("A1")
            Sheets("Imported Data").Columns(Desc).Copy Destination:=Sheets("Cropped Data").Range("B1")
            Sheets("Imported Data").Columns(Status).Copy Destination:=Sheets("Cropped Data").Range("C1")
            Sheets("Imported Data").Columns(Project).Copy Destination:=Sheets("Cropped Data").Range("D1")
            Sheets("Imported Data").Columns(SROlead).Copy Destination:=Sheets("Cropped Data").Range("E1")
            Sheets("Imported Data").Columns(OpenDate).Copy Destination:=Sheets("Cropped Data").Range("F1")
            Sheets("Imported Data").Columns(CloseDate).Copy Destination:=Sheets("Cropped Data").Range("G1")
            Sheets("Imported Data").Columns(TPT).Copy Destination:=Sheets("Cropped Data").Range("H1")
            Sheets("Imported Data").Columns(STATUSnew).Copy Destination:=Sheets("Cropped Data").Range("I1")
            Sheets("Imported Data").Columns(WRKstat).Copy Destination:=Sheets("Cropped Data").Range("J1")
            Sheets("Imported Data").Columns(Priority).Copy Destination:=Sheets("Cropped Data").Range("K1")
            Sheets("Imported Data").Columns(CreatedBy).Copy Destination:=Sheets("Cropped Data").Range("L1")
            Sheets("Imported Data").Columns(OpPartner).Copy Destination:=Sheets("Cropped Data").Range("M1")
            Sheets("Imported Data").Columns(DUT).Copy Destination:=Sheets("Cropped Data").Range("N1")
            Sheets("Imported Data").Columns(OpCode).Copy Destination:=Sheets("Cropped Data").Range("O1")
            Sheets("Imported Data").Columns(OpDesc).Copy Destination:=Sheets("Cropped Data").Range("P1")
            Sheets("Imported Data").Columns(OpStatus).Copy Destination:=Sheets("Cropped Data").Range("Q1")
End Sub

我遇到的错误是：

隐藏模块中的编译错误：生成。

还有其他选择吗？我应该如何改写这个，所以Excel 365可以工作吗？我需要此代码尽可能轻 - 正在处理大量数据。

任务立场：在表"导入数据"中查找特定的标头列，并将其复制到新的表"裁剪数据"，但按不同的顺序(而不是全部！(。

Answer 1

两件事引起了这个问题：

1. 在Excel 365" SRO"中是一个预定义的常数 - 因此我需要将其更改为SR0
2. Excel 365需要具有所有值(例如SR0，DESCPT，Stats等(，并以DIM(就像那样简单(预先定义：

Private Sub Generate2()
'warning! it's ZERO in all SR0, not O!
        Dim SR0 As Double
        Dim Descpt As Double
        Dim Stats As Double
        Dim Project As Double
        Dim SR0lead As Double
        Dim OpenDate As Double
        Dim CloseDate As Double
        Dim TPT As Double
        Dim STATUSnew As Double
        Dim WRKstat As Double
        Dim OpCode As Double
        Dim Priority As Double
        Dim OpPartner As Double
        Dim DUT As Double
        Dim OpDesc As Double
        Dim OpStatus As Double
        Dim CreatedBy As Double
            Application.EnableEvents = False
            Application.ScreenUpdating = False
                ActiveWorkbook.Sheets("Imported Data").Activate
'tis where You select what columns You want to copy and name them
            SR0 = Application.WorksheetFunction.Match("SroNum", Rows("1:1"), 0)
            Descpt = WorksheetFunction.Match("Description", Rows("1:1"), 0)
            Stats = WorksheetFunction.Match("Status", Rows("1:1"), 0)
            Project = WorksheetFunction.Match("srouf_platform", Rows("1:1"), 0)
            SR0lead = WorksheetFunction.Match("Name", Rows("1:1"), 0)
            OpenDate = WorksheetFunction.Match("CreateDate", Rows("1:1"), 0)
            CloseDate = WorksheetFunction.Match("Close Date", Rows("1:1"), 0)
            TPT = WorksheetFunction.Match("SroTPTinDays", Rows("1:1"), 0)
            STATUSnew = WorksheetFunction.Match("srouf_intel_sro_status", Rows("1:1"), 0)
            WRKstat = WorksheetFunction.Match("Status Code", Rows("1:1"), 0)
            OpCode = WorksheetFunction.Match("OperationCode", Rows("1:1"), 0)
            Priority = WorksheetFunction.Match("Priority Code", Rows("1:1"), 0)
            OpPartner = WorksheetFunction.Match("OperationPartnerName", Rows("1:1"), 0)
            DUT = WorksheetFunction.Match("LineSerialNum", Rows("1:1"), 0)
            OpDesc = WorksheetFunction.Match("OperationDescription", Rows("1:1"), 0)
            OpStatus = WorksheetFunction.Match("OperationStatus", Rows("1:1"), 0)
            CreatedBy = WorksheetFunction.Match("CreatedBy", Rows("1:1"), 0)
'adding new sheet - CROPPED DATA
           Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Cropped Data"
'tis where You copy selected columns to CROPPED DATA in specific A-Q order
            Sheets("Imported Data").Columns(SR0).Copy Destination:=Sheets("Cropped Data").Range("A1")
            Sheets("Imported Data").Columns(Descpt).Copy Destination:=Sheets("Cropped Data").Range("B1")
            Sheets("Imported Data").Columns(Stats).Copy Destination:=Sheets("Cropped Data").Range("C1")
            Sheets("Imported Data").Columns(Project).Copy Destination:=Sheets("Cropped Data").Range("D1")
            Sheets("Imported Data").Columns(SR0lead).Copy Destination:=Sheets("Cropped Data").Range("E1")
            Sheets("Imported Data").Columns(OpenDate).Copy Destination:=Sheets("Cropped Data").Range("F1")
            Sheets("Imported Data").Columns(CloseDate).Copy Destination:=Sheets("Cropped Data").Range("G1")
            Sheets("Imported Data").Columns(TPT).Copy Destination:=Sheets("Cropped Data").Range("H1")
            Sheets("Imported Data").Columns(STATUSnew).Copy Destination:=Sheets("Cropped Data").Range("I1")
            Sheets("Imported Data").Columns(WRKstat).Copy Destination:=Sheets("Cropped Data").Range("J1")
            Sheets("Imported Data").Columns(Priority).Copy Destination:=Sheets("Cropped Data").Range("K1")
            Sheets("Imported Data").Columns(CreatedBy).Copy Destination:=Sheets("Cropped Data").Range("L1")
            Sheets("Imported Data").Columns(OpPartner).Copy Destination:=Sheets("Cropped Data").Range("M1")
            Sheets("Imported Data").Columns(DUT).Copy Destination:=Sheets("Cropped Data").Range("N1")
            Sheets("Imported Data").Columns(OpCode).Copy Destination:=Sheets("Cropped Data").Range("O1")
            Sheets("Imported Data").Columns(OpDesc).Copy Destination:=Sheets("Cropped Data").Range("P1")
            Sheets("Imported Data").Columns(OpStatus).Copy Destination:=Sheets("Cropped Data").Range("Q1")

希望这对其他人有帮助：(