CODEWITHSUNDEEP
excelcopyrangepastevba
Bob
Bob

Reputation: 1396

VBA Copying Excel Range to Different Workbook

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

Upvotes: 2

Views: 11029

Answers (3)

Abdul Raheem
Abdul Raheem

Reputation: 1

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

Upvotes: 0

ASH
ASH

Reputation: 20362

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

Upvotes: 0

Adam Vincent
Adam Vincent

Reputation: 3861

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")

Upvotes: 1

Related Questions