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.

Thanks!
John
LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
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.

    Sheets("Sheet2").Select
    Sheets("Sheet2").Range("A1").Select
0
rspahitzCommented:
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
    Sheets("Sheet1").Activate
    Range("A1").Select
    ActiveCell.Value = "abc"
    SaveSheet.Activate
    SaveCell.Activate

Open in new window

0
zalazarCommented:
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

Sheets("Sheet1").Activate
Range("A1").Select

oActiveWorkbook.Activate
oActiveWorksheet.Activate
oActiveCell.Activate

Application.ScreenUpdating = True

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

MacroShadowCommented:
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

0
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")
        ActiveChart.ChartArea.Copy
        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?

Thanks,
John
0
MacroShadowCommented:
Try
ActiveSheet.ChartObjects("Chart 2").Copy Destination:= Workbooks(wb).Sheets(ShNm).Range("C3")

Open in new window

0

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.

Thanks,
John
0
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
0
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.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.