Hiding and unhiding sheets with VBA

grntrnr · Apr 4th 2012

I have successfully coded a routine to copy a sheet to a txt file, allowing it to be used as input by other workbooks not in the same collection. Everything works fine when I do not keep the sheet hidden, but fails when it is hidden. So, I have surrounded the copy command with Visible=True and Visible=False instructions. This fails on the Visible=False command. Can anyone help me with this?

Code

Application.ScreenUpdating = False
        strCubeAccountName = strFilePath & wsControl.Range("E7") & ".txt"
        On Error Resume Next
        Kill strCubeAccountName
        On Error GoTo 0
        Worksheets("CubeArray").Visible = True
        wsCubeArray.Copy
[COLOR=#ff0000]        Worksheets("CubeArray").Visible = False[/COLOR]
        ActiveWorkbook.SaveAs Filename:=strCubeAccountName, FileFormat:=xlCSV, CreateBackup:=False
        ActiveWorkbook.Close Savechanges:=False
        Application.ScreenUpdating = True

Display More

grntrnr · Apr 4th 2012

Re: Hiding and unhiding sheets with VBA

I tried to highlight the failing instruction. but, your editor inserted Color commands. Please ignore them. Thanks.

jproffer · Apr 4th 2012

Re: Hiding and unhiding sheets with VBA

Code

Sheets("Sheet1").Visible = True

then your code, then:

Code

Sheets("Sheet1").Visible = xlHidden
    'or
    Sheets("Sheet1").Visible = xlVeryHidden

grntrnr · Apr 4th 2012

Re: Hiding and unhiding sheets with VBA

Sorry, that doesn't seem to work. It give x1Hidden as variable not defined.

Code

Sheets("CubeArray").Visible = True
        wsCubeArray.Copy
        Sheets("CubeArray").Visible = x1Hidden

jproffer · Apr 4th 2012

Re: Hiding and unhiding sheets with VBA

wsCubeArray looks like it's not defined....try using:

Code

Sheets("CubeArray").Visible = True
Sheets("CubeArray").Copy Before:= Sheets(1)
Sheets("CubeArray").Visible = xlHidden

assuming, wsCubeArray is supposed to mean that worksheet copying to another location.

grntrnr · Apr 4th 2012

Re: Hiding and unhiding sheets with VBA

I tried it (using your second line of code), but the same failure "x1Hidden variable not defined. (wsCubeArray is a worksheet defined elsewhere as a worksheet.)

grntrnr · Apr 4th 2012

Re: Hiding and unhiding sheets with VBA

Okay, I stumbled on the solution. Here is the generic code for those who wish to save a worksheet as a txt file and be able to access it outside of the excel collection. Thanks for your help. jproffer

Code

Dim strfilename as string
'
public wsworksheet as worksheet
set wsworksheet = Worksheets("worksheet")
'
        Application.ScreenUpdating = False
        strCubeAccountName = strFilePath & filename & ".txt"
        On Error Resume Next
        Kill strfilename
        On Error GoTo 0
        wsworksheet.Visible = xlSheetVisible
        wsworksheet.Copy
        wsworksheet.Visible = xlSheetHidden
        ActiveWorkbook.SaveAs Filename:=strfilename, FileFormat:=xlCSV, CreateBackup:=False
        ActiveWorkbook.Close Savechanges:=False
        Application.ScreenUpdating = True

Display More

Hiding and unhiding sheets with VBA

Participate now!

Share