activate worksheet

[another rachel]

Hello
I have started writing (well, to be honest, slightly adjusting a macro that was previously posted) to compare two sets of data - and have asked the user to select the two worksheets that are to be compared.
My code defines them as:
currentdata
newdata
After completing tasks in both spreadsheets I need to make sure that the currentdata worksheet is the active worksheet.
My attempts so far...
worksheet (currentdata).activate
workbook (currentdata).activate
have failed miserably and any assistance/hits/ideas/suggestions would be most appreciated.
Thank you.

 

[Joe4]

If you are talking about two sheets in the same file, it would be:

Sheets("name").Select

If you are talking about two different files, it would be:

Windows("name.ext").Activate

Make sure you include the quotes, and the ".xls" extension on the file name.

 

[another rachel]

Thanks for the fast resoponse...
So, now...
Does that mean even if I have assigned the variables
currentdata
newdata
to the actual file name I still need to name the .xls file name in the command to active the worksheet?

If so, how do I do that?
I have given that I have asked the user to identify the relevanvt worksheet using the command Application.GetOpenFilename which means that the file name I wish to activate may change each time the macro is run.

Thanks for your help

 

[Joe4]

If you are assigning the file names to variables "currentdata" and "newdata", then no, you shouldn't need the extension or the quotes around the names.

If you are still having problems, send me the code where you are defining “currentdata” and “newdata”.

 

[another rachel]

Yes I am still exerpeincing problems...
Any suggestions?
Thanks for your help


My code reads:

Dim originaldata

(other code)

'ask user to confirm that the active work sheet contains the original version of data
originaldata = MsgBox("does this worksheet contain the original version of data?", vbYesNo)
If originaldata = vbYes Then
MsgBox ("thank you")
Else
'user selects appropriate file and macro continues to calculate there
originaldata = Application.GetOpenFilename("all files (*.*),*.*")
MsgBox ("please confirm that you would like to open the file " & originaldata)
Workbooks.Open (originaldata)
End If

(other code)

Windows(originaldata).Activate
Sheets("SHEET1").Select

 

[Joe4]

OK, there are two problems with the code. One is if you answer "Yes", originaldata = VBYes, so Windows(originaldata).Activate will give you an error. If you answer "No", you will get an error because Windows(filename).Activate wants only the file name, and not the full path, which is what GetOpenFileName gives you. Try the following code:

*************************************

Dim originaldata

''(other code)

'ask user to confirm that the active work sheet contains the original version of data
originaldata = MsgBox("does this worksheet contain the original version of data?", vbYesNo)
If originaldata = vbYes Then
MsgBox ("thank you")
originaldata2 = ActiveWorkbook.Name
Else
'user selects appropriate file and macro continues to calculate there
originaldata = Application.GetOpenFilename("all files (*.*),*.*")
MsgBox ("please confirm that you would like to open the file " & originaldata)
Workbooks.Open (originaldata)
originaldata2 = ActiveWorkbook.Name
End If

''(other code)

Windows(originaldata2).Activate
Sheets("SHEET1").Select

**********************************

It works on my computer.

 

[another rachel]

Aha, it is all much clearer now.
Thank you

My macro now runs from start to finish - it does not actually do what I need it to do -but that is a whole nother issue!