copy range from one workbook to another in excel vba

Question

I have workbook in which i have to copy a range in one workbook to a range variable in the active workbook. i am getting an error when i execute the following..Is there any other ways of doing this.

Dim NTwbk As Workbook Dim AppExcel As Excel.Application Dim NewRng As Range Dim res As Range Sub OpenWBK() Set AppExcel = New Excel.Application Set NTwbk = AppExcel.Workbooks.Open(Sheet1.Range("SecondWorkbookPath")) NewRng= NTwbk.Sheets("Sheet1").Range("B3") AppExcel.Quit End Sub

I tried this also :

NewRng.Value = NTwbk.Sheets("Sheet1").Range("B3").Value

Also didnt work

Daniel · Accepted Answer · 2012-04-09 00:08:02Z

The main problem with your script is that once you close the 2nd workbook (via AppExcel.Quit or NTwbk.close as the other answer lists) the range object you referenced is no longer in memory and your variable is no longer usable. Also when assigning an object to a variable you must use set:

Set NewRng = Ntwbk.Sheets("Sheet1").Range("B3")

This would not fail, but will also not be useful because you then close Ntwbk.

Any action that needs to be performed with a range in the a workbook must be performed before that workbook is closed.

If all you want is the value of the range, this is a possible change:

Dim NTwbk As Workbook Dim AppExcel As Excel.Application Dim NewRng As Range Dim res As String 'Note the change from Range to String Sub OpenWBK() Set AppExcel = New Excel.Application Set NTwbk = AppExcel.Workbooks.Open(Sheet1.Range("SecondWorkbookPath")) NewRng = NTwbk.Sheets("Sheet1").Range("B3").Value AppExcel.Quit End Sub

You can then freely use NewRng as the value of the particular range in the other workbook.

@Tony Dallimore (I can't comment on yours yet.) I forgot to mention this... While "SecondWorkBookPath" is not technically a range, if the workbook opened has a range that is named "SecondWorkBookPath" then the code will function and correctly apply to the named range. This can be a very handy feature when you want to repeatedly reference a particular cell, but you can't guarantee its address will remain constant.
It is true that if SecondWorkBookPath is a named range and if Sheet1 is a worksheet object, this code could be OK. Too many ifs for me. However, my overlooking NewRng becoming invalid when the second workbook was closed is a serious error.

Tony Dallimore · Accepted Answer · 2012-04-07 20:14:55Z

You already have Excel open so you do not need to open a second copy so discard:

Dim AppExcel As Excel.Application Set AppExcel = New Excel.Application AppExcel.Quit

Change the open to:

Set NTwbk = Workbooks.Open(Sheet1.Range("SecondWorkbookPath"))

However "SecondWorkbookPath" is not a valid range. If cell A1 of worksheet "Sheet1" contains a valid path and file name, the following would be OK:

Set NTwbk = Workbooks.Open(Worksheets("Sheet1").Range("A1").Value) Set NewRng = NTwbk.Sheets("Sheet1").Range("B3") NTwbk.Close SaveChanges := False

Make the above changes and try again.

Collectives™ on Stack Overflow

copy range from one workbook to another in excel vba

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related