这里是我的代码,我试图,其中工程BTW在PC上,而不是在Mac上,运行此代码,并且创建,命名Excel工作表,添加标签,改变所述足的颜色,更改名称所述片,然后转置数据,同时保持细胞和细胞的宽度和高度的格式,以新的工作表。
这工作,在PC上....但是当我到Mac上,它没有。
我走进引用,这是我看到的。
我见参考文献编辑控件,以及Microsoft脚本运行时失踪。 我都停用了,和脚本在这里给我一个错误还是:
wbBK2.SaveAs Dir & Application.PathSeparator & "Open Order Report -" & Format(Date, "mm-dd-yyyy") & ".xlsx"
错误发生在(Date, "mm-dd-yyyy")
具体的日期部分。 我想不通为什么会发生诚实。 如果有人可以仔细阅读这一点,并给我一个答案,这是极大的赞赏的解决方案。
我得到的错误是一个Error '9 Subscript Out Of Range
我看不出一个原因这个错误只会显示在Mac上,而不是PC。
Option Explicit
Sub OpenOrderReportExport()
Dim wsJL As Worksheet 'Jobs List
Dim wsPOT As Worksheet 'PO Tracking
Dim wsTNO As Worksheet 'Tel-Nexx OOR
Dim wsDOO As Worksheet 'Dakota OOR
Dim wbBK1 As Workbook 'Open Order Report
Dim wbBK2 As Workbook 'New Workbook
Dim wsWS1 As Worksheet 'Sheet1
Dim wsWS2 As Worksheet 'Sheet2
Dim wsWS3 As Worksheet 'Sheet3
Dim wsWS4 As Worksheet 'Sheet4
Dim CurrentFile As String, NewFileType As String, NewFile As String, Dir As String, lastrow As Long
Set wsJL = Sheets("Jobs List") 'Jobs List
Set wsPOT = Sheets("PO Tracking") 'PO Tracking
Set wsTNO = Sheets("Tel-Nexx OOR") 'Tel-Nexx OOR
Set wsDOO = Sheets("Dakota OOR") 'Dakota OOR
Set wbBK1 = ThisWorkbook
Set wbBK2 = Workbooks.Add 'New Workbook
Set wsWS1 = wbBK2.Sheets("Sheet1") 'Sheet1
Set wsWS2 = wbBK2.Sheets("Sheet2") 'Sheet2
Set wsWS3 = wbBK2.Sheets("Sheet3") 'Sheet3
Application.ScreenUpdating = False ' Prevents screen refreshing.
CurrentFile = ThisWorkbook.FullName
NewFileType = "Excel Files 2007 (*.xlsx)"
Dir = ThisWorkbook.path & Application.PathSeparator & "Reports"
wbBK2.SaveAs Dir & Application.PathSeparator & "Open Order Report -" & Format(Date, "mm-dd-yyyy") & ".xlsx"
Sheets.Add After:=Sheets(Sheets.Count)
Set wsWS4 = wbBK2.Sheets("Sheet4") 'Sheet4
With wbBK2
Dim Sht As Worksheet
For Each Sht In Worksheets
Sht.Tab.Color = 255
Next
End With
Sheets("Sheet1").Name = "Jobs List"
Sheets("Sheet2").Name = "PO Tracking"
Sheets("Sheet3").Name = "Dakota OOR"
Sheets("Sheet4").Name = "Tel-Nexx OOR"
With wbBK1
'Jobs List Export
lastrow = wsJL.Range("B" & Rows.Count).End(xlUp).Row
wsJL.Range("A2:N2").Copy
wsWS1.Range("A1").PasteSpecial xlPasteAll
wsJL.Range("A3:N" & lastrow).Copy
wsWS1.Range("A2").PasteSpecial xlPasteValuesAndNumberFormats
wsWS1.Range("A2").PasteSpecial xlPasteColumnWidths
wsJL.Range("B3:N" & lastrow).Copy
wsWS1.Range("B2").PasteSpecial xlPasteFormats
wsWS1.Columns("A").Delete
'Tel-Nexx Export
lastrow = wsTNO.Range("B" & Rows.Count).End(xlUp).Row
wsTNO.Range("A2:Q2").Copy
wsWS2.Range("A1").PasteSpecial xlPasteAll
wsTNO.Range("A3:Q" & lastrow).Copy
wsWS2.Range("A2").PasteSpecial xlPasteValuesAndNumberFormats
wsWS2.Range("A2").PasteSpecial xlPasteColumnWidths
wsTNO.Range("B3:Q" & lastrow).Copy
wsWS2.Range("B2").PasteSpecial xlPasteFormats
wsWS2.Columns("A").Delete
'Dakota Export
lastrow = wsDOO.Range("B" & Rows.Count).End(xlUp).Row
wsDOO.Range("A2:O2").Copy
wsWS3.Range("A1").PasteSpecial xlPasteAll
wsDOO.Range("A3:O" & lastrow).Copy
wsWS3.Range("A2").PasteSpecial xlPasteValuesAndNumberFormats
wsWS3.Range("A2").PasteSpecial xlPasteColumnWidths
wsDOO.Range("B3:O" & lastrow).Copy
wsWS3.Range("B2").PasteSpecial xlPasteFormats
wsWS3.Columns("A").Delete
'PO Tracking Export
lastrow = wsPOT.Range("B" & Rows.Count).End(xlUp).Row
wsPOT.Range("A2:K2").Copy
wsWS4.Range("A1").PasteSpecial xlPasteAll
wsPOT.Range("A3:K" & lastrow).Copy
wsWS4.Range("A2").PasteSpecial xlPasteValuesAndNumberFormats
wsWS4.Range("A2").PasteSpecial xlPasteColumnWidths
wsPOT.Range("B3:K" & lastrow).Copy
wsWS4.Range("B2").PasteSpecial xlPasteFormats
wsWS4.Columns("A").Delete
End With
With wsWS1
.Activate
.Range("A1").Select
End With
End Sub