Selecting hidden sheet

[AMAS]

Hi,

Is it possible to select a hidden sheet to do temporary calculations or do I have to unhide it and then hide it again everytime the macro runs.

Example:

Sheets("Temp").Visible = True
 
    Sheets("Temp").Select
    Range("A1").Select
 
    'more code
 
Sheets("Temp").Visible = False

Also is it possible to select the sheet and the range in one line? The following doesn't work:

Sheets("Temp").Range("A1").Select

Thanks.

AMAS

 

[Norie]

Why do you need to select the worksheet?

It could be possible to do what you want without selecting it, whether it's hidden or not.

 

[AMAS]

Hi Norie,

The temp sheet is used for alot of my code as a temporary mixing pot where intermediate steps are performed (e.g. opening .txt files, webpages, etc.) before the final product is brought back to the primary worksheets.

I can use Application.ScreenUpdating = False at the begining of the code but want to know my limitations before using this approach.

Any thoughts?

AMAS

 

[Norie]

Like I said you can probably do what you want without selecting the worksheet.

In fact why would you want/need to select it if all you are using it for is a 'mixing pot'?

 

[AMAS]

Hi Norie,

One of the purposes is opening a tab-delimited text file, manipulating it and taking the final result back to the original sheet. When the temp sheet is not selected, the code tries to do this with the original sheet which messess everything.

I found that I was creating then deleting the temp sheet many times during the course of code. Therefore I create it early on and just hide it instead of creating/ erasing it. When I am retrieving data from multiple times, this could be up to 10,000 times. This seems to be slowing down my code.

AMAS

 

[devonknows]

Instead of select the cell why dont you just use the data that is there, manipulate it in VBA and then replace the contents of the cell?

Example:
Sheets("Sheet1").Range("A4").Value

Dim i As Integer
i = Sheets("Sheet1").Range("A1")
i = i / 2
Sheets("Sheet1").Range("A1") = i
MsgBox Sheets("Sheet1").Range("A1")

Not sure what the need to select the sheet and cell is?

Regards,
Devon

 

[AMAS]

Hi Devon,

The problem is the temp sheet is used for different things. For example when I import a text file then it moves some columns around, trims part of the cells, etc. before copying the final cells back to the main database. With other macros the temp sheet is used to open web pages and then find cells with certain data in them and only copy these cells back to the database. In all cases, the temp sheet is erased or deleted in its entirety after the action is completed. I tried to not Select the temp sheet and then Excel just used the current database sheet for the import, which messes everything on that sheet.

Any thoughts?

AMAS

 

[devonknows]

Have you tried opening the document into VBA and using Loops/Do While to loop through your data, open another file for your web page usage and compare and modify the data.

Using the VBA process would certainly save a lot of strain processing wise, i mean unhiding a sheet might not be that big a deal here n there but 10,000 times got a be a big resource drain especially when excel starts lagging in the middle of your function.

Id suggest looking into the FileSystem procedure personally and you can use codes such as "DoEvents" to keep excel up to date or use a user form in VbModal mode to prevent clicks on excel.

Withough more details thats the only thing i can suggest you try the above mentioned, you cant select cells within a sheet that is hidden, if its just pulling data and comparing thats a different matter but for trimming cells an preformatting you need the cell activated.

Alternatively, have you tried formatting/trimming the destination cell rather than copying then the temp interim stage... just a thought

Regards,

 

[AMAS]

Hi Devon,

I will look into the possibility of using FileSystem and "DoEvents". In the meantime I guess the answer to my question that I cannot select a range in a hidden worksheet.

Any thoughts about my second problem about selecting a worksheet and a range in one line:

Sheets("Temp").Range("A1").Select

AMAS

 

[Norie]

Have you considered not using one worksheet for all these different things?

Also if the code is working with the wrong sheet that can probably be easily dealt with by making sure the code refers to the correct worksheet.

That's pretty straightforward to do, the main thing needed is worksheet references for ranges etc.

By the way, what is it you are doing that involves retrieving data 10,000 times?

That just doesn't sound 'right'.