none
Cell to bring value and formatting both RRS feed

  • Question

  • Hi,

    I enter formula =sheet1'!A1 in cell A1 on sheet2. It brings value from sheet1 A1. I want it to bring value as well as formatting both. Is it possible by vba code ? if so please help.

    I have long list of articles. I change them frequently in sequence. I need to print them in A4 size. I have to get the long sheet to get break down in A4 size printable sheet retaining first two lines and last one line same (headings and name etc) in all sheets. The sequence should change as I do in changes in long sheet.

    regards

    Monday, March 9, 2015 12:34 PM

Answers

  • Hi Drsantoshsinghrathore,

    Hi,

    Thank you,

    This code will format cell as number from text format ? Then it is not so helpful for my current problem. I want formatting about cell lining of border etc.

    regards

    Yes, the code would format cell to the number format based on the source cell. We also could set the cell format using CellFormat object. And here is an example that set the border for the cell for your reference:

    With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With

    Also we can use the Recod Macro to get the simpliar code if we are not familair with Excel Object devleoping.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, March 11, 2015 9:20 AM
    Moderator

All replies

  • Hello,

    I am having a difficult time understanding what you are trying to do. Can't you just use the print layout to have your long list print into multiple sheets? You can have headers and footers repeated on each sheet and this would remove the need for multiple sheets in your workbook.

    If you would like to use this method go to VIEW, PAGE BREAK PREVIEW, set your page breaks for each printable area.

    To add footers / headers / scale the pages go to PAGE LAYOUT. Here you can set the width and height of each page (generally set the width to 1 page then do your PAGE BREAKS). To add the footers / headers go to PRINT TITLES in the PAGE LAYOUT tab.

    If this is not what you are looking for then please reply.

    THANKS!

    Monday, March 9, 2015 4:13 PM
  • Hi,

    I cannot add such header on all pages. please help me to do this. I want this header,

    And the formatting I want is similar to visible above. When I change the the formatting in big list I need it to change in there in A4 size sheet automatically.These two things needed. Heading and Formatting. Or page break. please help.

    regards

    Tuesday, March 10, 2015 2:06 AM
  • Hi Drsantoshsinghrathore,

    Based on the description, you want to synchronize the value and format from different cells.

    Can you explain more detail about the format you want to change?

    As far as I know, there is no such feature in Excel we can achieve the this goal. And the worksheet change event doesn't fire when the number formating or font of cell is changed.

    A possible workaround is that creating a procedure to set the formatting for the target cell. And here is a sample to set the number formatting of cell A2 based on A1 for your reference:

    Sub setNumberFormat()
    
    Range("A2").NumberFormat = Range("A1").NumberFormat
    End Sub
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 10, 2015 7:47 AM
    Moderator
  • Hi,

    Thank you,

    This code will format cell as number from text format ? Then it is not so helpful for my current problem. I want formatting about cell lining of border etc.

    regards

    Tuesday, March 10, 2015 2:18 PM
  • Hi Drsantoshsinghrathore,

    Hi,

    Thank you,

    This code will format cell as number from text format ? Then it is not so helpful for my current problem. I want formatting about cell lining of border etc.

    regards

    Yes, the code would format cell to the number format based on the source cell. We also could set the cell format using CellFormat object. And here is an example that set the border for the cell for your reference:

    With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With

    Also we can use the Recod Macro to get the simpliar code if we are not familair with Excel Object devleoping.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, March 11, 2015 9:20 AM
    Moderator
  • Hi Fei Xue,

    Thank you,

    I made an automation what I needed. It became a very big code and four command buttons. I tried but could not make it smaller and with single command button. Better if it works with change in the long main sheet.

    please make the corrections.

    https://onedrive.live.com/redir?resid=90B1344C0622B513!9293&authkey=!ACRjcUH7eBjARc4&ithint=file%2cxlsm

    http://1drv.ms/1AgmhJt

    regards

    Wednesday, March 11, 2015 7:19 PM
  • Hi Drsantoshsinghrathore,

    >>Cell to bring value and formatting both<<

    Is the original issue resolved? If yes, I suggest that you reopen a new thread to involve more communities
    into discussing so that you can get more effective response.

    Also I would suggest that you separate the technical issue from the requirement or provide a demo. Considering
    the security, we don't recommend that you upload a project contained the privacy data.

    Thanks for your understanding.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, March 12, 2015 10:05 AM
    Moderator