Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft Excel _ Vba To Go To Last Worksheet You Were On ?

Posted by: bakersburg9 Sep 10 2019, 02:00 PM

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

Posted by: DanielPineault Sep 10 2019, 02:59 PM

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/excel/2878-excel-shortcut-to-previous-last-sheet-tab.html#vba and would advise you read and implement the code in it's entirety (perform all 8 steps and then report back).


Posted by: bakersburg9 Sep 11 2019, 09:36 AM

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

Posted by: ADezii Sep 11 2019, 11:44 AM

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:


 

Posted by: bakersburg9 Sep 11 2019, 12:13 PM

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

Posted by: ADezii Sep 11 2019, 12:29 PM

Thanks for the clarification, Steve.

Posted by: Debaser Sep 12 2019, 03:51 AM

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

Posted by: bakersburg9 Sep 12 2019, 02:01 PM

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

Posted by: bakersburg9 Sep 13 2019, 05:00 PM

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