VBA打开Excel文件并将工作表1数据粘贴到“RRimport”当前工作簿中的工作表

问题描述

好的，所以我有一个目前的工作簿（原始工作簿）和几张表。我想打开一个现有的工作簿（数据工作簿）并复制数据工作簿第1页的所有内容，然后将所有内容粘贴到原始工作簿的RRimport表格中。在这个过程的最后，我想关闭数据工作簿到目前为止，我有以下代码，但是它正在我的原始工作簿的工作表名称ARGimport之后粘贴新工作表 ：

Ok so I have a current workbook (Original Workbook) with several Sheets. I would like to open an existing workbook (Data Workbook) and copy all of the contents in Sheet 1 of 'Data Workbook', then paste everything into Sheet "RRimport" of 'Original Workbook'. At the end of this process I would like to close the 'Data Workbook' So far I have the following code, however it currently pastes a new sheet right after my sheet names "ARGimport" of my Original Workbook:

Sub ImportData()

Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = ActiveWorkbook

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.xls (*.xls),")

If FileToOpen = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub
Else
    Set wb2 = Workbooks.Open(Filename:=FileToOpen)

    For Each Sheet In wb2.Sheets
        If Sheet.Visible = True Then
            Sheet.Copy After:=wb1.Sheets("ARGimport")
        End If
    Next Sheet

End If

    wb2.Close

End Sub

感谢您的帮助的我能够弄清楚这一点。更新和工作代码如下：

Thanks to the help of rdhs I was able to figure this out. Updated and working code below:

Sub ImportData()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Sheet As Worksheet
Dim PasteStart As Range

Set wb1 = ActiveWorkbook
Set PasteStart = [RRimport!A1]

Sheets("RRimport").Select
    Cells.Select
    Selection.ClearContents

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.xls (*.xls),")

If FileToOpen = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub
Else
    Set wb2 = Workbooks.Open(Filename:=FileToOpen)

    For Each Sheet In wb2.Sheets
        With Sheet.UsedRange
            .Copy PasteStart
            Set PasteStart = PasteStart.Offset(.Rows.Count)
        End With
    Next Sheet

End If

    wb2.Close

End Sub

推荐答案

这样做你想要什么？

Sub ImportData()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Sheet As Worksheet
Dim PasteStart As Range

Set wb1 = ActiveWorkbook
Set PasteStart = [RRimport!A1]

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.xls (*.xls),")

If FileToOpen = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub
Else
    Set wb2 = Workbooks.Open(Filename:=FileToOpen)

    For Each Sheet In wb2.Sheets
        With Sheet.UsedRange
            .Copy PasteStart
            Set PasteStart = PasteStart.Offset(.Rows.Count)
        End With
    Next Sheet

End If

    wb2.Close

End Sub

这篇关于VBA打开Excel文件并将工作表1数据粘贴到“RRimport”当前工作簿中的工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！