VBA/Macro "Return to most recent worksheet"

[abringley1083]

I have a macro I want to iron out... I need to copy/paste specific columns from one work sheet to another. I believe I have it to where when I run the macro, it will successfully copy/paste the columns.

My problem is the way I wrote the macro, it only knows to do it between two certain worksheets. For example, if I want to copy the columns from worksheet X into worksheet Y, it's written to do that. But if I start the macro on worksheet Z (to copy the columns into that particular worksheet), it still copies into worksheet Y.

Basically I want the code "Sheets("Y").Select" to be something like "Sheets(most recent/last visited/something).Select"

Let me know if you can help with this on Mac OS Excel 16.

 

[MARK858]

But if I start the macro on worksheet Z (to copy the columns into that particular worksheet) , it still copies into worksheet Y.

If that is the case then use ActiveSheet rather than referring to the worksheet name.

 

[abringley1083]

If that is the case then use ActiveSheet rather than referring to the worksheet name.

So I tried changing "Y" to ActiveSheet and "ActiveSheet" but I think what happens is when it jumps to Worksheet X to copy the columns, the active sheet changes to worksheet X.

Is that a possible problem?

 

[MARK858]

But if I start the macro on worksheet Z

I thought by what you posted that Z (the sheet being pasted to) was the activesheet.

Post your code (in code tags please) in the thread and describe what you would do if you were doing the task manually.

 

[PureBluff]

Apologies, I don't run a mac, but maybe something like this?

ThisWorkbook Code:

Private Sub Workbook_SheetDeactivate(ByVal Sht As Object)
Set LstSht = Sht
End Sub

Then in a module, reference it using:

Public LstSht As Worksheet
Sub YourCode()
    LstSht.Activate
End Sub

That wont work if there was never a previous sheet selected, but you could work around that with

sheets("Sheet1").activate
LstSht.Activate