Why does MS Excel VBA code work fine if I manually select (am looking at) the affected sheet--but not if any other sheet has the focus?

[RafFiniert]

With Worksheets("MapLookup_RO")

For c1 = 1 To c1end
Range("F4:F4").Copy Range("F" & Rows.Count).End(xlUp).Offset(1, 0)
Next c1

Application.Wait (Now + TimeValue("0:00:02"))
ActiveWorkbook.Save

End With


If I manually click on the MapLookup_RO worksheet, I can watch the code work as expected. However, if I manually click on any other worksheet and then run the code, it does not execute the copy command.

For modularity's sake, the code above is in a subroutine called from another subroutine. (The code is not "behind" the worksheet.)

 

[Joe4]

Welcome to the Board!

If your ranges do not have sheet qualifiers, they will always default to the Active Sheet at the point when it hits the code.

It looks like you are trying to use "With" to specify the sheet, but that only works if you put a period in front of all the range references within that With clause, to show that range belongs to the sheet named in the With line, and not to the Active Sheet, i.e.

With Worksheets("MapLookup_RO")

    For c1 = 1 To c1end
        .Range("F4:F4").Copy .Range("F" & .Rows.Count).End(xlUp).Offset(1, 0)
    Next c1

    Application.Wait (Now + TimeValue("0:00:02"))
    ActiveWorkbook.Save

End With

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

https://stackoverflow.com/staging-ground/79518356

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[RafFiniert]

Thank you--I will try that.