Select last sheet that had focus

**Internoob2** · 09-06-2011, 02:52 PM

Hello,

So, this is something I asked about here.

Tiger gave some advice that gave me an idea:

Sub tgr()
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    '''''''''''''''''''''''
    '                     '
    '   Your other code   '
    '                     '
    '''''''''''''''''''''''
    
    ws.Select
    
End Sub

So my thought is that I could use two macros. The first would assign the original active sheet to ws and then paste the string value (sheet name) to a cell on the 'multiple paste' sheet.

Then once the values are inputted on 'multiple paste' sheet the second macro will return to the sheet the first macro was ran from and paste the concatenated values.

My attempts so far have been unnsuccessful. I thought something like this would work, but it did not:

Sub Actv_Sheet_Ref()
Dim ws As Worksheet: Set ws = ActiveSheet
Dim x As String: Set x = ws




Sheets("Multiple paste Values").Select
    Range("F4").Value = x
    

End Sub

So as usual any help would be greatly appreciated.

-BC

**shg** · 09-06-2011, 03:32 PM

Sub Actv_Sheet_Ref()
    Dim wks         As Worksheet
    
    Set wks = ActiveSheet
    Worksheets("Multiple paste Values").Range("F4").Value = wks.Name
End Sub

**Internoob2** · 09-06-2011, 03:48 PM

Thank you Shg.

Just one more question.

Now how can I use the sheet name from f4 to go back to that sheet and paste?

the code I use now has the sheet name hard-coded:

Sheets("Data Sheet").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

How do I assign the string from f4 as a worksheet name (so I can return the focus there)?

-BC

P.S. Thank you for your help and patience I am still very much a beginner here.

**shg** · 09-06-2011, 03:52 PM

Your code is written so that it pastes in whatever is the active cell on Data Sheet. That's bad for two reasons: it's ambiguous, and requires that you select the sheet before pasting.

You can instead paste directly into a cell without selecting:

wks.Range(whatever).PasteSpecial Paste:=xlPasteValues

**Internoob2** · 09-06-2011, 04:02 PM

I understand, however for this application it will be pasting in the last cell selected on the previous sheet. (Sometimes it will be pasting to education, other times to work, activities, or interest).

So this way whatever the last cell was selected when the sheet is selected again the desired cell is already selected.

I am curious to know how to assign the sheet name from f4 back to the 'ws' variable, so this admittedly ambiguous process can be used on multiple sheets and fields. It doesn't seem to hard but I can't figure it out. I tried this:

Dim ws As Worksheet

Set ws = Worksheets("Multiple paste Values").Range("F4").Value

**Internoob2** · 09-07-2011, 10:30 AM

So, I came up with a solution from reading another forum, here. I have also attached a copy of a sample sheet with my solution.

Thank you for pointing me in the right direction. So from the sheet I am entering on with the desired cell selected I run 1st macro (ctrl+d), then copy and paste the multiple values to column A and run second macro (ctrl+f).

This will work for any new sheets added! A bit convoluted? Yes, however it suits my purposes here.

Thanks again. I love this site/community,
-BC