Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Return to prior worksheet after sub 1

Status
Not open for further replies.

Whitoja

Programmer
Jul 28, 2010
4
US
I have VBA code to sort a column of names in whichever worksheet I have up, not specified by Sheets("xxx").Select. I would like to imbed a sub that selects a specific worksheet where info may be copied and used on the original worksheet without having to specify the original worksheet.

Restating,I would would like to be able to go to any worksheet in my workbook and perform a sorting operation and then go to a specified worksheet and copy information there and return to the prior worksheet to use that copied information.

Any help would be much appreciated.
Tommy
 
As a start (you may need to give a worked example btw - your post is a little confusing)

The sheet you start off from can be captured via either

Dim sht as worksheet

Set sht = activesheet

OR

Dim strSht as string

strSht = activesheet.name

you can then use

sht.select

OR

Sheets(strSht).select

to go back to the sheet you started from

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Hello Whitoja!

I can see you are new to this forum and I would suggest that you read the FAQ on how to post a complete question! This would help the greatly experienced programmers in here narrow down your problems and send you on the best path for a solution.

Its definatly worth it and its quite informative. For your problem, I don't really understand why you need to do this, and exactly what you are trying to achieve.

when you say "use that copied information" and you say "prior worksheet" is the prior worksheet not the worksheet you copied the information from in the first place? If so, why do you need to copy it in another worksheet, is it to that you keep a record of untampered data?

Although I am not the best! I have the time to help you

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
 
Or you can definatly just have xlbo help you.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
 
It is like anything else. If you want go somewhere, do something, then that "thing" requires a something that identifies it.

So either use a declared object SET to that "thing" (in this case a worksheet): this is xlbo's
Code:
Dim sht as worksheet

Set sht = activesheet

OR, get the string name of that "thing" and use the string: this is xlbo's code:
Code:
Dim strSht as string

strSht = activesheet.name
If you are going to using (getting other information about/from) that "thing", more than once, I would recommend using objects.

Gerry
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top