再次,我试图将我的 excel 文件另存为多个 xlsx 文件,如下所示:
我想将其保存到多个 xlsx 文件中,每个文件将包含 1 行范围(“Ax:Dx”)。我已经编写了如下代码来做到这一点
Sub split_file()
Dim wb As Workbook
Dim ws As Worksheet
Dim i As Long
Dim lastrow1 As Long
Dim lastrow2 As Long
Set wb = ThisWorkbook
On Error Resume Next
'Dim filename As String
Path = "C:\test"
For i = 2 To Sheet2.Range("A" & Rows.Count).End(xlUp).Row
'wb.Worksheets(3).Copy 'create new active workbook
Sheets.Add.Name = Sheet2.Range("A" & i).Value
With Worksheets(Sheet2.Range("A" & i).Value)
Range("A1:D1").Value = Sheet2.Range("A" & i & ":D" & i).Value
.SaveAs filename:=Path & "\" & Sheet2.Range("A" & i).Value, FileFormat:=xlOpenXMLWorkbook, Password:="Welcome1"
.Close savechanges:=False
End With
Next i
On Error GoTo 0
End Sub
我得到的结果如下图:
使用正确的数据另存为后打开文件后,它会在其中包含一些不需要的工作表:(。如下所示:
我希望它只保存为同名文件。有人可以帮忙寻找这个问题吗?非常感谢你们的每一个支持。
请尝试下一个方法:
Sub split_file()
Dim wb As Workbook, ws As Worksheet, wsNew As Worksheet, wbNew As Workbook
Dim path As String, i As Long
Set wb = ThisWorkbook
Set ws = wb.Sheets(Sheet2.Name)
path = "C:\test"
For i = 2 To ws.Range("A" & rows.count).End(xlUp).row
Set wbNew = Workbooks.Add(xlWBATWorksheet)
Set wsNew = wbNew.Sheets(1): wsNew.Name = ws.Range("A" & i).value
wsNew.Range("A1:D1").value = ws.Range("A" & i & ":D" & i).value
wbNew.saveas FileName:=path & "\" & wsNew.Name, FileFormat:=xlOpenXMLWorkbook, password:="Welcome1"
wbNew.Close savechanges:=False
Next i
End Sub
我以为这Sheet2
是一个代号...
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句