Using With to speed up macro

[Wesley]

I am trying to speed up my macro by using With, but the With doesn't seem to be working. Can someone give me an example of how to use With with my code? I have the following chunk of code that repeats for different variables

If var1 = 1 The
Range("Range").Offset(Count + 1, 0).Selec
Selection.Cop
ActiveCell.Offset(0, 1).Activat
Workbooks(Book1).Activat
ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
False, Transpose:=Fals
Application.CutCopyMode = Fals
End I
Workbooks(Book1).Activat
ActiveCell.Offset(1, 0).Activat
Workbooks(Book2).Activat

Thanks for the help
Wesle

 

[J.E. McGimpsey]

It's a bit difficult, since With...End With won't speed up your
macro nearly as much as eliminating all the Selections and
Activates, and eliminating the copy-via-the-clipboard.

Eliminating all the selections:

Workbooks(Book1).Sheets(1).Cells(n, 1).Value = _
Workbooks(Book2).Range("Range").Offset(Count + 1, 0).Value
n = n + 1
Count = Count + 1

 

[Leo Elbertse]

While you've already got the solution to speed things up, I get the
impression that you don't really understand what With does:

With is not used to repeat code frequently but to repeatedly work with
the same area of your sheet. And since it doesn't use select and / or
activate it actually does speed up things tremendously, exactly for
the reasons given in the other post.

To give an example of how it's used:

With Range("Range")
' don't forget the period before Offset
.Offset(Count + 1).Value = xxx
.Offset(Count + 2).Value = yyy
' or simply make a change to the range itself
.Value = zzz
With .Font
.ColorIndex = 2
.Bold = True
End With
.Borders(xlEdgeBottom).LineStyle = xlDouble
End With

Leo