write this code more efficiently

Rowddawg · Sep 24th 2013

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.

Code

Sub ResetOctober()
'
' Reset October
'
'
    Dim actual As Range
    Dim projection As Range
        
    Set actual = Range("f6:f26")
    Set projection = Range("d6:d26")
    
    
    On Error Resume Next
    actual.Select
    Selection.Copy
    Selection.Offset(0, -4).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    projection.Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
    actual.Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
        
    actual.Select
    Selection.Offset(0, 7).Select
    Selection.Copy
    Selection.Offset(0, -4).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    projection.Select
    Selection.Offset(0, 7).Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
    actual.Select
    Selection.Offset(0, 7).Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
        
    actual.Select
    Selection.Offset(0, 14).Select
    Selection.Copy
    Selection.Offset(0, -4).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    projection.Select
    Selection.Offset(0, 14).Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
    actual.Select
    Selection.Offset(0, 14).Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
        
    actual.Select
    Selection.Offset(0, 21).Select
    Selection.Copy
    Selection.Offset(0, -4).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        projection.Select
    Selection.Offset(0, 21).Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
    actual.Select
    Selection.Offset(0, 21).Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
    
    Range("a1").Select
    
    
End Sub

Display More

venkat1926 · Sep 25th 2013

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.

Code

Sub ResetOctober()
     '
     ' Reset October
     '
     '
    Dim actual As Range
    Dim projection As Range
     
    Set actual = Range("f6:f26")
    'Set projection = Range("d6:d26")
     Set projection = actual.Offset(0, -2)
     
    On Error Resume Next
'    actual.Select
'    Selection.Copy
    actual.Copy actual.Offset(0, -4)
    
    
'    Selection.Offset(0, -4).Select
'    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
'    :=False, Transpose:=False
'
    
    
''    projection.Select
''    Selection.SpecialCells(xlCellTypeConstants, 1).Select
''    Selection.ClearContents


projection.SpecialCells(xlCellTypeConstants, 1).ClearContents
'    actual.Select
'    Selection.SpecialCells(xlCellTypeConstants, 1).Select
'    Selection.ClearContents
     actual.SpecialCells(xlCellTypeConstants, 1).ClearContents

Display More

Rowddawg · Sep 25th 2013

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.

Rowddawg · Sep 25th 2013

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.

Thanks.

**cytop** · Sep 25th 2013

Re: write this code more efficiently

Because it's using PASTE rather than PASTESPECIAL

Code

'    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.

Code

actual.Copy 
    actual.Offset(0, -4).PasteSpecial Paste:=xlValues

Rowddawg · Sep 25th 2013

Re: write this code more efficiently

Thanks Cytop. I'll adjust that now and keep messing with it.

Rowddawg · Sep 25th 2013

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.

write this code more efficiently

Participate now!

Share