include formatting when referencing cell from another workbook

Question

I want to pull in/copy over the values and formatting of a cell (the text color, the font size, bold etc) from one workbook to another.

As I understand it, using a standard cell referencing doesn't work, you need vba.

Here's some vba code which copies the formatting from a3 to a10 in the same worksheet:

Dim ping As Boolean
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A3")) Is Nothing Then
If ping = False Then
Range("A3").Copy
Range("C10").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
End If
ping = True
Exit Sub
Else
ping = False
End If
End Sub

How can I change this code to copy A3 from a different workbook named "mybook"?

yes, you need VBA if you don't want to do it manually. Formulas are for calculating only and therefore they only reference to the value of cells because you can't calculate with borders and text colors. — Pᴇʜ

Fillard Millmore Fillard Millmore · Accepted Answer · 2017-05-24T13:06:15

If you simply want to do a one-time copy of a cell(s), I do not believe you'll need VBA. You should just be able to highlight the cells you desire for copying in one workbook, then CTRL+C [copy highlighted contents] - then open the workbook for pasting, right click the upper-left cell of the area to be pasted, click "Paste Special", and make sure that the 'Paste' radio-button is set to 'All', and click "Paste". If this is a link to data you wish to paste along with the formatting, the solution is more involved.

include formatting when referencing cell from another workbook

1 Answers