0

I have created a dynamic workbook where i put and pull data to more sheets containing data from other sources. I have a "Temp" sheet where code updates som data, this sheet is hidden until the macro is run, to put and pull data, i make it visible and when finished i hide it again. This works (almost) perfect.

The problem occurs when i hide it, then my last Sheet i selected automatically? My code is totally without any .select or .active (Still learning) because it is important that code always return to the sheet from where the code is run as this changes name everyday.

I can't seem to find anything that solves my issue, hope somebody can help:

I tried the following, as you can see in the following code:

Sheets("Temp").Cells.ClearContents
Sheets("Temp").Visible = False

'Cleans filters
ActiveSheet.ShowAllData
Application.Goto Range("A4")

Application.ScreenUpdating = True

I want to always stay in my activesheet (example: ("16") which is the actual date, there will be one sheet for everyday etc. ("17"), ("18") and so on.)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Thomas Nørlund Rasmussen
  • 27
  • 3
  • Store the active sheet before hiding the other sheet and then after go back to the stored sheet afterwards. – SJR May 10 '19 at 10:11
  • `Dim Mysheetvariable as string Mysheetvariable = thisworkbook.activesheet.name 'do some code' thisworkbook.worksheets(Mysheetvariable).select` this should do the trick – Luuklag May 10 '19 at 10:17
  • @Luuklag Why not `Dim Mysheet as Worksheet: Set Mysheet = ThisWorkbook.ActiveSheet: 'Do some code: Mysheet.Select`? – Chronocidal May 10 '19 at 10:46
  • @Chronocidal yeah that works just as well. – Luuklag May 10 '19 at 11:01

1 Answers1

2

You can use With to immediately reactivate the sheet, like so:

With ActiveSheet
    OtherSheet.Visible = xlSheetVisible
    .Activate 'Immediately reactivate the ActiveSheet
End With

(You may want to toggle Application.ScreenUpdating so that you do not see so much as a flicker of the other sheet)

However, why do you need to show the sheet to copy data from it? If you are not using Select (which you say you are already sorted on), then you should be able to do most things with a Hidden sheet, such as HiddenSheet.Range("A1:B3").Copy

(A "Very Hidden" sheet, on the other hand, has a couple of restrictions - for example, you cannot delete a Very Hidden sheet.)

Chronocidal
  • 6,827
  • 1
  • 12
  • 26
  • Oh my god, im so imbarraced! I did not know that it was possible to pull and put data in a Hidden sheet, and i never tried to... Damn me! I read on the net that you can't work with hidden sheets, so best way was to hide and unhide them. Thank you so much for helping me, this is just perfect! And great extra examples. Have a great weekend. – Thomas Nørlund Rasmussen May 10 '19 at 11:33