2

So I have read online in many different places that I should not use .activate or commands like it. problem is my program HEAVILY relies on them.

Let me give some background first. I am writing a program to automate a part of my job. I have already written this particular portion of code months ago, and it worked perfectly well. However now that I am trying to update the code things that use to work are causing errors. namely the instances of ActiveCell.PasteSpecial. I read that it is the .activate that is causing the problem. Why is this only causing a problem now?

As for the main question I need my code to do 4 things

  1. get a account number from a specific cell.
  2. activate "sheet2" paste text date copied from a outside Program to "A1" and collect text data from different cells on "sheet2" based on the pasted data.
  3. activate "sheet1" paste data collected and receive the next account number.
  4. Switch between Excel and another program that only accepts keyboard entry. While this step is annoying it is not the step I need help on.

Here is the code I have so far. I know how to do most of the steps I am just lost when trying to get it done without .activate. 

 Sub Macro1() ' ' Macro1 Macro ' ' 'find missing emails Dim e As Range, Rang As Range Set Rang = Range("A2:A100") AppActivate "Microsoft Excel" Worksheets("Email_List").Activate Range("A1").Activate For Each e In Rang If Not IsEmpty(e.Value) = True Then ActiveCell.Offset(1, 0).Activate Sleep 700 ActiveCell.Offset(0, 3).Activate Sleep 700 If IsEmpty(ActiveCell.Value) Then ActiveCell.Offset(0, -3).Activate Sleep 700 ActiveCell.Copy Sleep 700 AppActivate "Other Program" Sleep 500 SendKeys "~", True Sleep 700 SendKeys "~", True Sleep 700 SendKeys "~", True Sleep 700 SendKeys "~", True Sleep 700 SendKeys "~", True Sleep 700 SendKeys "~", True Sleep 700 SendKeys "~", True Sleep 700 SendKeys "1", True Sleep 700 SendKeys "~", True Sleep 700 SendKeys "2", True Sleep 700 SendKeys "~", True Sleep 700 SendKeys "1", True Sleep 700 SendKeys "~", True Sleep 700 SendKeys "c ", True Sleep 700 SendKeys "^v", True Sleep 7001 SendKeys "^x", True Sleep 7000 SendKeys "^a", True Sleep 7000 SendKeys "^c", True Sleep 7000 AppActivate "Microsoft Excel" Sleep 500 Worksheets("Data").Activate Cells.Activate Cells.Delete Range("A1").Activate ActiveCell.PasteSpecial Sleep 500 If Range("A24").Value = "CONF# NOT FOUND, PRESS <ENTER>" Then Sleep 700 AppActivate "Other Program" Sleep 500 SendKeys "~", True Sleep 700 AppActivate "Microsoft Excel" Sleep 500 Worksheets("Email_List").Activate ElseIf Range("A24").Value = "ENTER RESERVATION NUMBER:" Then Range("D24").Activate ActiveCell.Value = "=LEFT(A6,6)" ActiveCell.Copy AppActivate "Other Program" Sleep 500 SendKeys "^v", True Sleep 700 SendKeys "30", True Sleep 700 SendKeys "~", True Sleep 700 SendKeys "^x", True Sleep 700 SendKeys "^a", True Sleep 700 SendKeys "^c", True Sleep 700 AppActivate "Microsoft Excel" Sleep 500 Worksheets("Data").Activate Cells.Activate Cells.Delete Range("A1").Activate ActiveCell.PasteSpecial Sleep 500 If Range("A8").Value = "3. E-FOLIO" Then Sleep 700 AppActivate "Other Program" Sleep 500 SendKeys ("3") Sleep 700 SendKeys ("~") Sleep 700 SendKeys "^x", True Sleep 700 SendKeys "^a", True Sleep 700 SendKeys "^c", True Sleep 700 AppActivate "Microsoft Excel" Sleep 500 Worksheets("Data").Activate Cells.Activate Cells.Delete Range("A1").Activate ActiveCell.PasteSpecial Sleep 700 Range("A21").Copy Worksheets("Email_List").Activate ActiveCell.Offset(0, 3).Activate ActiveCell.PasteSpecial ActiveCell.Offset(0, -3).Activate End If ElseIf Range("A2").Value = "===============================================================================" Then AppActivate "Other Program" Sleep 500 SendKeys "30", True Sleep 700 SendKeys "~", True Sleep 700 SendKeys "^x", True Sleep 700 SendKeys "^a", True Sleep 700 SendKeys "^c", True Sleep 700 AppActivate "Microsoft Excel" Sleep 500 Worksheets("Data").Activate Cells.Activate Cells.Delete Range("A1").Activate ActiveCell.PasteSpecial Sleep 500 If Range("A8").Value = "3. E-FOLIO" Then Sleep 700 AppActivate "Other Program" Sleep 500 SendKeys ("3") Sleep 700 SendKeys ("~") Sleep 700 SendKeys "^x", True Sleep 700 SendKeys "^a", True Sleep 700 SendKeys "^c", True Sleep 700 AppActivate "Microsoft Excel" Sleep 500 Worksheets("Data").Activate Cells.Activate Cells.Delete Range("A1").Activate ActiveCell.PasteSpecial Sleep 500 Range("A21").Copy Worksheets("Email_List").Activate ActiveCell.Offset(0, 3).Activate ActiveCell.PasteSpecial ActiveCell.Offset(0, -3).Activate End If End If Else ActiveCell.Offset(0, -3).Activate End If End If Next e End Sub

