目前我有以下代碼：
Sub EXCELS()
'Create excel files
Dim i As Integer
Dim name_file As String
For i = 5 To Sheets.Count
name_file = Sheets(i).Name
Worksheets(i).Copy
With ActiveWorkbook
.SaveAs Filename:="C:\Users\marya\OneDrive - Desktop\Cantina\listas" & "\" &
name_file & ".xlsx", FileFormat:=xlOpenXMLWorkbook
.Close SaveChanges:=False
End With
Next i
End Sub
我想通過作業表名稱更改和定義下面的代碼，如“Lista_AA”、“Lista_BB”。不是從作業表 5 創建 excel 檔案，而是創建包含名稱“Lista_AA”、“Lista_BB”的 excel 檔案...
For i = 5 To Sheets.Count
錯誤：
uj5u.com熱心網友回復：
目前尚不清楚我們如何知道應該將哪些作業表匯出到他們自己的作業簿中。此答案假定您要匯出名稱以“Lista_”開頭的每張作業表，以便匯出“Lista_AA”、“Lista_BB”而不是“Meal_Register”
Sub EXCELS()
'Create excel files
Dim i As Integer
Dim name_file As String
For i = 1 To Worksheets.Count
name_file = Sheets(i).Name
If Left(name_file, 6) = "Lista_" Then
Worksheets(i).Copy
With ActiveWorkbook
.SaveAs Filename:="C:\Users\marya\OneDrive - Desktop\Cantina\listas\" & _
name_file & ".xlsx", FileFormat:=xlOpenXMLWorkbook
.Close SaveChanges:=False
End With
End If
Next i
End Sub
如果作業表名稱有不同的模式，或者您想指定要匯出的作業表名稱串列，請在下面發表評論，我會修改或做出新的答案。
uj5u.com熱心網友回復：
這是一個版本，它將覆寫具有相同名稱的現有作業簿而不提示用戶
Sub EXCELS()
'Create excel files
Dim i As Integer
Dim name_file As String
Dim file_path as String
Application.DisplayAlerts = False
For i = 1 To Worksheets.Count
name_file = Sheets(i).Name
If Left(name_file, 6) = "Lista_" Then
Worksheets(i).Copy
file_path = "C:\Users\marya\OneDrive - Desktop\Cantina\listas\" & name_file & ".xlsx"
' try to delete a file that has the same name as the one we are about to save
on error resume next
kill file_path
on error goto 0
debug.print file_path
With ActiveWorkbook
.SaveAs Filename:=file_path, FileFormat:=xlOpenXMLWorkbook
.Close SaveChanges:=False
End With
End If
Next i
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/452979.html
上一篇：Excel-使用IF和OR
下一篇：將1添加到資料框中的所有數值