none
I HAVE TWO SHEETS WHNE K10 IN FIRST SHEET ="RDC" THAT SHEET SHOULD APPEAR LIKE SHEET 2 RRS feed

  • Question

  • I HAVE TWO SHEETS, WHEN K10 IN FIRST SHEET ="RDC" THAT SHEET SHOULD APPEAR LIKE RDC SHEET ONLY SHEET ELSE SAME SHEET1, CAN WE DO THAT , OR CAN WE HAVE ONE INTERACTIVE PAGE AND WHEN CLICKS SHEET1  IT WILL APPEAR AND CLICK RDC THAT SHEET WILL APPEAR.

    TO END CHANGES WILL OCCUR SEE BELOWSHEET1RDC

    Friday, March 24, 2017 11:05 AM

Answers

  • Hi,

    Do you mean when inputting "RDC" in the sheet, the format of this sheet is changed into the first picture?

    We could achieve that by re-formatting like deleting original tables and contents and re-generating new ones. However, I think this is not efficient and reasonable. Since the template exists, we could handle Worksheet.Change Event (Excel) and activate another available sheet when the value of Range K10 is "RDC". Or we could use Range.PasteSpecial Method (Excel) with format.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Saturday, March 25, 2017 7:45 AM
    Moderator

All replies

  • Hi,

    Do you mean when inputting "RDC" in the sheet, the format of this sheet is changed into the first picture?

    We could achieve that by re-formatting like deleting original tables and contents and re-generating new ones. However, I think this is not efficient and reasonable. Since the template exists, we could handle Worksheet.Change Event (Excel) and activate another available sheet when the value of Range K10 is "RDC". Or we could use Range.PasteSpecial Method (Excel) with format.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Saturday, March 25, 2017 7:45 AM
    Moderator
  • yes I mean when I put "order type as "rdc" in first sheet the second sheet format should come
    Saturday, March 25, 2017 7:33 PM
  • cn u help me with the codes so that I can try it and check ....
    Saturday, March 25, 2017 8:12 PM
  • Hello,

    For example, use the following code to paste Range("B7:D9") from Sheet2 into Sheet1 when  Sheet1.Range("K10") is "ABC".

    I am pasting all contents and formats here, you could also get other paste options from XlPasteType Enumeration (Excel)

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("K10")) Is Nothing Then
        If Range("K10").Value = "ABC" Then
        Sheets("Sheet2").Range("B7:D9").Copy
        Sheets("Sheet1").Range("B7").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
        End If
    End If
    End Sub
    
    

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 27, 2017 5:52 AM
    Moderator
  • I tried this code with changing the names of the tabs and range see below its not working  I will paste the page which I want to copy

    Private Sub Workshet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("K10")) Is Nothing Then
        If Range("K10").Value = "RDC" Then
        Sheets("Example").Range("A12:N42").Copy
        Sheets("Request").Range("A12").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
        End If
    End If
    End Sub

    Monday, March 27, 2017 6:13 AM
  • Do you get any error? Where do you write the code?

    Please open your VBE and find Sheet(Request) in the Project list and paste the code above in the page.

    E.g.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 27, 2017 7:07 AM
    Moderator
  • THE BELOW ARE THE CODES WHICH I HAVE IN THAT PAGE

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("K6,D8,D6,D9,K9"), Target) Is Nothing Then
            Me.Unprotect Password:="secret"
            Range("A14:N36").Locked = _
                (Range("K6").Value = "") Or _
                (Range("D8").Value = "") Or _
                (Range("D6").Value = "") Or _
                (Range("D9").Value = "") Or _
                (Range("K9").Value = "")
            Me.Protect Password:="secret"
        End If
    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim rng As Range
        Set rng = Range("A14:N36")
        If Me.ProtectContents And rng.Locked And Not Intersect(rng, Target) Is Nothing Then
            MsgBox "Fill the above Red Highleted cells first to unlock these cells", vbExclamation
        End If
    End Sub


    Private Sub Workshet_Change(ByVal Target As Range)
     If Not Intersect(Target, Range("K9")) Is Nothing Then
         If Range("K9").Value = "RDC" Then
         Sheets("Sheet2").Range("A12:N42").Copy
         Sheets("Sheet1").Range("A12").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
             False, Transpose:=False
         End If
     End If
     End Sub

     is this because we are trying to copy blank rows.

    Monday, March 27, 2017 7:22 AM
  • Please merge the two Worksheet_Change events. Or comment out the first one to test if the 2nd works.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, March 27, 2017 7:28 AM
    Moderator
  • one more query the cells A12 to n42 in sheet one is locked until we enter date in another fileld is thia the reason why this code is not working

    or can we try the other method of creating one message box while opening the worksheet and ask road call or workorder request and when cliks one go to respective tabs

    Monday, March 27, 2017 7:28 AM
  • If it is the current code you are using, Workshet_Change is wrong. Excel couldn't recognize the event.

    >>one more query the cells A12 to n42 in sheet one is locked until we enter date in another fileld

    If the range is locked, unlock the range before pasting and then lock it again.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 27, 2017 9:55 AM
    Moderator
  • THIS ERROR MESSAGE IS COMING

    Monday, March 27, 2017 2:01 PM
  • THIS ERROR MESSAGE IS COMING

    Monday, March 27, 2017 2:06 PM
  • I I CHANGE THE PASTING CELL TO A18 ITS WORKING BUT WHEN WE CHNAGE THE VALUE OK k10 ITS NOT GOING BACK TO OLD FORMAT
    Monday, March 27, 2017 2:37 PM
  • If you want the format changed based on a cell value, you need to format all cells in the code. Meaning you need to set its borders/colors and merge cells or some other formatting you need in the code.

    Copy/Paste is a simple way, but it needs a sheet or some range to store the old/new format for copying. So if you want the format goes back, the old format should be somewhere for copying.

    Please see the following sample, if K9=RDC, then the range format copies from another sheet. If not, create new formats for these cells for example, add borders and merge some cells and fill color.

    Private Sub Worksheet_Change(ByVal Target As Range)
     If Not Intersect(Target, Range("K9")) Is Nothing Then
        If Range("K9").Value = "RDC" Then
        'copy existing format
        Sheets("Sheet2").Range("A12:N42").Copy
        Sheets("Sheet1").Range("A12").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
        Else
        'create new format
        Range("A12:N42").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Range("B12:D12").Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Selection.Merge
        Range("A12:F12").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        End If
      End If
    End Sub


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 4, 2017 2:17 AM
    Moderator