Excel VBA copy content from one Sheet into other Workbook sheet

Question

I want to be able to select a workbook and then copy the content from that workbook (sheet 1) into my current active workbook where I run the macro. I've been looking at some answers here on StackOverflow to similar questions and got the following code (see below).

The selection of a file is currently working fine, but when I run the macro it throws an error

Runtime error "438": Object does not support that method or property`

(please note, that the error comes in my native language and is just translated by me)

Sadly no object is marked that he relates to, so I can't really make out what problem he has. Yet, I guess it is a problem with the PasteSpecial in the last line of function GetTemplateData, but that code should be alright (what is it supposed to do? Save the data into the first sheet of the give workbook activeWorkbook) and pass the reference back go GeneratedValues-routine.

Option Explicit

Private Sub GenerateValues()
    'Application.ScreenUpdating = False
    'Application.DisplayAlerts = False
    Dim activeWorkbook As Workbook
    Dim activeWorksheet As Worksheet

    Set activeWorkbook = Application.activeWorkbook
    Set activeWorksheet = GetTemplateData(activeWorkbook)

    activeWorkbook.Save
End Sub


'Get The Template Data
Private Function GetTemplateData(activeWorkbook As Workbook) As Worksheet

    Dim templateWorkbook As Workbook

    'Grab the Template Worksheet
    Set templateWorkbook = UseFileDialogOpen

    'Select all Content
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Copy

    'activeWorkbook.Sheets(activeWorkbook.Sheets.Count).Range("A1", Cells.End(xlDown) & Cells.End(xlRight)).PasteSpecial xlPasteValues
    activeWorkbook.Sheets(1).Range("A1", Cells.End(xlDown) & Cells.End(xlRight)).PasteSpecial xlPasteValues

End Function

'From https://learn.microsoft.com/de-de/office/vba/api/excel.application.filedialog
'Select the Workbook containing the Exported Template-Stories by User Selection
Function UseFileDialogOpen() As Workbook

    Dim lngCount As Long
    Dim filePath As String

    Dim templateBook As Workbook

    ' Open the file dialog
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        .Show

        Set templateBook = Workbooks.Open(.SelectedItems(1))

        ' Display paths of each file selected
        'For lngCount = 1 To .SelectedItems.Count
        '    MsgBox .SelectedItems(lngCount)
        'Next lngCount

    End With

    templateBook
End Function

*Sadly no object is marked that he relates to,* In any error, a line should be highlighted in yellow, where it fails. Where? Also, try to debug your code. Execute it step by step, using F8 instead of F5, that way you will see at what exact moment it fails. — Foxfire And Burns And Burns, Aug 14 '19 at 11:56
Also, you should avoid using proper names as variables, i.e. `activeworkbook` as that may not be the actual `ActiveWorkbook`. Secondly, this is going to cause problems `Cells.End(xlDown) & Cells.End(xlRight)`, You can look up how to find the last row and last column or `UsedRange`. — Darrell H, Aug 14 '19 at 12:14
Let me start by saying, code is well formatted which makes it easy to read. Apart from what @DarrellH and FABAB suggested, have a look at [why we should avoid using `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in VBA — Zac, Aug 14 '19 at 12:20

score 1 · Answer 1 · answered Aug 14 '19 at 13:15

I believe all of your problems originate here:

Private Sub GenerateValues()
    'Application.ScreenUpdating = False
    'Application.DisplayAlerts = False
    Dim activeWorkbook As Workbook
    Dim activeWorksheet As Worksheet

    Set activeWorkbook = Application.activeWorkbook
    Set activeWorksheet = GetTemplateData(activeWorkbook)

    activeWorkbook.Save
End Sub

ActiveWorkbook is a defined "variable" in VBA, so it is confused as to why you are trying to reassign it. Try using a different variable name instead.

Note: although ActiveWorksheet is not a defined variable in VBA, it is close in name to ActiveSheet, so I would also change that variable name to something different just so to not confuse you when writing future code.

score 0 · Answer 2 · answered Aug 22 '19 at 19:15

You could try something similar to this:

Sub CopyContentsFromOtherWorkbook()

    Dim wb As Workbook
    Dim twb As Workbook

    filePath = "C:\File.xlsx"

    Set wb = Workbooks.Open(filePath)
    wb.Sheets(1).Range("A1:Z10000").Copy

    Set twb = ThisWorkbook
    twb.Sheets(1).Range("C1").PasteSpecial xlPasteValues

    wb.Close
    twb.Save

End Sub

Excel VBA copy content from one Sheet into other Workbook sheet

2 Answers2