1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: copy/paste macro

Discussion in 'Business Applications' started by costagin, Apr 22, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. costagin

    costagin Thread Starter

    Joined:
    Apr 15, 2010
    Messages:
    8
    I need a macro that will run from sheet 2, select sheet1, unprotect it, copy the contents of Sheet1 N17, unprotect sheet 2 and PasteSpecial Values into cell A27 on sheet 2. The next time the macro runs it has to do the same thing except put the Sheet1 N17 value into the next empty cell to the right of N27. That has to happen until Y27 has something in it. Then the next time the macro runs, it needs to delete all the values in N17 thru Y17 and start over in N17. Each sheet has to be protected after each time the macro is run.

    I just can’t get the value to go into the next empty cell. I will appreciate any help I can get on this.

    This is what I have so far.


    Sub Macro6()
    '
    ' Macro6 Macro
    '
    '
    Sheets("Sheet1").Select
    ActiveSheet.Unprotect
    Range("N17").Select
    Selection.Copy
    Sheets("Sheet2").Select

    Range("A27").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Sheets("Sheet1").Select
    Range("D13").Select
    Application.CutCopyMode = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Sheets("Sheet2").Select
    Range("D3").Select
    End Sub
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Try the attached sheet
    I rewrote your Macro6 -> Macro6B

    Maybe this part of the code will help you with the rest:)
     

    Attached Files:

  3. costagin

    costagin Thread Starter

    Joined:
    Apr 15, 2010
    Messages:
    8
    Keebellah, thank you very much for the reply. I did make a mistake in my request. I need the first value to go into cell N27 and not A27. Then each time the macro runs it needs to continue copying into the next cell to the right until it has put something into Y27. The next time it runs, it should clear cells N27-Y27 and put the next value in N27 again.

    I noticed another strange problem. I replaced the word TEST in N17 with the formula =SUM(N13:N16). The values in those four cells gave me a value of 10 in N17. When I run macro 6b it puts the value 101 in cell A27, 102 in cell B27, 103 in cell C27, etc. I can't imagine why it's tacking an incrementing number onto the correct answer of 10 each time it runs.

    Thank you again for your help I really appreciate it.

    Costagin
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    If you read my version correctly you will notice that I added the colum number, this was just to show where you were.
    Remove & activecell.column part and it's over.

    Do I have to help to move it to N27 instead of A27?
    And is that N27 in Sheet1 or in Sheet2?
     
  5. costagin

    costagin Thread Starter

    Joined:
    Apr 15, 2010
    Messages:
    8
    Thanks for the help. That took care of the extra digit. I don't know how to move the A27 to N27 either. They're both on sheet 2. Help with that would be very much appreciated.

    Thanks again.
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Just so that I understand it:
    You need Sheet 1 to protected but you also want to fille N17 through Y27 and when all are filled stat again with N17?
    You als fille N17 with a formula, this one will be deleted when clearing the range
    Okay, where is A27 in Sheet1 or Sheet2?

    Is Sheet1 a results sheet?
    I think a little more ino to understand it will help me grasp the idea
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I editted the code so that everything happens in Sheet1 and it remains protected.

    See if you can edit it for your use
     

    Attached Files:

  8. costagin

    costagin Thread Starter

    Joined:
    Apr 15, 2010
    Messages:
    8
    Keebellah, I’m sorry to be taking up so much of your time but I very much appreciate all your help. I’ll try to get my explanation right this time so you can see what I’m trying to accomplish. I have also had to make a change to my spreadsheet so we’ll be using N30:Y30 on Sheet2 instead of N27:Y27.

    Sheet1 is the data sheet and sheet2 is the results sheet. The macro will be run from sheet2.

    The value in Sheet1 N17 needs to be copied to Sheet2 cell N30 the first time the macro runs.
    (Sheet1 N17 is the sum of the cells Sheet1 N11:N16. I enter the values into those six cells manually.)

    The second time the macro runs the value in Sheet1 N17 will be copied to Sheet2 O30.

    Each time the macro runs it will copy to the next cell on sheet2 until it has done so 12 times. That will fill cells N30:Y30. (Those cells actually represent the months of the year so the macro will be ran once a month. They won’t have any month or date information in them, just the value from Sheet1 N17.)

    After all 12 cells have been filled with a value from Sheet1 N17, the next time the macro runs it will clear all 12 cells Sheet2 N30:Y30 and start over again with cell Sheet2 N30. The macro does not need to clear the cell of Sheet1 N17. (Since I manually enter the values into Sheet1 N11:N16 the value of N17 changes each month.)

    This spreadsheet is a financial statement so I keep it protected from anyone else changing it. Therefore, each time the macro runs it needs to unprotect both sheets and then leave them protected when it finishes. I could unprotect and protect them manually if it’s too troublesome to do it in the macro.

    I hope I haven’t missed anything this time. Thank you again for all the time you’re spending on me.
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Okay, I think your explanation is complete, at least I think I undrstand it.
    I'll work it out and hope to let you know something by Monday morning.
    SO all I have to do ies read from Sheet 1 and process evyhting in Sheet 2 making sure that the sheets are both proteced after the run.
    Just one question, can anybody run this macro or is it just a user with extra authorization?
    You could put a passwrod authorization in between. It';s Simple.
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Quick and dirty have to leave now

    Let me know
     

    Attached Files:

  11. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I added the password module for you to test too.

    Like I said before, I like challenges and just ask, if I don't know the answer this forum has may geniuses :cool: so I'm sure anywone can come up with handy tips and solutions (y)
     

    Attached Files:

  12. costagin

    costagin Thread Starter

    Joined:
    Apr 15, 2010
    Messages:
    8
    Perfect! It does exactly what I needed. Thank you very much for all your time and effort.
     
  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I'm not that good at adding comments in the code explaining my steps but if you need some info there please let me know.
    One place to get vba info is the VBA project itself, once the vba editor is opened there is a button Project explorer, choose for vba and all the functions are shown, right click on each function and choose help will give you a screen with syntax instructions and simple examples, that way you get the idea and imagination and fantasy will do the rest.
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/918637

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice