Excel VBA copying literal cells from another sheet (do I really need to vlookup?)

0
votes

I have two sheets in a workbook in Excel; "Sheet1" and "Sheet2". Sheet1 has a lot of data that will be changed, and shee2 has two active cells. These cell equal whatever is in the first two cells of on the other page. That is, if (A1 & A2) on sheet1 equal (3 & 5) then on sheet2 they will equal the same thing. Right, so as for the Excel VBA part. I making a macro that creates "sheet2" . so the macro needs to take Range "A1:A2" from "sheet1: and put them at Range"A3 & A4" on sheet2. So, when the value on sheet1 A1 changes, then on sheet2 A3 it will also change.

I just want to know how to copy the dynamic value of a cell on another sheet. Is this possible, because I swear the closest thing I can find is a vlookup and that just seems to complicated for such an easy task, and I'm not even sure that would work.

TL;DR How copy dynamic value of cell from another sheet with no vlookup in VBA?

1
vbaexcelvlookup
Can you just put a link instead of the value? In your sheet2, if you want to show info from Sheet1, would something like this work in Sheet 2, cell A1: =Sheet1!A1. Then, whenever A1 changes, so does that one. Also, there's index/match (which is similar to Vlookup) that may do what you need. – BruceWayne

1 Answers

0
votes

You can go to your Excel sheet -> Formula Ribbon -> Name Manager -> New Name

Set your new names to be equal to $A$1 and $A$2. When you insert rows etc. in Excel, the Name Manager will automatically shift around so it stays pointing at the 'old cells'.

Then in VBA, you can refer to these as Range("NAME_FOR_A1") or Range("NAME_FOR_A2"), and it will always refresh to the 'updated' target.

Or, reading your question again, you could simply take the values from A1 & A2, put those values to a variable, and pass them to A3 & A4. So, something like:

A1Variable = Sheets(1).Range("A1").Formula
A2Variable = Sheets(1).Range("A2").Formula

Sheets(2).Range("A3") = A1Variable
Sheets(2).Range("A4") = A2Variable

Vlookup is not relevant here; that's an Excel Formula tool, not VBA. Hypothetically you could use it, but only rarely would that be simpler than using a VBA approach.