Any help you can provide will be greatly appreciated.

Improve this question
6
  • 1
    I'm voting to close this question as off-topic because this question should be asked on Code Review Commented Nov 3, 2016 at 12:50
  • I will re-post this question there then. Thank you for directing me to the right place. Commented Nov 3, 2016 at 12:55
  • Can't wait to review it on CR - see you on the other side! :-) Commented Nov 3, 2016 at 13:20
  • Good cause I have a lot to learn about VBA, and coding in general. Commented Nov 3, 2016 at 13:30
  • Argh, I misread the question - you need to first get the code to work as intended, then it can be peer reviewed on CR. I suggest you edit this post to ask specifically about the error you're getting, and only include the code that's needed to reproduce the problem - see minimal reproducible example. Commented Nov 3, 2016 at 13:46

1 Answer 1

Reset to default
2

Start here:

Worksheets("Email_List").Activate Range("A1").Activate

You have a worksheet in ThisWorkbook that's labelled "Email_List". Its actual (Name) property might be Sheet12; hit Ctrl+R to bring up the Project Explorer, then select the "Email_List" sheet under the "Microsoft Excel Objects" folder, and hit F4 to bring up the Properties toolwindow. Locate the (Name) property (should be the very first on top) and change Sheet12 (or whatever) to EmailListSheet.

Now back to your code, you no longer need to find the worksheet - you have a reference to it already.

EmailListSheet.Range("A1").Activate

Will do exactly the same thing as this:

Worksheets("Email_List").Activate Range("A1").Activate

But you don't want to .Activate the range. Rather, you want to keep a reference to it. Declare a Range variable:

Dim workingRange As Range Set workingRange = EmailListSheet.Range("A1")

Now instead of this:

 ActiveCell.Offset(1, 0).Activate Sleep 700 ActiveCell.Offset(0, 3).Activate Sleep 700

You can do that:

Set workingRange = workingRange.Offset(1, 3)

(no need to sleep here)

Next you want to see if that cell is empty:

If IsEmpty(ActiveCell.Value) Then

So you'll just do this:

If IsEmpty(workingRange.Value) Then

Then you're copying another cell like this:

ActiveCell.Offset(0, -3).Activate Sleep 700 ActiveCell.Copy

Not sure what's up with all that sleeping, but anyway you'll be doing this instead:

workingRange.Offset(0, -3).Copy

Cue SendKeys, paste into the other application, and copy from that other application, and we get to the part that blows up:

Worksheets("Data").Activate Cells.Activate Cells.Delete Range("A1").Activate ActiveCell.PasteSpecial

Same deal: name that worksheet DataSheet and work off a reference to that object.

DataSheet.UsedRange.Clear DataSheet.Range("A1").PasteSpecial

The rest is just more of the same.

Key points:

  • Name your worksheets and use the global reference you get for free, instead of fetching all sheets from the Worksheets collection every time you need it.
  • Avoid calling Range and Cells without qualifying them with a Worksheet object, e.g. DataSheet.Range. When they're not qualified, these members implicitly refer to the ActiveSheet, which is what you want to avoid here.
  • Once you get your code to work as intended, edit the revised code into your Code Review question to get that post reopened and your new working code peer reviewed and further improved.
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.