activate worksheet

another rachel

Board Regular
Joined
May 27, 2002
Messages
80
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,444
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 27, 2002
Messages
80
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,444
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 27, 2002
Messages
80

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,444
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 27, 2002
Messages
80
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!
 

Forum statistics

Threads
1,144,050
Messages
5,722,229
Members
422,417
Latest member
Johhny

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top