Cell Formatting with replication 1

[Scott24x7]

Hi All,
I use Excel for comparisons where I create multiple sheets in the same workbook, and then update all the static elements on the first page, and replicate them in the other pages by just referencing their cell.
So for instance, Sheet1, cell C6 may have the text "Vendor Criteria for Management Systems". Then in sheet 2, I just go to cell 6 and enter =Sheet1!C6

So this replicates my text, but NOT the formatting of the text from the other page. So is there some way to replicate the text between cells AND keep the formatting, without having to copy/paste it. (Essentially I want the target page to inherit the properties of the text field, so I don't have to go across 15 pages and CTRL+V the text, as there's some 200+ criteria...
Many thanks.


Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[combo]

You can:
1) create macro and assign to hotkey (does not work properly when run from macros list):
[pre]Sub PasteLinkAndFormat()
ActiveSheet.Paste Link:=True
Selection.PasteSpecial Paste:=xlPasteFormats
End Sub[/pre]
2) create template sheet if your data has the same structure and insert sheet from the tab's popup menu (see for instance

https://www.extendoffice.com/documents/excel/2341-excel-create-sheet-template.html)

combo

 

[Scott24x7]

Thanks, but that is too messy with thousands of fields over multiple tabs. It's too bad that it doesn't reflect RTF formatting in the new cell. Disappointing...

Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[combo]

No other way if you insert a link only. Using macro (the first solution) in a support workbook or personal, you can only copy cell, select target and press own defined shortcut.

combo

 

[SkipVought]

Hi,

This is a VBA solution. You need ONE small (relatively) piece of code if I understand your requirement correctly.

Assuming that Sheets(1) is your master sheet

In the VB Editor, open the ThisWorkbook object

In the ThisWorkbook object code module,

1) select Workbook in the OBJECT drop down

2) select SheetChange in the PROCEDURE drop down

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    With Target
        Sheets(1).Cells(.Row, .Column).Copy
        .Cells(1, 1).PasteSpecial xlPasteFormats
    End With
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Scott24x7]

Hi Skip,
Thanks, I think I'm getting close, but I'm not sure or I missed something intuitive.

So as you mention Sheet1 is the master sheet. It has been renamed to Vendor 1, does that change the code? I'm not very VGA savvy.
Also, I did the following:
Clicked "Developer" from the tabs at top.
Clicked "Visual Basic" button in the ribbon.
That opened the VBA editor, but I didn't see anything called "OBJECT".
I right clicked on ThisWorkbook in the Project-VBAProject window.
That brought up a <filename> - ThisWorkbook (Code) window.
I selected Workbook from the first drop down box, and in the second drop down box I selected Sheet Change.
I then pasted that code snippet into that window, and then I close the window, and exit the editor.
I then saved the file as a "Macro-Enabled Workbook".
But when I went to following pages, there was no change to the fields.
(I set one cell in first page to yellow background, and firs line of text as bold, but in the next page, where =Vendor1!C6 is in the same cell location, I still have only the plain text without any RTF applied.
Is there something I'm missing?
(Or that I did wrong?)

Also, don't think this is a big deal but I'm using Office 2016 version of Excel.



Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

Plz upload your workbook. I’ll look at it when I get back to my “office.”

But when I went to following pages, there was no change to the fields.

The formatting on the other sheets, takes place when YOU make a change in any cell.

Just go and Edit/Enter any of your formulas referencing a cell in Sheet 1.

My code reacts to...

Then in sheet 2, I just go to cell 6 and enter =Sheet1!C6

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Scott24x7]

Hi Skip,
I've created a small/simple version.
The idea is font data from master page cell should be replicated to the other forms cells. What is odd is 2 pages show the yellow cell background, one doesn't, none show the bold text...
I'm also getting some run time error, but i'm sure you'll see that when you make any text change.
Thanks for your help and your time.
-S


Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

C6 on Vendor 2: I edited and hit ENTER and the background is yellow.

You CANNOT format portions of text in a formula. so...none show the bold text.

I do have a method using the CAMERA Icon/Feature.

Instead of using a FORMULA, Select the cell in Vendor 1, Select the CAMERA Icon in the toolbar, select the sheet of interest and then the CELL. A PICTURE of the target is displayed.

This approch reflects all value & format changes to the first sheet immediately.

My procedure uses the row and column widths from the Vendor 1 sheet to format the target sheet.

Attach an example

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Here's an improved procedure

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim i As Long, UR1 As Range, sp As Shape
    
    If Sh.Name = Sheets(1).Name Then Exit Sub
    
    Set UR1 = Sheets(1).UsedRange
    
    'adjust row and column fits
    For i = UR1.Column To UR1.Columns.Count + UR1.Column - 1
        Sh.Columns(i).EntireColumn.ColumnWidth = Sheets(1).Columns(i).ColumnWidth
    Next
    
    For i = UR1.Row To UR1.Rows.Count + UR1.Row - 1
        Sh.Rows(i).EntireRow.RowHeight = Sheets(1).Rows(i).RowHeight
    Next
    
    'position & size each pic
    For Each sp In Sh.Shapes
        With sp
            If Left(sp.Name, 7) = "Picture" Then
                .LockAspectRatio = msoFalse
                .Top = Evaluate(.DrawingObject.Formula).Top
                .Left = Evaluate(.DrawingObject.Formula).Left
                .Width = Evaluate(.DrawingObject.Formula).Width
                .Height = Evaluate(.DrawingObject.Formula).Height
            End If
        End With
    Next
    
    Set UR1 = Nothing
End Sub

Your file returned



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Scott24x7]

Hi Skip,
This is great. I noticed that moving sheet to sheet it became really sluggish, but I also noticed that once the display is as you want it to be, you can disable the macro in the background, the the display text remains. So this will enable me to update the first tab, then go to all the other pages and cells and allow those to update, and then I can disable the function but the formatting in the other pages persists. I get that they won't update unless I renable it after changes in sheet 1, but this is very workable.
Still saves me heaps of time.
Many thanks. It's odd to me that MS haven't implemented supporting RTF data from cell to cell...


Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

I haven’t used the Camera feature often but its a handy tool. The most useful it has been to me was displaying indentured bills of material for major assemblies, for the purpose of highlighting schedule delays in lower legs of the BOM for a shop floor display.

The top rows of the sheet were used for control values. The BOM was actually assembled on another sheet and referenced in the picture which had a fixed size to fit the one screen display.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Scott24x7]

Very clever. I hadn't encountered it before.


Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."