所以我有6个“主”文件，然后分成40个单独的文件 [英] So, I have 6 "master" files to then divide into 40 separate files

问题描述

我将简要介绍一下我想要的：
我有6个主文件，每个文件包含40个工作表如下：
AG工作簿有HR Gp 1到HR Gp 40，
ER工作簿中有FB Gp 1到Gp 40等。所有的表都已经平了。

我已经设法创建一个宏（使用Excel Mac 2011）一组（代码如下），但是我无法使其成功循环。

任何帮助排序循环将不胜感激
非常感谢，
Mike

  Sub Macro3（）
'
'Macro3宏
'关闭屏幕
应用程序
'.ScreenUpdating = False only删除同时测试
'.EnableEvents = False 
'.Calculation = xlCalculation手动暂停
结束
 
'获取桌面的路径
 Dim sPath As String 
 sPath = MacScript（（桌面文件夹作为字符串的路径））
 
'为宏使用新工作簿的名称
 Dim NewCaseFile As Workbook 
 
'打开新工作簿
设置NewCaseFile = Workbooks.Add 
 
'将组1的工作表移动到NewcaseFile：6个工作簿中的1个工作表... 
 Windows （AG.xlsx）激活
表格（HR gp 1）。选择
表格（HR gp 1）。之前：= NewCaseFile.Sheets（1）
 Windows（ER.xlsx）。激活
表格（F& B gp 1）。选择
表格（F& B，gp 1）。Move Before：= NewCaseFile.Sheets（1）
 Windows（CS.xlsx）。激活
表格（Acc gp 1）。选择
表格Acc gp 1）。Move Before：= NewCaseFile.Sheets（1）
 Windows（EV.xlsx）。激活
表格（Mkt gp 1）。选择
表格（Mkt gp 1）。之前：= NewCaseFile.Sheets（1）
 Windows（JD.xlsx）。激活
表单（Rdiv gp 1）。选择
 （Rdiv gp 1）。之前：= NewCaseFile.Sheets（1）
 Windows（PG.xlsx）。激活
表格（Fac gp 1）。选择
）（Fac gp 1）移动之前：= NewCaseFile.Sheets（1）
 
'保存Group1的创建文件
 ActiveWorkbook.SaveAs文件名：= sPath& gp 1.xlsx，FileFormat：= _ 
 xlOpenXMLWorkbook，CreateBackup：= False 
 ActiveWorkbook.Close False 
 
'返回
上的应用程序Application.ScreenUpdating = True 
 Application.DisplayAlerts = True 
 
 End Sub 
  

解决方案

尝试这样的东西（试图坚持你的风格/方法）

 工作簿
设置NewCaseFile = Workbooks.Add 
 
'------------------------------ ------------------- 
 Dim strSheetNameAG As String 
 Dim strSheetNameER As String 
'etc 
 
 Dim intLoop As Integer 
 
对于intLoop = 1至40 
 
'设置表名称
 strSheetNameAG =HR gp& i 
 strSheetNameER =F& B gp& i 
'etc 
 
'移动他们跨
 Windows（AG.xlsx）。表（strSheetNameAG）.Move之前：= NewCaseFile.Sheets（1）
 Windows（ER.xlsx）。Sheets（strSheetNameAG）.Move Before：= NewCaseFile.Sheets（1）
'etc 
 
下一个intLoop 
 
' ------------------------------------------------- 
'为Group1保存创建的文件
 ActiveWorkbook.SaveAs文件名：= sPath& gp 1.xlsx，FileFormat：= _ 
 xlOpenXMLWorkbook，CreateBackup：= False 
 ActiveWorkbook.Close False 
  

I will briefly describe what I would like: I have 6 "master" files each containing 40 worksheets as follows: AG workbook has HR Gp 1 to HR Gp 40, ER workbook has FB Gp 1 to Gp 40, etc. All sheets are "flat" already.

I have managed to create a macro (using Excel Mac 2011) which works for one group (code follows at the bottom), but I have not been able to make it "loop" successfully.

Any help to sort the loop would be appreciated Many thanks, Mike

Sub Macro3()
'
' Macro3 Macro
'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

'open new workbook
Set NewCaseFile = Workbooks.Add

'Move group 1's sheets to NewcaseFile : 1 sheet from 6 workbooks...
  Windows("AG.xlsx").Activate
    Sheets("HR gp 1").Select
    Sheets("HR gp 1").Move Before:=NewCaseFile.Sheets(1)
  Windows("ER.xlsx").Activate
    Sheets("F&B gp 1").Select
    Sheets("F&B gp 1").Move Before:=NewCaseFile.Sheets(1)
  Windows("CS.xlsx").Activate
    Sheets("Acc gp 1").Select
    Sheets("Acc gp 1").Move Before:=NewCaseFile.Sheets(1)
  Windows("EV.xlsx").Activate
    Sheets("Mkt gp 1").Select
    Sheets("Mkt gp 1").Move Before:=NewCaseFile.Sheets(1)
  Windows("JD.xlsx").Activate
    Sheets("Rdiv gp 1").Select
    Sheets("Rdiv gp 1").Move Before:=NewCaseFile.Sheets(1)
  Windows("PG.xlsx").Activate
    Sheets("Fac gp 1").Select
    Sheets("Fac gp 1").Move Before:=NewCaseFile.Sheets(1)

'Save the created file for Group1
 ActiveWorkbook.SaveAs Filename:=sPath & "gp 1.xlsx", FileFormat:= _
   xlOpenXMLWorkbook, CreateBackup:=False
   ActiveWorkbook.Close False

'turn screen back on
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

解决方案

Try something like this (tried to stick to your style/approach)

'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

这篇关于所以我有6个“主”文件，然后分成40个单独的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！