Is it possible with VBA to make A1 the active cell in a sheet without activating the sheet?

Is it possible with VBA to make A1 the active cell in a sheet without activating the sheet? If so, how? I have a macro that copies and pastes charts into multiple sheets in an external workbook, and then activates the external book. I would like to deselect the pasted charts just because I don't like the way that looks when I select the sheet for further work later.

John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
You have to at least select the sheet unless it's already the active sheet.

Can you use something like this? It will save the current location, then activate the desired sheet (replace Sheet1 with your sheet name) then select A1, after which you can do what you need to do, then it will restore the original cell.
If you're already on the correct sheet, you can skip the activation of that.

    Dim SaveCell As Range
    Dim SaveSheet As Worksheet
    Set SaveSheet = ActiveSheet
    Set SaveCell = ActiveCell
    ActiveCell.Value = "abc"

Open in new window

The code below follows more or less the same approach as rspahitz but it is also saving the workbook and it does set screenupdating to false during the select.
Application.ScreenUpdating = False

Dim oActiveCell As Range
Dim oActiveWorksheet As Worksheet
Dim oActiveWorkbook As Workbook

Set oActiveCell = ActiveCell
Set oActiveWorksheet = ActiveSheet
Set oActiveWorkbook = ActiveWorkbook



Application.ScreenUpdating = True

Open in new window

OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

You have two options:
1. Do the copy and paste in one line that way the chart  wont be selected in the first place. i.e.
rngCopyRange.Copy Destination:= rngPasteRange

Open in new window

 2. Running the following after the copy:
Application.CutCopyMode = False

Open in new window

John CarneyReliability Business Tools Analyst IIAuthor Commented:
That's intriguing, MacroShadow. How would i incorporate this into my code below? The last line pastes the chart where I want it without activating Workbooks(wb), but it pastes it as a picture, which doesn't look as good aesthetically as the actual chart does. I couldn't find any PasteSpecial option for pasting objects.
        ActiveSheet.ChartObjects("Chart 2").Activate
        Set targCel = Workbooks(wb).Sheets(ShNm).Range("C3")
        targCel.PasteSpecial xlPasteAll

Open in new window

In any event, assuming that the topLeftCell of my chart is B2 (if that's relevant), how would your code work with my code?

ActiveSheet.ChartObjects("Chart 2").Copy Destination:= Workbooks(wb).Sheets(ShNm).Range("C3")

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Hi MacroShadow, I tried it but I get an error. Let me work with it a little more when I have more time and I'll get back to you.

John CarneyReliability Business Tools Analyst IIAuthor Commented:
I haven't been able to make anything work but I want to reward you all for posting answers and close out the question.   Thanks!  John
Martin LissOlder than dirtCommented:
If you can show us the code that you used and describe what's wrong with the results (if any) maybe one of us can help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.