CODEWITHSUNDEEP
excelvba
ct4242
ct4242

Reputation: 33

How to copy a range of cells

I am trying to copy data from a closed Excel file to the workbook I am currently in. I would like to browse for the file and then have the macro do the rest.

I am getting an error in the target workbook that says

"Application-defined or object defined error."

Below is what I have so far. Target is the workbook I am opening and y is the current one, or should be at least! 

Option Explicit
Sub getfilename()

Dim myFilePath As String
Dim target As Workbook, y As Workbook

myFilePath = Application.GetOpenFilename()

'copying
Set target = Workbooks.Open(myFilePath)

target.Sheets("Sheet1").Range("R9C2:R20C2").Copy

'pasting
Set y = ActiveWorkbook

y.Sheets("Adjustment").Cells("R57C4").PasteSpecial

'close
target.Close

End Sub

Upvotes: 0

Views: 260

Answers (1)

Werrf
Werrf

Reputation: 1148

The RANGE object expects A1 notation rather than R1C1 notation. The CELLS object can use row number and column number (though you don't need the R..C.. structure there, either.

Sub getfilename()

Dim myFilePath As String
Dim target As Workbook, y As Workbook

myFilePath = Application.GetOpenFilename()

Set y = ActiveWorkbook

'copying
Set target = Workbooks.Open(myFilePath)

'Here we're using the A1 notation
target.Sheets("Sheet1").Range("B2","B9").Copy

'Here we're using the Row & Column numbers notation
y.Sheets("Adjustment").Cells(57, 4).PasteSpecial

'close
target.Close

End Sub

In addition, you don't actually need to use the Copy and PasteSpecial methods to duplicate values. It's not too big a deal in a small macro like this, but in a larger process you'd find it more efficient to duplicate the values directly using something like: target.Range("A1","A10").value = source.Range("A1","A10").value

Upvotes: 4

Related Questions