|
[size=13.913043975830078px]重新求助:求高手帮助当前工作表另存为工作簿
[size=13.913043975830078px]一、勾选B2点击“保存”按钮,当前工作表另存为新的工作簿,条件:
[size=13.913043975830078px]1、新工作簿名称为A2+B2单元格内容,中间最好添加“:或--”连接符号,如:【考核:单位1 】或【考核--单位1】;
[size=13.913043975830078px]2、新工作簿工作表不带公式,数据为文本格式,并去除按钮。
[size=13.913043975830078px]3、新工作簿保存在当前文件夹下。
[size=13.913043975830078px]二、B2下拉菜单,数量较多,希望通过点击“全部保存”按钮,B2下拉菜单中的所有单位全部另存为新工作簿,新工作簿的要求与上面相同。
[size=13.913043975830078px]原求助时考虑不周样本中没带宏及公式,所以在样本中测试成功,但正式操作结果带公式且出现乱码,再求高手出手帮助,谢谢
[size=13.913043975830078px]详见附件及截屏
- Sub suaa()
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- pa = ThisWorkbook.Path & ""
- fnam = [A2] & "-" & [B2]
- If Dir(pa & fnam & ".xls*") <> "" Then
- x = MsgBox("此文件已存在,是否删除?", 4)
- If x = 7 Then Exit Sub
- Kill pa & fnam & ".xls*"
- End If
- With Workbooks.Add
- Cells.Copy .Sheets(1).Cells
- .Sheets(1).Cells.PasteSpecial Paste:=xlPasteValues
- For Each sp In .Sheets(1).Shapes
- sp.Delete
- Next
- .SaveAs pa & fnam
- .Close
- End With
- Application.DisplayAlerts = True
- End Sub
- Sub suab()
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- pa = ThisWorkbook.Path & ""
- dw = Sheet1.Range("考核单位")
- For i = 1 To UBound(dw)
- [B2] = dw(i, 1)
- fnam = [A2] & "-" & [B2]
- If Dir(pa & fnam & ".xls*") <> "" Then Kill pa & fnam & ".xls*"
- With Workbooks.Add
- Cells.Copy .Sheets(1).Cells
- .Sheets(1).Cells.PasteSpecial Paste:=xlPasteValues
- For Each sp In .Sheets(1).Shapes
- sp.Delete
- Next
- .SaveAs pa & fnam
- .Close
- End With
- Next
- Application.DisplayAlerts = True
- End Sub
复制代码
|
|