Excel help please

  • RyanDS 14 Sep 2018 11:26:12 12,000 posts
    Seen 2 hours ago
    Registered 10 years ago
    Fucked if I can find the old thread. Google / forum search doesn't show it, I assume a poster got banned and took the thread with them.

    Anyway, I just need some simple VBA to throw into a macro for work.

    The entirety of what I need is:

    Look at cell $F$2 (This will be a number 1-52)

    Copy the whole row that is that number +8
    Paste whole row as Values.
    Update the cell in column AK in that row with "Yes"
    If possible lock down all cells so they can't be amended.

    So if cell F2 is 7, copy and paste row 15 and strip out the formulas.

    It is annoying me, as I am sure this should be very simple, but I am missing something in my skillset to achieve this. (Basicaly the point is each week I want someone to press a button to lock down the data so the old stuff doesn't get amended.)

    Any ideas kind people?

    Edited by RyanDS at 11:28:33 14-09-2018
  • Doozler 14 Sep 2018 14:57:37 98 posts
    Seen 4 hours ago
    Registered 5 years ago
    How about this? Doesn't protect cells as yet, but should do the rest.




    Sub main()
    Dim rn As Integer
    'read in row to copy
    rn = ActiveSheet.Cells(2, 6)
    'copy and paste row as values
    ActiveSheet.Rows(rn).EntireRow.Copy
    ActiveSheet.Cells(rn, 1).PasteSpecial Paste:=xlPasteValues
    'amend column AK to "yes"
    ActiveSheet.Cells(rn, 37) = "yes"
    'clear clipboard
    Application.CutCopyMode = False
    End Sub
  • Doozler 14 Sep 2018 15:06:51 98 posts
    Seen 4 hours ago
    Registered 5 years ago
    Locking the cells is a bit weird, as by default I believe all the cells are "locked" but the worksheet is not "protected". So I think you would have to:

    Unlock all the cells
    Password protect the worksheet
    Then lock the individual rows in VBA

    Problem is I don't think the copy rows approach will work... let me have a think.
  • RyanDS 14 Sep 2018 15:18:29 12,000 posts
    Seen 2 hours ago
    Registered 10 years ago
    Dude! You are a fucking legend! Thank you.
  • Doozler 14 Sep 2018 15:41:54 98 posts
    Seen 4 hours ago
    Registered 5 years ago
    Ok, the problem is that you need to protect a woksheet for Locking to have any effec- and then all the cells are locked by default.

    So:

    First UNLOCK all cells that a user might need to change (eg, all columns 1 - AK)

    Then use the code below. You'll end up with a sheet protected by the password "password". Obviously this is not super-secure - anyone who can see the caro code can see the password - but might be good enough for accidental error, which I presume is what you're worried about.



    Sub main()
    Dim rn As Integer
    Dim i As Integer


    'read in row to copy
    rn = ActiveSheet.Cells(2, 6)
    'copy and paste row as values
    ActiveSheet.Unprotect "password"

    'amend column AK to "yes"
    ActiveSheet.Cells(rn, 37) = "yes"
    ActiveSheet.Range(Cells(rn, 37), Cells(rn, 37)).Locked = True

    For i = 1 To 36
    ActiveSheet.Cells(rn, i).Copy
    ActiveSheet.Cells(rn, i).PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Cells(rn, i).Locked = True
    Next


    'clear clipboard
    Application.CutCopyMode = False
    ActiveSheet.Protect "password", True, True

    End Sub
  • Doozler 18 Sep 2018 15:30:24 98 posts
    Seen 4 hours ago
    Registered 5 years ago
    @RyanDS Any use?
  • RyanDS 18 Sep 2018 16:33:24 12,000 posts
    Seen 2 hours ago
    Registered 10 years ago
    @Doozler

    As above, it worked perfectly (well one small tweak on the rows.)

    Thank you so much!
Log in or register to reply

Sometimes posts may contain links to online retail stores. If you click on one and make a purchase we may receive a small commission. For more information, go here.