CODEWITHSUNDEEP
excelvba
w0rksh0p
w0rksh0p

Reputation: 11

Excel Worksheets

I have 2 worksheets, ws1 and ws2. This is the vba. It will go to ws2 and then ws1. I just want to see ws1 while ws2 does the copying job, and I do not want to juggle between ws1 and ws2.

How should I correct the vba?

Sheets("ws2").Select
Range("A3:AA3").Select
Selection.Copy
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("A4").Select
Application.CutCopyMode = False
Range("A4:AA4").Select
Selection.Insert Shift:=xlDown
Sheets("ws1").Select

Upvotes: 0

Views: 389

Answers (3)

Scott Conover
Scott Conover

Reputation: 1421

A forum entry on ozgrid provides a simple and elegant solution 

Sub NameOfSub() 

    Application.ScreenUpdating = False

    'Some Code Here

    Application.ScreenUpdating = True 

End Sub

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149335

The juggling is happening because you are telling excel to do the juggling :) And hence I always recommend not to use .SELECT/.ACTIVATE.

Directly perform the action. See this.

Sub Sample()
    Dim ws As Worksheet

    Set ws = Sheets("ws2")

    With ws
        .Range("A3:AA3").Copy
        .Range("A4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        .Range("A4:AA4").Insert Shift:=xlDown
    End With
End Sub

Upvotes: 2

BonyT
BonyT

Reputation: 10940

Use 

   Application.ScreenUpdating = False

before you run the above code to stop viewing what your macro is doing.

so:

Sheets("ws1").Select
Application.ScreenUpdating = False
Range("A3:AA3").Select
.... etc

Application.ScreenUpdating = True

Upvotes: 0

Related Questions