我正在使用的代碼獲取我當前的作業簿資料，并為特定列（AF 列）中的每個唯一專案創建一個新作業簿，并為該唯一值提取所有行資料。我還需要它做的是根據不同的列（列 AG）重命名新作業簿表，但無法使其正常作業。我可以讓它根據位于 AF 中的唯一值重命名作業表，因為我將此串列存盤為物件變體，但無法弄清楚如何正確重命名作業表。
重命名最初設定為 xNSht.Name = ky。
我嘗試了以下方法：
xNSht.Name = xSht.Cells(i, xSName).Value
使用上述內容，我嘗試將 xSName 添加到我的 dic 物件并嘗試將其添加到 Dim I row as a Long
Dim xSName As Long 'This creates the new sheet with a value from column AG but it is not from the correct row that is being copied over (ky)
'OR tried to set it as it's own line, which just errors out.
Dim i As Long, xCName As Long, xSName As Long
我還嘗試將其設定為 AG2 的新作業表范圍，但這只是錯誤并說 AG2 為空。如果我將它設定為原始作業表 (xSht)，它會為每個新作業表使用與原始作業表相同的 AG2 值，這是不正確的。
xNSht.Name = xNSht.Range("AG2").Value
問題：如何根據要復制的列 AG 中的值重命名新作業表？
完整代碼如下
Sub Invoice_Split()
Dim wbN As Workbook
Dim xSht As Worksheet, xNSht As Worksheet
Dim i As Long, xCName As Long
Dim dic As Object, ky As Variant, lnk As Variant
Dim xTitle As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Set xSht = ThisWorkbook.Sheets("Data")
Set dic = CreateObject("Scripting.Dictionary")
xCName = 32 'Change this number to the column number which you will create new sheets based on - currently set to AF
xTitle = "A:AG"
For i = 2 To xSht.Cells(Rows.Count, xCName).End(xlUp).Row
If xSht.Cells(i, xCName).Value <> "" Then dic(xSht.Cells(i, xCName).Value) = xSht.Cells(i, "A").Value
Next
For Each ky In dic.keys
ThisWorkbook.Sheets("CONTROL").Copy
Set wbN = ActiveWorkbook
xSht.Range(xTitle).AutoFilter xCName, ky
Set xNSht = Worksheets.Add(, wbN.Sheets(wbN.Sheets.Count))
xNSht.Name = xNSht.Range("AG2").Value
ActiveWindow.DisplayGridlines = False
xSht.AutoFilter.Range.EntireRow.Copy xNSht.Range("A1")
xNSht.Columns.AutoFit
'save workbook
wbN.SaveAs ThisWorkbook.Path & "\" & ky
wbN.Close False
Next
On Error Resume Next
With ActiveWorkbook
For Each lnk In .LinkSources(Type:=xlLinkTypeExcelLinks)
.BreakLink Name:=lnk, Type:=xlLinkTypeExcelLinks
Next
End With
On Error GoTo 0
xSht.AutoFilterMode = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
End Sub
uj5u.com熱心網友回復：
我認為您需要更改設定名稱的順序。設定的時候還沒有把值放進去，試試把這三行改成如下：
老的
xNSht.Name = xNSht.Range("AG2").Value
ActiveWindow.DisplayGridlines = False
xSht.AutoFilter.Range.EntireRow.Copy xNSht.Range("A1")
新的
xSht.AutoFilter.Range.EntireRow.Copy xNSht.Range("A1")
xNSht.Name = xNSht.Range("AG2").Value
ActiveWindow.DisplayGridlines = False
如果這不起作用，請嘗試使用stopvba 除錯工具并檢查您的各種變數（或者最好使用帶有即時視窗的除錯工具）
例子...
Debug.Print xNSht.Range("AG2").Value
Stop
xNSht.Name = xNSht.Range("AG2").Value
'did this work?
Debug.Print ky
xNsht.Name = ky
Stop
'did this work?
Debug.Print dic(ky)
xNSht.Name = dic(ky)
'did this work?
Stop
'etc...
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/372743.html
上一篇：初始化可變長度的串列（或陣列）