none
Help merge the following excel vb RRS feed

  • Question

  • I would like to streamline this clunky code.  I have 50 rows, that is the user input area then I copy the data to rows below. My problem is I can't lock some of the spreadsheet without affecting the rest. This works but has a studder it it, that you wizards know how to fix. Thank you!

    'Add Task
        Dim pw As String
        pw = ""
        With Range("b" & Rows.Count).End(xlUp)(2)
            ActiveSheet.Unprotect pw
            .EntireRow.Cells.Locked = False
            Range("B36:E36,G36,H36").Copy
            .PasteSpecial xlPasteValuesAndNumberFormats, _
                          Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            .EntireRow.Cells.Locked = True
           ActiveSheet.Protect pw
       End With
        'this is a redundant process how do I incorporat into the above process?
        pw = ""
        With Range("h" & Rows.Count).End(xlUp)(2)
         ActiveSheet.Unprotect pw
          .EntireRow.Cells.Locked = False
             Range("E29").Copy
            .PasteSpecial xlPasteValuesAndNumberFormats, _
                          Operation:=xlNone, SkipBlanks:=False, Transpose:=False
           .EntireRow.Cells.Locked = True
           ActiveSheet.Protect pw
     End With


    heads up

    Wednesday, January 1, 2014 4:51 PM

All replies

  • Try this version:

        'Add Task
        Dim pw As String
        Application.ScreenUpdating = False
        pw = ""
        ActiveSheet.Unprotect pw
        Range("B36:E36,G36,H36").Copy
        Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial _
           Paste:=xlPasteValuesAndNumberFormats
        Range("E29").Copy
        Range("H" & Rows.Count).End(xlUp)(2).PasteSpecial _
            Paste:=xlPasteValuesAndNumberFormats
        ActiveSheet.Protect pw
        Application.ScreenUpdating = True


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, January 1, 2014 5:08 PM
  • Is there a way to code this where "Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial _" is a variable? So I can share the row info with the next copy statement? Here is the error of my ways, if for whatever reason e29 is blank, this will throw my index off and what was once copied together can now be off by a  row or more. Thanks for your help?

    heads up

    Saturday, January 4, 2014 2:40 AM
  • Perhaps this?

        'Add Task
        Dim pw As String
        Dim r As Long
        Application.ScreenUpdating = False
        pw = ""
        ActiveSheet.Unprotect pw
        Range("B36:E36,G36,H36").Copy
        r = Range("B" & Rows.Count).End(xlUp).Row + 2
        Range("B" & r).PasteSpecial _
           Paste:=xlPasteValuesAndNumberFormats
        Range("E29").Copy
        Range("H" & r).PasteSpecial _
            Paste:=xlPasteValuesAndNumberFormats
        ActiveSheet.Protect pw
        Application.ScreenUpdating = True


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, January 4, 2014 8:55 AM