宏将数据从一个工作簿复制到特定文件夹中的所有其他工作簿 [英] Macro to copy data from one workbook to all other workbooks in a specific folder

问题描述

我需要将主项目列表从一个工作簿复制到特定文件夹中的所有其他工作簿。我试过 - 看到我的宏下面。虽然宏没有给我任何错误，我也不能让它工作...有人可以帮助吗？

提前感谢！

$作为对象，MySource As Object，文件As Variant
file = Dir（C： \Users\New文件夹）
While（file<>）

工作簿（主项目列表（2）.xlsx）。列表）范围（A1：D34）。选择
Selection.Copy
Windows（文件）。激活
表格（主项目列表）。范围（A1） 。选择
Selection.PasteSpecial粘贴：= xlPasteColumnWidths，操作：= xlNone，_
SkipBlanks：= False，Transpose：= False
ActiveSheet.Paste
退出子
file = Dir
Wend
End Sub

解决方案

尝试这样一个：

  Sub Macro1（）
 Dim file As String 
 Dim myPath As String 
 Dim wb As Workbook 
 Dim rng As范围
 
 Dim wbMaster As Workbook 
'如果主工作簿已经打开
设置wbMaster =工作簿（主项目列表（2）.xlsx）
'如果主工作簿未打开
'设置wbMaster = Workbooks.Open（C：\Users\New folder\Master项目列表（2）.xlsx）
 
设置rng = wbMaster 。（主项目列表）。范围（A1：D34）
 
 myPath =C：\Users\New folder\注意： end 
 file = Dir（myPath& * .xls *）
 While（file<>）
 
设置wb = Workbooks.Open（myPath&文件）
 rng.Copy 
使用wb.Worksheets（主项目列表）。范围（A1）
 .PasteSpecial xlPasteColumnWidths 
 .PasteSpecial xlPasteAll 
结束
 
 wb。关闭SaveChanges：= True 
设置wb =没有
 
文件=目录
 Wend 
 
 Application.CutCopyMode = False 
 End Sub 
  

I need to copy a master project list from one workbook to all of the other workbooks in a specific folder. I have tried - see my macro below. While the macro does not give me any errors, I also cannot get it to work... Can someone please help?

Thanks in advance!!

Sub Macro1()
   Dim MyObj As Object, MySource As Object, file As Variant
   file = Dir("C:\Users\New folder")
   While (file <> "")

    Workbooks("Master Project list (2).xlsx").Sheets("Master Project list").Range("A1:D34").Select
    Selection.Copy
    Windows(file).Activate
    Sheets("Master Project list").Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Exit Sub
    file = Dir
    Wend
End Sub

解决方案

Try this one:

Sub Macro1()
   Dim file As String
   Dim myPath As String
   Dim wb As Workbook
   Dim rng As Range

   Dim wbMaster As Workbook
   'if master workbook already opened
   Set wbMaster = Workbooks("Master Project list (2).xlsx")
   'if master workbook is not opened
   'Set wbMaster = Workbooks.Open("C:\Users\New folder\Master Project list (2).xlsx")

   Set rng = wbMaster.Sheets("Master Project list").Range("A1:D34")

   myPath = "C:\Users\New folder\" ' note there is a back slash in the end
   file = Dir(myPath & "*.xls*")
   While (file <> "")

        Set wb = Workbooks.Open(myPath & file)
        rng.Copy
        With wb.Worksheets("Master Project list").Range("A1")
            .PasteSpecial xlPasteColumnWidths
            .PasteSpecial xlPasteAll
        End With

        wb.Close SaveChanges:=True
        Set wb = Nothing

        file = Dir
    Wend

    Application.CutCopyMode = False
End Sub

这篇关于宏将数据从一个工作簿复制到特定文件夹中的所有其他工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！