Select last sheet that had focus

Internoob2,

Hate to say it, but all those .Select statements are making my eyes bleed. There's pretty much never a need to select anything in a macro. You can have things happen on other sheets without ever leaving the active sheet. For example, your code can be simplified to one line:

Please Login or Register  to view this content.

That will clear 'Multiple paste Values'!A2:A8 without leaving the active sheet (which I'm guessing is 'Data Sheet').

~tigeravatar

Ah, I see, thanks.

That is why I am internoobiate level. I know just enough to cause damage.

I was thinking about vba code I have used in Access (to close the last form you came from).

Thank you,
-BC

For some reason I do not seem able to change the selected cell on the multiple paste values sheet in the same manner (with out leaving data sheet). I tried:

Please Login or Register  to view this content.

This is a simple sub to make some data entry less repetitive (eliminate mouse clicks, keyboard shortcuts, etc).

Full disclosure, I am adding additional sheets and wanted to not have to change the hard coded sheet names when I am working on a new sheet. I remembered passing the form name in access as an argument and thought there may be a similar technique in excel for sheet names.


Thanks for your timely response I can always use better practices, not sure if it matters too much in this application though.

Thank you again,
-BC

Internoob2,

You're using .Select again. Just replace the .select with whatever the operation on the cell is going to be. If you are intent on using selects, or if that's just easier with the code you currrently have, you can set the activeworksheet to a variable and then .Select it at the end like so:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

That does help a bit.

The select is just to have the focus on A2 next time I go to that tab.

When I have multiple entries to copy to a single cell I:
1: Paste them into the multiple pastes sheet and have a function that concatenates them with a ", " between each value
2. Run the sub to paste them into the sheet I was entering on initially, clear the pasted values and set focus on A2 for the next time I need to paste multiple values.


I will say this is solved, don't want to take up anymore of your time.

I really do appreciate you helping me out.

-BC

Internoob2,

If what you're doing is joining cells with delimiter ", " then you can use the following code. It prompts you to select a range of cells, gets their values, joins them with ", ", and then displays the result in a msgbox. Just delete the msgbox line and uncomment the range.value = strjoin line to have it output the result to a cell instead. Just change the ("A1") to the desired output cell:

Please Login or Register  to view this content.

~tigeravatar

I attached a sample sheet of what I have setup currently.

Now that I am adding additional sheets, I still have data sheet hard coded and change that code to the new sheet name.

I just wanted to know if it was possible to not have to hard code the sheet name. When you run "CopyPaste" (ctrl+f) from multiple paste sheet to have it go back to the sheet that was previously active.

For now I think I am not too inconveinced by changing the hard coded sheet name when I begin a new sheet. I was thinking ahead to when data may not being entered to one sheet at a time.

I hope I am being clear here.

Right now When I get to a cell that has multiple values, in this case education, I leave the cell selected and click the multiple paste values sheet, paste the values in column A, then run the sub to paste the concatenated string from F2 into the education cell on data sheet and then clear the values on multiple paste sheet and set the focus on A2 for the next time I go there.

Hopefully my thoughts will be clearer when you look at the sample workbook.

Thanks again for hanging in there with me,
-BC

Internoob2,

Where is the following coming from? I can see that they're links to facebook pages, are those being entered in manually? or copy/pasted from a website?

University of the Visayas
San Francisco Theological Seminary
RIT
John I. Leonard High School

Also, in the sample workbook, Data Sheet, row 384 is populated, but everything above it is empty. Is it normal to have empty rows like that? or does each row get filled in one at a time and the empty stuff is due to deleting data to provide a sample workbook?

Assuming that empty rows are not normal and that the data is copy/pasted from a website, you can use the following after each paste into the Multiple paste Values sheet:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

Also, I notice in your code data sheet was hard coded. Is there anyway to avoid doing that?

I attached a sample with your code and another sheet added.

So, is it possible to have it setup that if you went on the "different fan page data" sheet to the "multiple paste" sheet the paste would go to the previous sheet without the name hard coded in the sub?

I really, really appreciate your help,
-BC

P.S. I ask because most likely once the data is entered all the sheets will be updated with new fan data and it may be someone besides myself entering, so I would like it if it would work from the same sub no matter which sheet you are entering on.

Internoob2,

Code amended to end by activating the 'Multiple paste Values' worksheet and selecting cell A2:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

Internoob2,

Just saw you posted while i was posting. will get back to you on that soon

You are the man!

I will be leaving for the day soon, however I did notice something else.

The way I used to have it setup I could use the multiple paste sheet for any field I was in on data sheet (so if i had work [column F] selected multiple paste would paste there, etc). Now it appears hard coded to the next empty cell in Column E.

If I could trouble you with this as well: is it possible to have it work similar to that?

-BC

Internoob2,

Attached is a modified version of the sample workbook. The ctrl+d that you set sub tgr to will now open a userform. In the userform, select the desired destination sheet, select the desired field, paste in the items you would normally paste into the 'Multiple paste Values' sheet, and then click Submit. It will add the items to the next empty row for the chosen sheet and field.

To see the code for the userform, just doubleclick on the userform

Hope that helps,
~tigeravatar

Thank you for all the work you did on this.

I truly appreciate you taking the time to help me.

-BC

With a idea from your advice tiger I came up with a different solution in this thread.


Thanks again,
-BC