VBA - Copy-Paste to another workbook

Question

I am trying to copy specific cells from one workbook to another at the end of the table. I assume the problem is with using 'ActiveWorkbook' a lot. I get the error "Object doesnt support this property or method" and it seems that macro copies cells from CurrentBook and not uploader.

How can I fix my code?


Dim uploadfile As Variant
Dim uploader As Workbook
Dim CurrentBook As Workbook
Dim lastRow As Integer

Set CurrentBook = ActiveWorkbook

uploadfile = Application.GetOpenFilename()
    If uploadfile = "False" Then
        Exit Sub
    End If
Workbooks.Open uploadfile

Set uploader = ActiveWorkbook

With uploader
    Application.CutCopyMode = False
    Range("A1:J100").Copy
End With


CurrentBook.Activate
lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("A" & lastRow & ":J" & lastRow + 100).Select
Selection.Paste

uploader.Close

End Sub

This [post](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) might solve your problems. You need to declare and reference worksheets and workbooks. — Damian, May 30 '19 at 09:44
You can use ThisWorkbook to refer to the workbook that uses the VBA code. And if I recall correctly you can do `Set uploader = Workbooks.Open uploadfile` but I'm on my phone now so I can't test it. — Andreas, May 30 '19 at 09:55
*I get the error "Object doesnt support this property or method"* on what line? — Andreas, May 30 '19 at 09:58
@Andreas Set uploader = Workbooks.Open uploadfile sadly doesnt work Error "Expected end of statement" — Marysia, May 30 '19 at 10:02

score 0 · Answer 1 · answered May 30 '19 at 10:03

Here, and only because I'm bored at work, the are thousands of posts like this:

Option Explicit
Sub CopyPaste()

    Dim uploadfile As String 'not Variant, the filename will be a string
    Dim wbCopy As Workbook 'Better than uploader As Workbook you need to declare variables easy to read
    Dim wbPaste As Workbook 'same as above
    Dim LastRow As Lon 'integer doesn't work (it will be a long delimited to integer) either Byte(0-255) or Long

    Set wbPaste = ThisWorkbook 'the workbook containing the code

    uploadfile = Application.GetOpenFilename()
    If uploadfile = "False" Then
        Exit Sub
    End If

    Set wbCopy = Workbooks.Open(uploadfile) 'declare your paste workbook like this

    ' Set uploader = ActiveWorkbook this way of referencing a workbook might give you errors, use the above

    With wbCopy.Sheets("MySheetName") 'reference always the worksheets, change MySheetName for the actual sheet name
        'Application.CutCopyMode = False this is useless here it's emptying the clipboard but below you copy something new
        .Range("A1:J100").Copy 'you missed the . on the beginning (when using With you need to use the dot to reference the with)
    End With

    With wbPaste.Sheets("MySheetName") 'reference always the worksheets, change MySheetName for the actual sheet name
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Range("A" & LastRow).PasteSpecial xlPasteValues 'when you copy/paste you just need to find the first cell on the paste range, Excel will paste the whole range
    End With

    Application.CutCopyMode = False 'now you can use this to avoid warning message on closing the copy workbook
    wbCopy.Close Savechanges:=False

End Sub

Thank you for the code Damian but it copies the table from wbPaste(ThisWorkbook) — Marysia, May 30 '19 at 10:21
And I still get the error "Object doesnt support this property or method" — Marysia, May 30 '19 at 10:21
For the first comment @Marysia just switch the variables. As for the second, let me check something. For the second comment, the code works fine for me. The error comes on the `Set wbCopy = Workbooks.Open(uploadfile)` line? — Damian, May 30 '19 at 10:23

Hippolyte BRINGER · Accepted Answer · 2019-05-30T10:23:45.153

In VBA you must not use Ative.

So replace all the Active and you have to use PasteSpecial, it's better than Paste

Sub test()

Dim uploadfile As Variant
Dim uploader As Workbook
Dim CurrentBook As Workbook
Dim lastRow As Integer

Set CurrentBook = ThisWorkbook

uploadfile = Application.GetOpenFilename()
    If uploadfile = "False" Then
        Exit Sub
    End If
Set uploader = Workbooks.Open(uploadfile)

Application.CutCopyMode = False
uploader.Worksheets(1).Range("A1:J100").Copy

lastRow = CurrentBook.Worksheets(1).Cells(CurrentBook.Worksheets(1).Rows.Count, "A").End(xlUp).Row + 1
Range("A" & lastRow & ":J" & (lastRow + 100)).PasteSpecial xlPasteValues

uploader.Close

End Sub

This code works for me,

Your welcome :)

This code won't work `Cells(Rows.Count, "A") `since the active workbook is the copy workbook and not the paste workbook, will get the last row from the copy workbook. You didn't reference the `Rows.Count` — Damian, May 30 '19 at 10:22

score 0 · Answer 3 · answered May 30 '19 at 10:43

Below you could find a sample code on how to copy from one workbook to another:

Option Explicit

Sub test()

    Dim wbSource As Workbook, wbDestination As Workbook

    'Set both workbooks by name to avoid conflicts
    Set wbSource = Workbooks("Book1")
    Set wbDestination = Workbooks("Book2")

    'Copy paste only values
    wbSource.Worksheets("Sheet1").Range("A1").Copy
    wbDestination.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues

End Sub

VBA - Copy-Paste to another workbook

3 Answers3