I need to activate a cell within a macro and am having difficulty

[Jeff Smith]

I have a workbook with the following routine located in "ThisWorkbook".

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.Goto Sh.Range("A1"), True
End Sub

There are about 20 worksheets and it is most desirable that when moving into
a worksheet, the cursor always locates at cell A1.

However, there is one worksheet where a macro opens a SheetVeryHidden where
I store important data.

Some time ago, I found I could not deposit data in any cell in this
SheetVeryHidden other than in cell A1. I could not resolve this so simply
used a cell elsewhere in this worksheet to refer to the data stored in A1
and this works well.

However, I now have a need to store additional data in this worksheet and
elected to use cells B1:E1 (being next to A1). I found I could not get the
cursor to move from A1 under any macro.

I would prefer to use A22 for this data. I cannot overwrite the data
stored in A1 with this new data in A11 (where I can easily deposit the
data - but it overwrites the previous contents of A1)

For this section of the macro to sheet named "Data" I have the following:

Sheets("Data").Visible = xlSheetVisible
Sheets("Data").Select
ActiveSheet.Unprotect Password:="password"
? SendKeys "{DOWN}"
? SendKeys "{DOWN}"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
' Range("A33").Select
Selection.Copy

Before resorting to using the SendKeys {DOWN} (which will not work - it
generates an error message) I previous simply used Range("A3").Select, which
also didn't work.

Can anyone see where I am going wrong?

Thanking you in anticipation,

sincerely

Jeff Smith

 

[Frank Kabel]

Hi Jeff
in most cases it is not required to use select or move the cursor. Also
I'm not sure which selection you want to copy.
In your case maybe try the following:

sub foo()
Dim target_wks as worksheet
Dim source_wks as worksheet

set source_wks=Activesheet
set target_wks = worksheets("Data")
target_wks.Visible = xlSheetVisible
target_wks.Unprotect Password:="password"

target_wks.range("A3").value = source_wks.range("A5").value
'or something similar
'protect and hide source_wks again
end sub

 

[Jeff Smith]

This solution has worked. I very much appreciate you taking the time to
respond to this query. I had previously spent many hours trying to solve
this.

regards

Jeff Smith

 

[Frank Kabel]

Hi
thanks for your feedback