将活动工作簿中的Sheet1复制到活动工作簿工作表2的单元格中命名的工作簿中的工作表2

Question

我有一系列工作簿，我需要不断地将工作簿的Sheet1复制到新工作簿的Sheet2。工作簿的名称将在数字上前进(name_May2011_2、name_May2011_3、name_May2011_5)。末尾的数字将会改变，不一定是按顺序的。我有允许我在新工作表中列出活动工作簿的代码。我需要引用该工作表中的一个单元格作为工作簿的名称作为目标顶部副本。到目前为止，我的代码如下：

代码子Copy_Merge()

'Declare variables and data types
Dim Wb As Workbook
Dim Ws As Worksheet
Dim i As Single, j As Single


 
'Create a new worksheet and save to object ws
Set Ws = Sheets.Add
 
'Go through open workbooks
For j = 1 To Workbooks.Count
 
    'Save workbook name to cell A1 and downwards
    Range("A1").Cells(j, 1) = Workbooks(j).Name
     
    'Iterate through worksheets in given workbook
    For i = 1 To Workbooks(j).Sheets.Count
   
            'Save worksheet names to cell B1 and cells further right
            Range("A1").Cells(j, i + 1) = Workbooks(j).Sheets(i).Name
 
    'Continue with next worksheet
    Next i
 
'Continue with next workbook
Next j

‘这就是我在’我需要将活动工作簿的工作表2的单元格A2中的变量设置为字符串‘并将该字符串用作工作簿目标名称时遇到的问题

Dim SB As Workbook
Dim Ss As Worksheet

Set SB = ThisWorkbook
Set Ss = ThisWorkbook.Sheet("Sheet2")
Set MyToday = SB.Ss.Range("A2").Value 'name of destination workbook


Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Workbooks(MyToday).Sheets(3)
On Error Resume Next
ActiveSheet.Name = "Sheet2"
On Error GoTo 0

终止子/code

我已经在这上面工作了几天了，我对编写宏相对比较陌生，我已经无能为力了。有没有人能帮一下这个代码或者推荐一个更好的代码来使用？

Answer 1

为了从当前工作簿的"Sheet2“的单元格"A2”中检索目标工作簿的名称，并使用它将相应的工作簿分配给对象，您可以使用：

Dim SourceWorkbook As Workbook   ' Your SB
Dim SourceWorksheet As Worksheet ' Your Ss

Dim TargetWorkbook As Workbook
Dim TargetWorksheet As Worksheet
Dim TargetWorkbookName As String
Dim TargetSheetName As String

Set SourceWorkbook = ThisWorkbook
Set SourceWorksheet = SourceWorkbook.Sheets("Sheet2")           ' the "s" in "Sheets" is important to access the Workbook's Sheet-Collection

TargetWorkbookName = SourceWorksheet.Range("A2").Value          ' TargetWorkbookName is of type "String", thus "Set" is not allowed
Set TargetWorkbook = Workbooks(TargetWorkbookName)
TargetSheetName = SourceWorksheet.Range("B2").Value
Set TargetWorksheet = TargetWorkbook.Sheets(TargetSheetName)