excel - Excel VBA 在激活工作表以粘贴数据时遇到问题

Question

我在 VBA 代码中间激活工作簿时遇到问题：想法是打开新文件->用户为该文件输入新名称（mainWB）->重命名文件->从文件夹中选择其他 xlsx 文件-> 对于每个选定的文件：

• 打开文件
• 从第一张表中选择并复制所有数据
• 打开主WB
• 创建一个新工作表
• 复制粘贴数据
• 用 AF2.value 重命名工作表

关闭之前选择的所有文件。

到目前为止，我有：

Sub OpenSelectedFiles()

Dim fd As FileDialog
Dim SelectedFile As Integer
Dim FileName As String
Dim TempWB As Workbook
Dim MainWB As Workbook
Dim i As Integer
Dim oldName As String
Dim newName As String

oldName = ActiveWorkbook.FullName
newName = Application.InputBox(Prompt:="Nombre del Nuevo Archivo:")
ActiveWorkbook.SaveAs newName
newName=ActiveWorkbook.Name
Kill oldName

Set fd = Application.FileDialog(msoFileDialogFilePicker)

fd.InitialFileName = "Libraries\Documents"
fd.InitialView = msoFileDialogViewList
fd.AllowMultiSelect = True

SelectedFile = fd.Show
If SelectedFile = -1 Then
    For i = 1 To fd.SelectedItems.Count
        Set TempWB = Workbooks.Open(fd.SelectedItems(i))
        Call CopyFromOpenFile(TempWB)
    Next i
End If
Dim openWB As Workbook
Application.ScreenUpdating = False
For Each openWB In Application.Workbooks
    If Not (openWB Is Application.ActiveWorkbook) Then
        openWB.Close
    End If
Next
Application.ScreenUpdating = True
End Sub

Private Sub CopyFromOpenFile(src As Workbook)

Application.ScreenUpdating = False

    Cells.Select
    Selection.Copy

    Windows(newName).Activate 'Here Excel gives me an error, I cannot activate it to paste the data from the other files 

    Sheets.Add After:=ActiveSheet
    Cells.Select
    ActiveSheet.Paste
    ActiveSheet.Name = Range("AF2").Value

End Sub

End Sub

如果有人对如何解决它有任何想法，我将不胜感激。提前致谢！

标签： excelvba