Help with shortening code

I am new to Visual Basic and was hoping someone could help me shorten this code. The part that seems too long is the Copy and PasteSpecial portion (Bold). Can this be shortened any? As you can probably tell I recorded some of this macro and wrote the rest. This is only a small part of a very large marcro, which is why I want to shorten it. A variation of the code I posted repeats several times. Thanks for any help.


Sub Finish_01()

If Range("I7") = "" Then
Range("I7").Formula = Range("E2") + Range("F2")
Range("I7").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B2:C2").Select
Application.CutCopyMode = False
Selection.Copy
Range("G7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False Range("A1").Select
ElseIf Range("L7") = "" Then ........

............ End Sub

Originally Posted by CJ-22

I am new to Visual Basic and was hoping someone could help me shorten this code. The part that seems too long is the Copy and PasteSpecial portion (Bold). Can this be shortened any? As you can probably tell I recorded some of this macro and wrote the rest. This is only a small part of a very large marcro, which is why I want to shorten it. A variation of the code I posted repeats several times. Thanks for any help.


Sub Finish_01()

If Range("I7") = "" Then
Range("I7").Formula = Range("E2") + Range("F2")
Range("I7").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B2:C2").Select
Application.CutCopyMode = False
Selection.Copy
Range("G7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False Range("A1").Select
ElseIf Range("L7") = "" Then ........

............ End Sub

Hi,

For starters you could use

Range("I7").Copy
Range("I7").PasteSpecial(xlPasteValues)
Range("B2:C2").Copy
Range("G7").PasteSpecial(xlPasteValues)

As always it's a trade off. If there are only two or three Copy..Paste operations, then it's probably not worth the overhead of having a loop to handle them. If however there are several then you do need to incorporate these either in a loop, or by calling a separate Copy Paste subroutine and passing the variables to it.

So for instance at the position where you want to start a copy and paste, and using the example above, you'd introduce the following line:

Call CopyPaste(Range("I7"),Range("I7")) '

This would call the following Procedure..untested

Sub CopyPaste(CopyCell As Range, DestinationCell As Range)
     CopyCell.Copy
     DestinationCell.PasteSpecial(xlPasteValues)

End Sub

If you want to use a loop then there'd need to be some algorithm for working out how the copy and pasted cells change. i.e. is there any relationship both between them, and also between each occurrence?

If there is post back and we'll see what we can come up with. However I suspect the 'Call CopyPaste()' process is going to be the best and simplest way.

Rgds

When I get home from work today I will try some of these. If they don't work, I will post again. Thank you.