none
COPY RANGES OF CELLS IN SAME FORMAT FROM SHEET2 TO SHEET1 UPON A CONDITION RRS feed

  • Question

  • IF A CELL VALUE IN SHEET1 IS SUPPOSE SHEET1  K10="RDC"  I NEED TO COPY EXACTLY SAME FORMAT SHEET2 A12:N42  TO SHEET1 A12:N42  CAN SOME ONE HELP ME WITH THE CODES FOR THIS

    Sheets("Sheet2").Select
        Columns("A:A").Select
        Selection.Cut
       
        Sheets("Sheet1").Select
        Columns("C:C").Select
        ActiveSheet.Paste

    Friday, March 24, 2017 5:43 PM

Answers

  • Unprotect your sheet, then run this code:

    Sub ProtectSheet()

    ActiveSheet.Protect "Password", UserInterfaceOnly:=True
    End Sub

    That will allow VBA to make changes to your sheet without having to unprotect and protect it every time.

    • Marked as answer by DEEPU1980 Thursday, March 30, 2017 9:00 PM
    Tuesday, March 28, 2017 1:40 PM

All replies

  • If cell K10 is manually entered (or selected from a DV list):

    'IF A CELL VALUE IN SHEET1 IS SUPPOSE SHEET1  K10="RDC"  I NEED TO COPY EXACTLY SAME FORMAT SHEET2 A12:N42  TO SHEET1 A12:N42  CAN SOME ONE HELP ME WITH THE CODES FOR THIS

    1) Copy the code below.
    2) Right-Click the sheet tab of Sheet1.
    3) Select "View Code"
    4) Paste the code into the window that appears.
    5) Save the file as a macro-enabled .xlsm file.
    6) Make changes as needed

    Private Sub Worksheet_Change(ByVal Target As Range)

        If Target.Address <> "$K$10" Then Exit Sub
        If Target.Value <> "RDC" Then Exit Sub

        Application.EnableEvents = False

        Worksheets("Sheet2").Range("A12:N42").Copy Worksheets("Sheet1").Range("A12:N42")

        Application.EnableEvents = True
    End Sub

    Monday, March 27, 2017 5:02 PM
  • ITS WORKING FINE BUT WHEN WE ENTER K10=RDC IT CHNAGES AND IF AGAIN I CHANGE THE ENTRY IN K10=RPM IT SHOULD GO BACK TO OLD FORMAT CAN WE DO THAT
    Monday, March 27, 2017 5:44 PM
  • my sheet1 is password protected  so we need to unprotect password and do the pasting and again protect the page  also if k10= any other value we should undo the pasting ,orginal sheet1 should display
    Monday, March 27, 2017 6:20 PM
  • You need a third sheet - let's say Sheet3 - with the formatting on those cells that you want:

                       

    Private Sub Worksheet_Change(ByVal Target As Range)

        If Target.Address <> "$K$10" Then Exit Sub

        If Target.Value = "RDC" Then
            Application.EnableEvents = False
            Worksheets("Sheet2").Range("A12:N42").Copy Worksheets("Sheet1").Range("A12:N42")
            Application.EnableEvents = True
        End If

        If Target.Value = "RPM" Then
            Application.EnableEvents = False
            Worksheets("Sheet3").Range("A12:N42").Copy Worksheets("Sheet1").Range("A12:N42")
            Application.EnableEvents = True
        End If

    End Sub


    Note that if you are just changing formats, then you could use conditional formatting with the formula option using the formula

    =$K$10="RDC"

    Then you can control fill, font, etc. for those cells without a macro.



    Monday, March 27, 2017 6:20 PM
  • THANKS ITS WORKING FINE BUT WHEN K10=RDC A POPUP WINDOW APPEARS"DO WANT TO REPLACE CONTENTS OF DESTINATED CELLS.. CAN WE AVOID THIS MESSAGE..
    Monday, March 27, 2017 9:33 PM
  • can we add more field in "rpm" like "unsc","trev","prev" with same format and can we protect this sheet after the change, itried with the protected sheet its not working , or can we create sheet1 as interactive page with two buttons "work order" and "rpm" and when we click "workorder" sheet2 will activate and when we click "rpm" sheet3 will activate both sheet2 and 3 should be protected after activation, is it possible, can you please advise on this
    Monday, March 27, 2017 10:26 PM
  • Unprotect your sheet, then run this code:

    Sub ProtectSheet()

    ActiveSheet.Protect "Password", UserInterfaceOnly:=True
    End Sub

    That will allow VBA to make changes to your sheet without having to unprotect and protect it every time.

    • Marked as answer by DEEPU1980 Thursday, March 30, 2017 9:00 PM
    Tuesday, March 28, 2017 1:40 PM