Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Concantenation in Sheets(____________).Select

  Asked By: Nicole    Date: Nov 28    Category: MS Office    Views: 603
  

I was wondering if anyone could give me some guidance on what I'm doing
wrong with my concantenated sheets(___________).select statement as
described below:

I'm using an OnTime event to run my macro throughout the day.
Because I could be working on one of many sheets prior to the execution,
I have a named cell in my "control" sheet where I can enter the sheet
name where i want excel to return to after the OnTime macro process is
complete

I have the following elements built in my excel macro coding:

Dim ReturnToSheetNamed As Variant

Application.Goto Reference:="User_Input_Return_to_Sheet_Named"
ReturnToSheetNamed = ActiveCell.Value

The final VBA statements I installed into my macro are:

If ReturnToSheetNamed <> "" Then
Sheets("&ReturnToFocusListsSheetIndicated&").Select
End If

the problem is in the sheets(_________).select statement
but I'm not sure what I'm doing wrong here

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Edwin Chavez     Answered On: Nov 28

As you already have a string you don't use the quotes

Sheets(ReturnToFocusListsSheetIndicated).Select

I would also make the Dim "As String"

 
Answer #2    Answered By: Burk Martin     Answered On: Nov 28

regarding:
Application. Goto Reference:=" User_Input_ Return_to_ Sheet_Named"
ReturnToSheetNamed = ActiveCell.Value

try:
ReturnToSheetIndex=activesheet.index


the return  call would look like this:
sheets(ReturnToSheetIndex).select

 
Didn't find what you were looking for? Find more on Concantenation in Sheets(____________).Select Or get search suggestion and latest updates.