Excel VBA实现跨工作表粘贴单元格引用而非值的方法问询

Got it, let's get your cells syncing automatically instead of just copying static values! The problem with your current code is that it’s dumping the value of Sheet1's B5 into Sheet2's C15—no connection means no updates when B5 changes. Here are a few solid ways to set up that live reference:

1. Directly Set the Cell Formula #

The simplest way is to assign the formula directly to the target cell. This creates a permanent link that updates instantly when Sheet1's B5 changes:

' Link Sheet2's C15 to Sheet1's B5 (absolute reference)
Sheets("Sheet2").Range("C15").Formula = "=Sheet1!$B$5"

If you prefer using R1C1 notation (handy for dynamic row/column operations), use this instead:

Sheets("Sheet2").Range("C15").FormulaR1C1 = "=Sheet1!R5C2"

2. For Dynamic Row Scenarios (Your LastRow/Newpro Use Case) #

It looks like you’re working with dynamic rows (like LastRow in the Projects sheet and Newpro in the Database sheet). To link a dynamic row in Projects to the corresponding row in Database, just build the formula with your row variables:

' First, define your dynamic row variables (fill in your logic to get these values)
Dim LastRow As Long
Dim NewproRow As Long
LastRow = Sheets("Projects").Cells(Rows.Count, "B").End(xlUp).Row + 1 ' Example to get next empty row
NewproRow = ' Add your code to find the "Newpro" row in the Database sheet

' Set the formula to link Projects' LastRow B column to Database's NewproRow B column
Sheets("Projects").Cells(LastRow, "B").Formula = "=Database!$B$" & NewproRow

Use $ for absolute references (so the link doesn’t shift if you drag the formula) or omit them for relative references.

3. Using Paste Special (The Manual Method in VBA) #

You mentioned trying Paste Special and missing the right option—Paste Link is exactly what you need! Here’s how to do it manually or via VBA:

• Manual steps: Copy Sheet1's B5 → Right-click Sheet2's C15 → Paste Special → Select "Paste Link"
• VBA code to automate this:

Sheets("Sheet1").Range("B5").Copy
Sheets("Sheet2").Range("C15").PasteSpecial Paste:=xlPasteLink
Application.CutCopyMode = False ' Clean up the copy buffer

This does the same thing as setting the formula directly—pick whichever method feels more intuitive for your workflow.

内容的提问来源于stack exchange，提问作者Remi