CODEWITHSUNDEEP
excelvba
Bryan R. Olmedo
Bryan R. Olmedo

Reputation: 1

How to copy a cell value from one excel file and paste it to another excel file?

I would like to copy a cell value from one workbook to another, but the problem is that the cell values are from different sheets of the workbook. How can I do this? Could you help me with this?

Upvotes: 0

Views: 18675

Answers (2)

David Zemens
David Zemens

Reputation: 53663

This is the bones of what you need, you can do a lot more with looping over ranges of cells, sheets in workbooks, etc., but fundamentally you will need to specify in VBA what is the target workbook, worksheet, and cell range, and then set that .Value equal to a specific workbook/worksheet/cell .Value in another workbook.

I use Workbook and Worksheet variables in this example, you can also use range variables for the cell(s) if you desire or if you need to apply to multiple cells.

Sub TransferValues()
Dim wb1 as Workbook
Dim wb2 as Workbook
Dim ws1 as Worksheet
Dim ws2 as Worksheet



Set wb1 = Workbooks("First Workbook.xlsx")  '<modify as needed'
Set ws1 = wb1.Sheets("The sheet name")      '<modify as needed'

Set wb2 = Workbooks("Another Workbook.xlsx") '<modify as needed'
Set ws2 = wb2.Sheets("Another sheet name")   '<modify as needed'


'This line puts the value from wb1.ws1 in wb2.ws2, cells specified:
ws2.Range("A1").Value = ws1.Range("A1").Value


End Sub

Upvotes: 3

NickSlash
NickSlash

Reputation: 5100

I'm guessing you want a formula, and not instructions on copy and paste.

Cell references can be made using the following format.

=[Workbook Name]SheetName!CellAddress

Example, you have two workbooks open, Book1 and Book2.

Book1, Sheet1, Cell A1 = =[Book2]Sheet1!A1

Book2, Sheet1, Cell A1 = Hello World

The value of [Book1]Sheet1!A1 will now be "Hello World" (it will always reflect the value that is in [Book2]Sheet1!A1, providing the book is open)

If you do want a copy of the actual value, and not a reference to it, you will need to just use plain copy and paste or use the above formulas and when you're done use copy+paste special (paste values) to convert the formulas to their values.

Update

Just realised it was tagged so you'd need to use something like @DavidZemens' answer.

You could also use the macro recorder to do this, but doing it manually like David suggests will result in nicer code that's easier to re-use/modify later on.

Upvotes: 3

Related Questions