To post as a guest, your comment is unpublished.· 5 months agoHi Lil,
If you need to copy to a different sheet, please apply the below VBA code. Sheet1 is the original worksheet, Sheet3 is the destination worksheet. Please change them based on your needs.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If .Range("A5").Value2 <> "" Then
On Error Resume Next
How to reference format and value from another cell in Excel?
Normally, we use the formula =A1 to reference cell A1 to another cell in worksheet. But this can only reference the cell value. If you want to reference the cell value as well as its format, you need to try another method. This article provides two methods for you to achieve it.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Supposing you want to reference format and value from cell A1, please do as follows to get it done.
1. Select the cell (A1) you need to reference, then copy it with pressing Ctrl + C keys.
2. Go to the cell you want to link the reference cell, right click it and select > Paste Special > Linked Picture. See screenshot:
Now the format and value of cell A1 is referenced to a specified cell. And the format and value of these two cells will be synchronic as below screenshot shown.
You can reference format and value from another cell automatically by running the below VBA script.
1. Right click the sheet tab which contains the cell you need to reference, and then click View Code from the right-clicking menu.
2. In the popping up Microsoft Visual for Applications window, copy and paste the below VBA code into the Code window.
VBA code: Reference format and value from another cell
Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Worksheets("Sheet1") If .Range("A1").Value2 <> "" Then On Error Resume Next Range("A1").Copy (.Range("E2")) End If End With End Sub
Note: In the code, Sheet1 is the sheet name which contains the cell you need to reference both the value and format. A1 and E2 means that cell A1 will be referenced to cell E2 automatically.
From now on, when value changes in cell A1 of Sheet1, its value and format will be referenced to cell E2 immediately.
- How to reference or link value in unopened/closed Excel workbook file?
- How to reference worksheet by index number instead of name in Excel?
- How to remove all formula references but keep values in cells in Excel?
- How to keep formula cell reference constant in Excel?
- How to reference tab name in cell in Excel?
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 6 months agoHow do I do that if I want to copy to a different sheet?
To post as a guest, your comment is unpublished.