Results 1 to 8 of 8

Easy Question Re: Copying Formatting Between Worksheets

This is a discussion on Easy Question Re: Copying Formatting Between Worksheets within the Excel Questions forums, part of the Question Forums category; I know this is easy, but duh...I can't figure it out. I am trying to copy the formatting from one ...

  1. #1
    New Member
    Join Date
    Mar 2003
    Posts
    30

    Default Easy Question Re: Copying Formatting Between Worksheets

    I know this is easy, but duh...I can't figure it out. I am trying to copy the formatting from one worksheet to another, but I want that formatting to change on worksheet 2 when the formatting on worksheet 1 changes. So let's say I have a cell that is red in worksheet 1. I want it to be red in a specified cell in worksheet 2. If I change the formatting to green in the cell on worksheet 1 it should change also in worksheet 2.

    Please help the helpless!

    Thanks,
    Bobby

  2. #2
    Board Regular armando's Avatar
    Join Date
    Jun 2003
    Posts
    157

    Default Re: Easy Question Re: Copying Formatting Between Worksheets

    Hi1;

    On the first worksheet, give a click to the rectangle above the number 1 indicating first row (the rectangle is at the left of letter A that indicates column A. Then copy, and select the same rectangle in the second worksheet and then, Edit/Paste Special/Format OK.

    A faster way is using the button "copy format" instead of copy. Then when you select the rectangle in the second worksheet you'll have the format copied.

    Regards

  3. #3
    New Member
    Join Date
    Mar 2003
    Posts
    30

    Default Re: Easy Question Re: Copying Formatting Between Worksheets

    Yea, but if I then change the format in worksheet 1 with this method it won't change it in worksheet 2. I would have to manually go back and do this again.

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Easy Question Re: Copying Formatting Between Worksheets

    Quote Originally Posted by robertuva
    Yea, but if I then change the format in worksheet 1 with this method it won't change it in worksheet 2. I would have to manually go back and do this again.
    Hi robertuva:

    For the change to be effected automatically, we need VBA. The following code uses the Worksheet_SelectionChange event ...
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
        If Target = [a2] Then [a2].Copy [sheet2!A2]
    End Sub
    when the color of cell A2 in the active worksheet changes, that change is also efected in cell A2 of sheet2.

    I hope this helps. If I have misunderstood your question, my apologies!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    Board Regular Chitosunday's Avatar
    Join Date
    Jul 2003
    Posts
    1,017

    Default Re: Easy Question Re: Copying Formatting Between Worksheets

    I think it is better to use the worksheet_activate method than the selectionchange because the selectionchange is activated when you enter the specific cell. 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()
    Sheets("Sheet1").Columns("A:A").Copy
    Application.ScreenUpdating = False
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    Range("a1").Select
    Application.ScreenUpdating = True
    End Sub
    Mr Young at Heart - Learning is like chasing the wind, it's boundless.

  6. #6
    New Member
    Join Date
    Apr 2010
    Posts
    13

    Default Re: Easy Question Re: Copying Formatting Between Worksheets

    I Have been trying to do the same but slightly different and unfortuantely my knowledge of VBA is limited. I have read both the scripts and have tried to figure out how to do it myself but have struggled and anyone who could offer me some help it would be greatly appreciated.

    I do my staff roster Monday to Sunday colouring in cells which represent start and finish times being I run with 10 staff it gets a little convalouded trying to read it so what I want to do is copy the formatting from one sheet to another for each staff member
    So I want to copy for formatting from
    c3:ac3 in sheet1 to b2:ad2 in sheet3
    c19:ac19 in sheet1 to b3:ad3 in sheet3
    c35:ac35 in sheet1 to b4:ad4 in sheet3
    c51:ac51 in sheet1 to b5:ad5 in sheet3
    c68:ac58 in sheet1 to b6:ad6 in sheet3
    c84:ac84 in sheet1 to b7:ad7 in sheet3
    c101:ac101 in sheet1 to b8:ad8 in sheet3
    This will copy one employees roster to the new sheet in order then I want to do it for the remaining 10 employees
    c4:ac4 in sheet1 to b10:ad10 in sheet 3
    c20:ac20 in sheet1 to b11:ad11 in sheet3
    ect ect

    If anyone can help I would be muchly appreciated
    Cheers
    Colby

  7. #7
    Board Regular
    Join Date
    Nov 2005
    Location
    USA
    Posts
    186

    Default Re: Easy Question Re: Copying Formatting Between Worksheets

    BurgerUrge,

    It's proper board etiquette to start your own thread instead of jumping into the middle of another person's question. In this case, since Robertuva's question has already been answered, there's not too much confusion that would arise from posting your question here. But this thread dates back 7 years and there's not much of a point in reviving such an old thread. You can certainly post a new thread and reference this one... e.g. "I've tried the scripts in the following thread.... but I have problems x, y, z...etc."

  8. #8
    New Member
    Join Date
    Apr 2010
    Posts
    13

    Default Re: Easy Question Re: Copying Formatting Between Worksheets

    Sorry burrgogi,

    Just didn't want it to seem like I hadn't tried to work it out myself or look arround before posting my question. will start a new thread now

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com