Excel Worksheets

Question

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

Welcome to StackOverflow :) Instead of updating the question to let us know that you have got your answer, you can either post the final answer that you arrived at or select any of the answer that helped you with your query. :) — Siddharth Rout, Jul 26 '12 at 14:45

score 2 · Answer 1 · answered Jul 26 '12 at 14:17

2

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

answered Jul 26 '12 at 14:17

Siddharth Rout

147,039
17
206
250

+50 for not selecting or activating, if I could. See Chris Nielsen's excellent answer for ideas on how to write your code without selecting/activating. http://stackoverflow.com/a/10717999/138938 – Jon Crowell Jul 26 '12 at 15:11
1

Yes. I have a post in that link too :P – Siddharth Rout Jul 26 '12 at 15:12
No in your case you do not need to. Simply use the baove code as it is :) – Siddharth Rout Aug 02 '12 at 14:43

score 0 · Answer 2 · answered Jul 26 '12 at 14:09

0

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

answered Jul 26 '12 at 14:09

BonyT

10,750
5
31
52

score 0 · Answer 3 · edited Aug 03 '12 at 12:16

0

A forum entry on ozgrid provides a simple and elegant solution

Sub NameOfSub() 

    Application.ScreenUpdating = False

    'Some Code Here

    Application.ScreenUpdating = True 

End Sub

edited Aug 03 '12 at 12:16

mmmmmm

32,227
27
88
117

answered Jul 26 '12 at 14:14

Scott Conover

1,421
1
14
27

bigresource.com is some kind of auto-generated doorway scraper site that just redirects you while showing ads. If you can, you should post a link to the "real" content. – JimmyPena Jul 26 '12 at 14:54
Whoops! Folly on my part, copied the wrong link and used my path rather than the destination. Thanks JimmyPena. – Scott Conover Jul 26 '12 at 14:57

Excel Worksheets

3 Answers3