VBA Copying Excel Range to Different Workbook

Question

I am trying to find a way to copy a range in one workbook, in this case A6:J21,to another workbook. I thought it would be something like the following...

currentWorksheet = xlWorkBook.Sheets.Item("Command Group")
excelRange = currentWorksheet.Range("A6:J21")
excelDestination = newXlSheet.Range("A6:J21")
excelRange.Copy(excelDestination)

But it gives me an error on excelRange.Copy(excelDestination).

The below code runs as expected, so I'm not sure where i'm going wrong here..

Dim xRng As Excel.Range = CType(currentWorksheet.Cells(7, 7), Excel.Range)
Console.WriteLine(xRng.ToString)
Dim val As Object = xRng.Value()
testString = val.ToString
Console.WriteLine(testString)
newXlSheet.Cells(1, 1) = testString

Try just `excelRange.Copy excelDestination`, assuming you set `newXlSheet` somewhere. — BruceWayne, Mar 30 '17 at 17:18
I'm confused by what you mean? wouldn't you have to say what range in the `newXlSheet` to put it in? — Bob, Mar 30 '17 at 17:29
You do `excelDestination = newXlSheet.Range(...)` but I don't see where you ever declare `newXlSheet`... So, if you never declare/set it, it'll error on you because VBA doesn't know what sheet that is. Add `Option Explicit` to the *very top* of your code (even above `Sub mySub()`), which will force you to declare all variables used in your macros. — BruceWayne, Mar 30 '17 at 17:44
What error do you get when it errors out on the `.Copy` line? How does the second code block relate to the first one that errors? Or are you just using that second one to show you can use `newXlSheet` without any issues? — BruceWayne, Mar 30 '17 at 19:52

score 1 · Answer 1 · answered Mar 30 '17 at 21:50

To answer your question "Why is B running, but not A"..

In A: currentWorksheet = xlWorkBook.Sheets.Item("Command Group")

First, you are missing SET for your object assignment. Secondly, you are using Workbook.Sheets.Item() which returns a Sheets object. A Sheets object can represent either a chart sheet, or a work sheet, and therefore does not have a .Range() method.

You can verify this by stepping over this code:

Dim currentWorksheet As Sheets
Set currentWorksheet = ThisWorkbook.Sheets.Item("Command Group")
excelRange = currentWorksheet.Range("A1:A21")

It will error, and tell you that the method is not found.

To Fix A: Ensure you get back a Worksheet object by using strong typing.

Dim currentWorksheet as Worksheet
Set currentWorksheet = ThisWorkbook.Sheets.Item("Command Group")

To fix future code and ease the debugging process I highly recommend always declaring Option Explicit at the top of all your modules.

For brevity you can shorten your code to:

Dim currentWorksheet as Worksheet
Set currentWorksheet = ThisWorkbook.Sheets("Command Group")

score 0 · Answer 2 · edited May 23 '17 at 12:17

This should do it, let me know if you have trouble with it:

Sub foo()
Dim x As Workbook
Dim y As Workbook

'## Open both workbooks first:
Set x = Workbooks.Open(" path to copying book ")
Set y = Workbooks.Open(" path to destination book ")

'Now, copy what you want from x:
x.Sheets("name of copying sheet").Range("A1").Copy

'Now, paste to y worksheet:
y.Sheets("sheetname").Range("A1").PasteSpecial

'Close x:
x.Close

End Sub

Alternatively, you could just:

Sub foo2()
Dim x As Workbook
Dim y As Workbook

'## Open both workbooks first:
Set x = Workbooks.Open(" path to copying book ")
Set y = Workbooks.Open(" path to destination book ")

'Now, transfer values from x to y:
y.Sheets("sheetname").Range("A1").Value = x.Sheets("name of copying sheet").Range("A1") 

'Close x:
x.Close

End Sub

Copy from one workbook and paste into another

score 0 · Answer 3 · answered Jun 10 '21 at 11:04

Refer to the below code to copy data from one worksheet(say file1) to the other(say file2). I created this file to avoid copying formats from the other workbook as it was causing the file(say file1) to crash. The intention is to copy only values cell by cell from one sheet to another sheet.

Sub Copydatafrom_sheets()
    'This will copy sheet cell by cell without selecting the cells.
    'commented items are not used in the code execution
    Dim i As Long
    Dim j As Long
        i = 1
        j = 1
    Application.ScreenUpdating = False
    Dim file1 As Workbook ' defined as workbook
    Dim file2 As Workbook ' defined as workbook
    Dim range1 As Range
    Dim range2 As Range ' not used
    Dim Copied_data As String ' to store data in this string while iterating
    Set file1 = Workbooks.Open("G:\MyProject - Backup\QAQC\Data Combined - 2.xlsx") ' file where orinal data is stored, use your own file names
    Set file2 = Workbooks.Open("G:\MyProject - Backup\QAQC\Test3.xlsm") ' File where it shall be copied
    Set range1 = file1.Sheets("ASC_Table_1").Range("A1:V25944") 'set the range to be copied
    For Each cell In range1
    
        Copied_data = file1.Sheets("ASC_Table_1").Cells(i, j).Value
        'MsgBox (Copied_data)
        file2.Sheets("Sheet2").Cells(i, j) = Copied_data
          If j <= 22 Then j = j + 1
            If j > 22 Then
                 i = i + 1
                 j = 1
            End If
    Application.StatusBar = Format((i / 25994), "Percent")
    Next
    Application.ScreenUpdating = True
    file2.Save 'Optional
    End Sub

VBA Copying Excel Range to Different Workbook

3 Answers3

Linked