3 回答
TA贡献1805条经验 获得超10个赞
尝试这样的事情(试图坚持自己的风格/方法)
'open new workbook
Set NewCaseFile = Workbooks.Add
'-------------------------------------------------
Dim strSheetNameAG As String
Dim strSheetNameER As String
'etc
Dim intLoop As Integer
For intLoop = 1 To 40
'set sheet names
strSheetNameAG = "HR gp " & i
strSheetNameER = "F&B gp " & i
'etc
'move them across
Windows("AG.xlsx").Sheets(strSheetNameAG).Move Before:=NewCaseFile.Sheets(1)
Windows("ER.xlsx").Sheets(strSheetNameAG).Move Before:=NewCaseFile.Sheets(1)
'etc
Next intLoop
'-------------------------------------------------
'Save the created file for Group1
ActiveWorkbook.SaveAs Filename:=sPath & "gp 1.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close False
TA贡献1799条经验 获得超8个赞
好吧,没有帕尔默小姐,我仍然会处于黑暗中(真的是黑色),但设法使其工作(下面的代码),但没有我展示的那么优雅……仍然要感谢她的帮助。
Sub Macro4()
'turn off screen
With Application
' .ScreenUpdating = False only removed while testing
' .EnableEvents = False
'.Calculation = xlCalculationManual disbled for the moment
End With
'get the path to desktop
Dim sPath As String
sPath = MacScript("(path to desktop folder as string)")
'give a name to new work book for macro use
Dim NewCaseFile As Workbook
'-------------------------------------------------
Dim strSheetNameAG As String
Dim strSheetNameER As String
Dim strSheetNameCS As String
Dim strSheetNameEV As String
Dim strSheetNameJD As String
Dim strSheetNamePG As String
'etc
'Dim intLoop As Integer
Dim i As Integer
For i = 1 To 40
'open new workbook
Set NewCaseFile = Workbooks.Add
'set sheet names
strSheetNameAG = "HR gp " & i
strSheetNameER = "F&B gp " & i
strSheetNameCS = "Acc gp " & i
strSheetNameEV = "Mkt gp " & i
strSheetNameJD = "Rdiv gp " & i
strSheetNamePG = "Fac gp " & i
'etc
'move them across
Windows("AG.xlsx").Activate
Sheets(strSheetNameAG).Select
Sheets(strSheetNameAG).Move Before:=NewCaseFile.Sheets(1)
Windows("ER.xlsx").Activate
Sheets(strSheetNameER).Select
Sheets(strSheetNameER).Move Before:=NewCaseFile.Sheets(1)
Windows("CS.xlsx").Activate
Sheets(strSheetNameCS).Select
Sheets(strSheetNameCS).Move Before:=NewCaseFile.Sheets(1)
Windows("EV.xlsx").Activate
Sheets(strSheetNameEV).Select
Sheets(strSheetNameEV).Move Before:=NewCaseFile.Sheets(1)
Windows("JD.xlsx").Activate
Sheets(strSheetNameJD).Select
Sheets(strSheetNameJD).Move Before:=NewCaseFile.Sheets(1)
Windows("PG.xlsx").Activate
Sheets(strSheetNamePG).Select
Sheets(strSheetNamePG).Move Before:=NewCaseFile.Sheets(1)
'etc
'Save the created file for Group in use
ActiveWorkbook.SaveAs Filename:=sPath & "gp " & i & ".xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close False
Next i
'-------------------------------------------------
'turn screen back on
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
TA贡献1757条经验 获得超8个赞
最后的建议包括(用工作簿代替Windows ...),下面的更新代码,经过测试且可以使用,非常感谢Mike
Sub Macro4()
'turn off screen
With Application
' .ScreenUpdating = False only removed while testing
' .EnableEvents = False
'.Calculation = xlCalculationManual disbled for the moment
End With
'get the path to desktop
Dim sPath As String
sPath = MacScript("(path to desktop folder as string)")
'give a name to new work book for macro use
Dim NewCaseFile As Workbook
'Create sheet names
Dim strSheetNameAG As String
Dim strSheetNameER As String
Dim strSheetNameCS As String
Dim strSheetNameEV As String
Dim strSheetNameJD As String
Dim strSheetNamePG As String
'Create loop counter variable
'Dim intLoop As Integer
Dim i As Integer
For i = 1 To 40
'open new workbook
Set NewCaseFile = Workbooks.Add
'set sheet names
strSheetNameAG = "HR gp " & i
strSheetNameER = "F&B gp " & i
strSheetNameCS = "Acc gp " & i
strSheetNameEV = "Mkt gp " & i
strSheetNameJD = "Rdiv gp " & i
strSheetNamePG = "Fac gp " & i
'move them across
Workbooks("AG.xlsx").Sheets(strSheetNameAG).Move Before:=NewCaseFile.Sheets(1)
Workbooks("ER.xlsx").Sheets(strSheetNameER).Move Before:=NewCaseFile.Sheets(1)
Workbooks("CS.xlsx").Sheets(strSheetNameCS).Move Before:=NewCaseFile.Sheets(1)
Workbooks("EV.xlsx").Sheets(strSheetNameEV).Move Before:=NewCaseFile.Sheets(1)
Workbooks("JD.xlsx").Sheets(strSheetNameJD).Move Before:=NewCaseFile.Sheets(1)
Workbooks("PG.xlsx").Sheets(strSheetNamePG).Move Before:=NewCaseFile.Sheets(1)
'Save the created file for Group in use
ActiveWorkbook.SaveAs Filename:=sPath & "gp " & i & ".xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close False
Next i
'-------------------------------------------------
'turn screen back on
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
- 3 回答
- 0 关注
- 419 浏览
相关问题推荐
添加回答
举报