VBA将来自不同工作簿的数据复制到特定的工作表中，然后将数据粘贴到当前工作簿中的特定表中。

I am very new to VBA and need some help with copy and paste with different workbook and loop sheet names.

我是VBA的新手，需要一些帮助来复制和粘贴不同的工作簿和循环表名称。

I need to write a Macro to copy data from 100 workbooks (with exactly same formatting and sheet names), need copy from two sheets named "Summary" and "DB", to my current workbook's different sheets. I have set up a Control_Panel Sheet in my current workbook with the name of the copy source files' name and I need to escape the ones are blank (Or delete the sheet that does not have the copy source file, that's even better)

我需要编写一个宏来从100个工作簿(具有完全相同的格式和表名)中复制数据，需要从两个名为“Summary”和“DB”的表中复制到当前工作簿的不同表中。我在当前工作簿中设置了一个名为Control_Panel的工作簿，其中包含了复制源文件的名称，我需要转义为空的文件(或者删除没有复制源文件的工作表，这样更好)

The Control_Panel Sheet Page looks like this:

Control_Panel页面如下所示:

Here is my Code:

这是我的代码:

   Option Explicit

   Sub CopyData()
   Dim Address As String
   Dim CopyAddress As String
   Dim CopyRef1 As String
   Dim CopyRef2 As String
   Dim PasteSheet As String
   Dim Sheet1 As String
   Dim Sheet2 As String
   Dim A As Worksheet
   Dim i As Integer


   For i = 8 To 107 Step 1


     If IsEmpty(Cells(i, "D").Value) = False Then

     Sheet1 = "Summary"
     Sheet2 = "DB"

     Address = Cells(i, "D").Value
     PasteSheet = Cells(i, "B").Value


      CopyRef1 = "'" & Cells(4, "C") & "[& Address &]" & Sheet1 & "'!'" &       Range("B6:DO20")
    A = Worksheets("Sheet" & PasteSheet).Activate
    A.Range("C6:DP20").PasteSpecial

      CopyRef2 = "'" & Cells(4, "C") & "[& Address &]" & Sheet2 & "'!'" & Range("B7:LK631").Copy
      ActiveWorkbook.Worksheets(PasteSheet).Range("B23:LK647").PasteSpecial


     End If
    Next i
   End Sub

I do not want to open the copy source file each time when I try to copy the data, and it seems that the code does not recognize the location of the source file. Also, I am not sure if "PasteSheet" name is recognized when I am trying to paste data to each of the sheet.

当我试图复制数据时，我不希望每次都打开这个拷贝源文件，而且似乎代码没有识别源文件的位置。另外，当我试图将数据粘贴到每个表中时，我不确定是否识别了“PasteSheet”名称。

Please help me to find a way to copy the data from two sheets, named "Summary" and "DB", from multiple workbooks without open them and paste the data to different sheet with the sheet name loops from Cell B8 to B107.

请帮助我找到一种方法，将来自多个工作簿的“Summary”和“DB”两个表的数据复制到不同的工作簿中，并将数据粘贴到不同的表中，从B8到B107的表单名称循环。

Thank you so much!

谢谢你这么多!

1 个解决方案

#1

It is very difficult to see what you are trying. Try to name your workbooks and worksheets like so:

很难看出你在尝试什么。试着给你的工作簿和工作表命名如下:

Dim wb as workbook, dim ws as worksheet,
Set wb = ThisWorkbook,
Set ws = wb.Worksheets("thenameofyourworksheet")

And then work with ws.cells(4, "C") etc. At least one error I can see in your code:

然后和ws一起工作。单元格(4，“C”)等。我在您的代码中至少可以看到一个错误:

Your both variables CopyRef1/2 contain the Address term, which is(should be) in fact a variable. So the placing of your " " is incorrect or insufficient.

您的两个变量CopyRef1/2包含地址项，实际上是(应该是)一个变量。所以你的“”的位置是不正确的或者是不够的。

Try ... & "[" & Address & "]"

试一试……与"["及地址及"]"

Pro Tip: Use flags! Do not name variables like Address or so, name it sAddress and flag your type.

专家提示:使用的旗帜!不要使用诸如地址之类的变量名，将其命名为sAddress并标记您的类型。

#1