I have a sheet in excel that has 40 rows, with some information in each row,

A           B
Nate        32
Bob         28
Cheryl      65
John        34
Candy       23

I have manually highlighted men in blue and women in pink. Now I would like to keep one sheet with this info updated, but I have other sheets where I want to have additional info in columns B. So In my next sheet I would like to have

A           B
Nate        Smith
Bob         Smith
Cheryl      Flores
John        Candy
Candy       --

The kicker, is that if I change Nate to Mary and format it pink in Sheet1 I want Sheet2 to reflect this, is it possible? I can get the data from the cell easily enough


But this only gets the data, is there a way to copy the custom formatting (background color, borders etc)? I would rather have these things on different sheets to avoid horizontal scrolling like mad but if I can't I will go that route.

  • Wouldn't freezing the 1st column (so it is always displayed) on a single sheet solve your problem better than having a lot of separate worksheets? – laurent Mar 19 '13 at 16:22
  • I'm open to alternate solutions, how would I do this? – Nate Mar 19 '13 at 16:27
  • I answered with a quick guide on how to do this. I think this is the best way to deal with large number of columns or lines. – laurent Mar 19 '13 at 16:56

Instead of moving your data to multiple sheets, you could try if freezing the 1st column (or more) would solve your problem. The frozen columns won't scroll and will stay always on the screen.

To do this, you drag the small icon next to the right of the horizontal scrollbar to the end of the column you want to freeze.

Drag this icon to where you want to freeze

Next you select the menu Exibition and there is an icon to freeze panes.

OBS: On office 2010, if you want to freeze only 1st column, you can do it directly on the same menu without draging the small icon before.

If you are using Office 2003, drag the icon and use menu Windows -> freeze

You can freeze lines the same way (using the icon on the top of the vertical bar)


This is possible with some VB.

Source: http://www.mrexcel.com/forum/excel-questions/55333-easy-question-re-copying-formatting-between-worksheets.html

Right click the sheet2 name below your display then view code and delete or clear any data in the view code and write this:

Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.