CODEWITHSUNDEEP
vbaexcel
NinjaWarrior
NinjaWarrior

Reputation: 25

Excel hangs when runs VBA in Module

Excel rookie here. I'm trying a very simple task: copying and pasting cells. The Excel will go into "non responding" once the VBA runs. The code was running at one point, but as I developed more lines, Excel stopped responding. I comment out the rest of the line and only run the lines shown below, Excel still hangs. Any ideas? Thanks!

Also, I'm writing the codes in the module in Excel.

Sub EDRII()
Application.ScreenUpdating = False

Dim EDR As Worksheet, Lookup As Worksheet, FA As Worksheet
Set EDR = Sheets("for edr II")
Set Lookup = Sheets("Lookup")
Set FA = Sheets("FA_Segment_Region")

Sheets(EDR).Activate
Range("B6:X10").Copy Range("B5:X9")

Application.ScreenUpdating = True
End Sub

Excel xlsm

Upvotes: 1

Views: 128

Answers (2)

CallumDA
CallumDA

Reputation: 12113

Here's some pointers:

  1. Indent your code so it's easier to follow (minor point in your example)
  2. Fully qualify your references (ThisWorkbook.Worksheets(), EDR.Range(), etc)
  3. Rather than copying and pasting, use .Value

Your code was breaking down because you were trying to activate EDR with Sheets(EDR).Activate which should just be EDR.Activate. Either way, when you take into account the points above you see that you don't need to Activate anything anyway!

Sub EDRII()
    Application.ScreenUpdating = False

    Dim EDR As Worksheet, Lookup As Worksheet, FA As Worksheet
    Set EDR = ThisWorkbook.Worksheets("for edr II")
    Set Lookup = ThisWorkbook.Sheets("Lookup")
    Set FA = ThisWorkbook.Sheets("FA_Segment_Region")

    EDR.Range("B5:X9").Value = EDR.Range("B6:X10").Value

    Application.ScreenUpdating = True
End Sub

Upvotes: 1

YowE3K
YowE3K

Reputation: 23994

You have defined EDR to be a Worksheet and assigned it to be a reference to the sheet called "for edr II".

When you try to use it later, in the line saying

Sheets(EDR).Activate

you are using EDR in a spot where VBA is expecting a String or an Integer (or Long).

That line should simply say

EDR.Activate

It's also a good idea to avoid Activate and Select whenever possible so, rather than activating the sheet, you could just use

EDR.Range("B6:X10").Copy EDR.Range("B5:X9")

for the Copy statement.

Upvotes: 2

Related Questions