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

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



In article ,
Wesley wrote:

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 Then
Range("Range").Offset(Count + 1, 0).Select
Selection.Copy
ActiveCell.Offset(0, 1).Activate
Workbooks(Book1).Activate
ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End If
Workbooks(Book1).Activate
ActiveCell.Offset(1, 0).Activate
Workbooks(Book2).Activate

Thanks for the help,
Wesley

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

On Mon, 29 Dec 2003 14:31:07 -0800, Wesley
wrote:

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 Then
Range("Range").Offset(Count + 1, 0).Select
Selection.Copy
ActiveCell.Offset(0, 1).Activate
Workbooks(Book1).Activate
ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End If
Workbooks(Book1).Activate
ActiveCell.Offset(1, 0).Activate
Workbooks(Book2).Activate

Thanks for the help,
Wesley