UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Vba To Go To Last Worksheet You Were On ?, Office 2013    
 
   
bakersburg9
post Sep 10 2019, 02:00 PM
Post#1



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


I tried this:

CODE
Public LastSheet As String
Sub Select_Last()
Application.Sheets(LastSheet).Select
End Sub


but didn't work - actually, I may not have known how to apply it
Go to the top of the page
 
DanielPineault
post Sep 10 2019, 02:59 PM
Post#2


UtterAccess VIP
Posts: 6,899
Joined: 30-June 11



Excel doesn't keep track of what sheet you are/were on. So you would need to setup your workbook to store a sheet name whenever you switch sheets, then you'd be able to call that variable to return to it at a click. That said, I recognize that bit of code from https://www.extendoffice.com/documents/exce...et-tab.html#vba and would advise you read and implement the code in it's entirety (perform all 8 steps and then report back).


--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
bakersburg9
post Sep 11 2019, 09:36 AM
Post#3



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


Daniel,
I'll check that out later - but in the meantime, I found this in my notes - it uses the dreaded SEND KEYS, but I think that's not necessary - there's another way to skin that cat

THE FOLLOWING MACRO :

1. COPIES A RANGE OF DATA FROM A WORKSHEET
2. MOVES TO “MAIN” WORKSHEET
3. FINDS END OF DATA ALREADY IN MAIN, THEN MOVES DOWN ONE ROW
4. PASTES DATA FROM SOURCE WORKSHEET
5. GOES BACK TO SOURCE WORKSHEET
6. MOVES TO NEXT WORKSHEET (One to right of source)
7. REPEATS STEPS 1-6 UNTIL DONE.

CODE
Sub MoveToMainNewNew()
'
Dim PWkSheet As String
Dim counter As Integer

counter = 1

Do While counter < 220
'
PWkSheet = ActiveWorkbook.ActiveSheet.Name
    
    Application.Goto Reference:="R8C1"
    Range("A8:E150").Select

‘Copy to Buffer  
    Selection.Copy
    Sheets("MAIN").Select

‘Find where to paste data
    Application.Goto Reference:="R64000C1"   'go to end
    Selection.End(xlUp).Select       ' go to bottom of current data block
    
'Go down one cell

    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.paste

ActiveWorkbook.Worksheets(PWkSheet).Activate
SendKeys ("{esc}")  ‘ Clear
counter = counter + 1   ‘ Increment Counter

    Range("A1").Select ' go to top
Sheets(ActiveSheet.Index + 1).Select  ‘ Go to next sheet

'Move to next
Sheets(ActiveSheet.Index + 1).Select

End Sub


Does that 'float your boat' ?



Steve
This post has been edited by bakersburg9: Sep 11 2019, 09:44 AM
Go to the top of the page
 
ADezii
post Sep 11 2019, 11:44 AM
Post#4



Posts: 2,674
Joined: 4-February 07
From: USA, Florida, Delray Beach


You could actually make use of a Public Array to store the Worksheet Names and Cell Addresses of all the Worksheets that have been worked on. Whenever you wish to recall the Last Sheet/Address that you worked on, you would execute a Macro with a single line of Code, displaying the Value of the Array @ ArrayName(UBound(ArrayName)-1) which will be the next-to-last Value in the Array. This approach is a little crude but it does seem to work. Let me know if you are interested. Sample OUTPUT is posted below:

Attached File(s)
Attached File  Last.JPG ( 16.55K )Number of downloads: 0
 
Go to the top of the page
 
bakersburg9
post Sep 11 2019, 12:13 PM
Post#5



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


ADezii,
I'm not as interested in knowing what the last sheet was that I worked on, but GOING there coded in my macro - that amazing awesome macro you wrote for me with the individual worksheets and copying the worksheet name to COL A on the summary sheet, and also copying 7 values one at a time to the next line in columns B, then C, and so on, to column H, then basically repeating that process, negated my need for this capability

My intention was to, at each individual worksheet, run a macro to copy the value to COL A (the worksheet tab name) in the SUMMARY worksheet, then go back to the worksheet tab I was just on, and continue this process - which pretty labor-intensive, but much better than doing it TOTALLY manually...

Thanks again!!! cool.gif

Steve
Go to the top of the page
 
ADezii
post Sep 11 2019, 12:29 PM
Post#6



Posts: 2,674
Joined: 4-February 07
From: USA, Florida, Delray Beach


Thanks for the clarification, Steve.
Go to the top of the page
 
Debaser
post Sep 12 2019, 03:51 AM
Post#7



Posts: 147
Joined: 11-October 18



There is very rarely a need to do that in code, since you rarely need to actually change sheets to use them. For example, the code you posted earlier can be reduced to something like:

CODE
Sub MoveToMainNewNew()
   Dim ws As Worksheet
   For Each ws In ActiveWorkbook.Worksheets
      If UCase$(ws.Name) <> "MAIN" Then
         ws.Range("A8:E150").Copy Sheets("MAIN").Cells(Rows.Count, "A").End(xlUp).Offset(1)
      End If
   Next
End Sub
Go to the top of the page
 
bakersburg9
post Sep 12 2019, 02:01 PM
Post#8



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


Debaser,
Agreed - but i just learned this - this is HUGE! I was trying to do it "piecemeal," step-by-step, one at a time - didn't realize you could copy multiple items at once - My "vision" was to copy from one cell to the SUMMARY worksheet, then go back to the individual worksheet, and copy the NEXT item to the summary page, and so on.... I deserve virtually none of the credit, but this was the greatest success story for me ever - my sup giving me a project that would've taken days, completed in a few minutes....

Thanks ! cool.gif

Steve
Go to the top of the page
 
bakersburg9
post Sep 13 2019, 05:00 PM
Post#9



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


I just realized my code doesn't come CLOSE to what I was trying to do - I still think this is what I was doing - I still think I can do that
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th October 2019 - 10:17 PM