write this code more efficiently

  • Hello everyone.
    I'm fairly certain there's a faster way to do this. Much like previous posts very new to vba, just trying to learn how to do stuff in a more efficient manner. Created this to clear a financial sheet for one month, to get started on the next fiscal year. The three sets past the original set are offset to clear the remaining weeks in the month. (only 4 weeks in this month of the period.) Everything is 7 columns past the previous week, which is why the offset(0, 7). The offset(0, -4) is to move actual from this year to prior year in what will be next year's setup...

    Attached is a dummy month
    Thanks in advance for any help.

  • Re: write this code more efficiently

    you macro is just copying certain ranes and also clearing contents of some constants.
    for copying you need not select either what is to be copied or where to copiy nor for clearing conteants.
    I am giving the modifed code statement for the first half and I hope you will be able to use this for rewritig the macro
    first. try it. good exercise.

    I am not an expert. better solutions may be available. $$$venkat$$$1926@$$$gmail.com

  • Re: write this code more efficiently

    Thank you very much. This is exactly what I was wanting to know. I thought it was redundant to select something, then take that selection again and copy, etc... but when I tried shortening it I ran into some errors.

    Again, thank you very much.

  • Re: write this code more efficiently

    When I use the revised code, it is currently pasting formulas and constants, not just pasting values. Any idea why?

    Original code had it pasting the value from the actual range into the offset area of 4 columns over.


  • Re: write this code more efficiently

    Because it's using PASTE rather than PASTESPECIAL

    '    actual.Select
         '    Selection.Copy
        actual.Copy actual.Offset(0, -4)

    The Copy & Paste operations are combined into 1 line.

    Unfortunately, PasteSpecial cannot be used on 1 line like that so you will have to split those out again.

        actual.Offset(0, -4).PasteSpecial Paste:=xlValues
  • Re: write this code more efficiently

    Thanks again for all the help. Just got the code working for an entire year, so our controller can fix about 20 locations in roughly 10 minutes instead of having to copy/paste etc for the entire sheet for all those locations... Still a bit lengthy, but thanks to Cytop and Venkat's help it effectively cut the code by more than half from what it would've been...

    And it is giving me a better understanding of how to use vba, which is great.
    Thanks to you both, again.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!