将数据从一个工作簿中的工作簿复制到另一个工作簿中

Question

在工作簿中，比如Main.xlsx，我有多个工作表，说Main418, Main418_NotBilled, Main923, Main923_NotBilled。

在另一个位置，我有两个工作簿说，Wokbook_418.xlsx和Wokbook_923.xlsx。在Wokbook_418.xlsx有三张Total, Sheet418, Sheet418_NotBilled。同样在Wokbook_923.xlsx有三张Total, Sheet923, Sheet923_NotBilled。

我的要求是有一个macro in Main.xlsx，它需要将两个工作簿"Wokbook_418.xlsx" and "Wokbook_923.xlsx"复制到名称为Wokbook_418_copy_26May.xlsx and Wokbook_923_copy_26May.xlsx respectively的另一个位置。

现在在“Wokbook_418_copy_26May.xlsx”， - 不想触摸总计工作表。 - 要将工作表“Sheet418”中的数据替换为工作表“Main418”中的数据。 （在Main.xlsx中可以使用“Main418”表格） - 想要将表格“Sheet418_NotBilled”中的数据替换为表格“Main418_NotBilled”中的数据。 （在Main.xlsx中提供“Main418_NotBilled”表）

而在“Wokbook_923_copy_26May.xlsx”， - 不想触摸总计工作表。 - 要将工作表“Sheet923”中的数据替换为工作表“Main923”中的数据。 （在Main.xlsx中提供“Main923”表格） - 想要将表格“Sheet923_NotBilled”中的数据替换为表格“Main923_NotBilled”中的数据。 （在Main.xlsx中提供“Main923_NotBilled”表单）

我在Excel中没有这个级别的专业知识。

我需要一些指导（而不是确切的代码）来实现此功能。

请帮忙。

Answer 1

从Main.xlsx工作簿中的宏中，可以打开每个工作簿，从Main.xlsx中的表单中复制信息，然后使用您的命名条件将编辑的工作簿保存到新位置。例如：

Sub Macro1() 

'opens the Workbook_418.xlsx file 
Workbooks.Open Filename:="your_file_path_here\Workbook_418.xlsx" 

'Activates your Main.xlsx workbook 
Windows("Main.xlsx").Activate 

'Selects your Main418 worksheet and copies the entire sheet 
    Sheets("Main418").Select 
    Cells.Select 
    Selection.Copy 

'Activates your Workbook_418.xlsx workbook 
Windows("Workbook_418.xlsx").Activate 

'Selects your Sheet418 worksheet and pastes the entire sheet 
    Sheets("Sheet418").Select 
    Cells.Select 
    ActiveSheet.Paste 

'Saves the Workbook_418.xlsx file with your new file name 
    'formats a string to return the day & month 
    Dim x As String 
x = Day(Date) & MonthName(Month(Date)) 

Application.ActiveWorkbook.SaveAs Filename:="your_file_path_here\Workbook_418_" & x & ".xlsx", _ 
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False 

End Sub 

Answer 2

使用@Dave在上述响应中提供的指导，我为所有工作表创建了它。我在这里发布的解决方案只是为了帮助其他人：

Sub Macro1() 


    Dim LCounter As Integer 

    For LCounter = 1 To 4 

     'BUNumber should be 417, 618, 641, 682 
      If LCounter = 1 Then 
       BUNumber = 417 
      ElseIf LCounter = 2 Then 
       BUNumber = 618 
      ElseIf LCounter = 3 Then 
       BUNumber = 641 
      ElseIf LCounter = 4 Then 
       BUNumber = 682 
      End If 

     ''''''''''''''''''''''''''''''''''''' File Operation Start''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
     'opens the Reference_BU417.xlsx file 
     Workbooks.Open Filename:="C:\Users\makumar\Desktop\file for mani\reference_files\Reference_BU" & BUNumber & ".xlsx" 

     'Activates your Main sheet.xlsm workbook 
     Windows("Main sheet.xlsm").Activate 

      'Selects your 417 worksheet and copies the entire sheet 
       Sheets("" & BUNumber & "").Select 
       Cells.Select 
       Selection.Copy 

     'Activates your Reference_BU417.xlsx workbook 
     Windows("Reference_BU" & BUNumber & ".xlsx").Activate 

      'Selects your BU417 worksheet and pastes the entire sheet 
       Sheets("BU" & BUNumber & "").Select 
       Cells.Select 
       ActiveSheet.Paste 




     'Activates your Main sheet.xlsm workbook 
     Windows("Main sheet.xlsm").Activate 

      'Selects your 417_NotBilled worksheet and copies the entire sheet 
       Sheets("" & BUNumber & "_NotBilled").Select 
       Cells.Select 
       Selection.Copy 

     'Activates your Reference_BU417.xlsx workbook 
     Windows("Reference_BU" & BUNumber & ".xlsx").Activate 

      'Selects your BU417_NotBilled worksheet and pastes the entire sheet 
       Sheets("BU" & BUNumber & "_NotBilled").Select 
       Cells.Select 
       ActiveSheet.Paste 




     'Saves the Reference_BU417.xlsx file with your new file name 
      With ActiveWorkbook 
       .SaveCopyAs "C:\Users\makumar\Desktop\file for mani\generated_files\" & _ 
          Left(.Name, InStrRev(.Name, ".") - 1) & _ 
          "_" & Format(Date, "ddmmmyyyy") & ".xlsx" 
      End With 


     'closes the Reference_BU417.xlsx file 
     Workbooks("Reference_BU" & BUNumber & ".xlsx").Close SaveChanges:=False 

     '''''''''''''''''''''''''''''''''''''File Operation End''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 


     MsgBox ("Complete for: " & BUNumber & "") 

    Next LCounter 


End Sub