Hiding Sheets

Hi,

I am writing an application using Excel + VBA. I am doing a lot of
calculation on data on one of the worksheets and I wish to have this sheet
hidden. While calculating, I also format some of the columns and delete some
columns.

It seems like Excel dosen't like this sheet to be hidden. Any reason? Or
should I do something else?

TIA

Hi Rain,

There should normally be no problem formatting cells or hiding / unhiding
rows or columns on a hidden sheet.

If you post your code or a portion thereof which fails, perhaps more
constructive help can be offered



---
Regards,
Norman



"Rain" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I am writing an application using Excel + VBA. I am doing a lot of
> calculation on data on one of the worksheets and I wish to have this sheet
> hidden. While calculating, I also format some of the columns and delete
> some
> columns.
>
> It seems like Excel dosen't like this sheet to be hidden. Any reason? Or
> should I do something else?
>
> TIA

Hi Norman,

It seems to fail on just selecting the hidden sheet. I'm selecting the
sheet before pasting data on it so that I can format the data and do some
math on it.

Code excerpt:
----------------
Sub Macro1(strSheet As String)

If strSheet = "Select Car" Then
Sheets(strSheet).Select
Columns(strCol).Select
Selection.Copy
Range("A1").Select
*** Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Columns("B:B").Select
...
...
...
End Sub

The error that I get is:
Run-time error '1004': Select method of Worksheet class failed

TIA

"Norman Jones" wrote:

> Hi Rain,
>
> There should normally be no problem formatting cells or hiding / unhiding
> rows or columns on a hidden sheet.
>
> If you post your code or a portion thereof which fails, perhaps more
> constructive help can be offered
>
>
>
> ---
> Regards,
> Norman
>
>
>
> "Rain" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I am writing an application using Excel + VBA. I am doing a lot of
> > calculation on data on one of the worksheets and I wish to have this sheet
> > hidden. While calculating, I also format some of the columns and delete
> > some
> > columns.
> >
> > It seems like Excel dosen't like this sheet to be hidden. Any reason? Or
> > should I do something else?
> >
> > TIA
>
>
>

Hi Norman,

It seems to fail while I select the sheet to paste the data on which
I intend to do some formating on some columns and delete some columns.

Error:
------
Run-time error '1004':
Select method of Worksheet class failed

Fails at the step indicated by " *** "

Macro excerpt:
--------------
Sub Macro1(strSheet As String)

If strSheet = "Car Sheet" Then
Sheets(strSheet).Select
Columns(strCol).Select
Selection.Copy
Range("A1").Select
*** Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Columns("B:B").Select
..
..
..
..
..
End Sub



TIA


"Norman Jones" wrote:

> Hi Rain,
>
> There should normally be no problem formatting cells or hiding / unhiding
> rows or columns on a hidden sheet.
>
> If you post your code or a portion thereof which fails, perhaps more
> constructive help can be offered
>
>
>
> ---
> Regards,
> Norman
>
>
>
> "Rain" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I am writing an application using Excel + VBA. I am doing a lot of
> > calculation on data on one of the worksheets and I wish to have this sheet
> > hidden. While calculating, I also format some of the columns and delete
> > some
> > columns.
> >
> > It seems like Excel dosen't like this sheet to be hidden. Any reason? Or
> > should I do something else?
> >
> > TIA
>
>
>

Hi Rain,

You should endeavour to avoid select constructs. It is almost always
possible to avoid selects and this tends to result in shorter, more
efficient code.

A problem with your code is that, it is possible to copy bidirectionally to
a hidden sheet, it is not possible to select such a sheet.

An additional problem reside in the syntax tadopted for the paste method:
whilst the use of the destination argument is optional, if it is not used
then a selection is required. This will fail for a hidden sheet.

Therefore, removing selects, adding yje destination argument etc, you will
have code something like:

Sub Macro1(strSheet As String)

If strSheet = "Select Car" Then
Sheets(strSheet).Columns(strcol).Copy
ActiveSheet.Paste Destination:= _
Sheets("Sheet2").Range("A1")
End If

End Sub


---
Regards,
Norman



"Rain" <[email protected]> wrote in message
news:[email protected]...
> Hi Norman,
>
> It seems to fail on just selecting the hidden sheet. I'm selecting the
> sheet before pasting data on it so that I can format the data and do some
> math on it.
>
> Code excerpt:
> ----------------
> Sub Macro1(strSheet As String)
>
> If strSheet = "Select Car" Then
> Sheets(strSheet).Select
> Columns(strCol).Select
> Selection.Copy
> Range("A1").Select
> *** Sheets("Sheet2").Select
> Range("A1").Select
> ActiveSheet.Paste
> Columns("B:B").Select
> ..
> ..
> ..
> End Sub
>
> The error that I get is:
> Run-time error '1004': Select method of Worksheet class failed
>
> TIA
>
> "Norman Jones" wrote:
>
>> Hi Rain,
>>
>> There should normally be no problem formatting cells or hiding /
>> unhiding
>> rows or columns on a hidden sheet.
>>
>> If you post your code or a portion thereof which fails, perhaps more
>> constructive help can be offered
>>
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Rain" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi,
>> >
>> > I am writing an application using Excel + VBA. I am doing a lot of
>> > calculation on data on one of the worksheets and I wish to have this
>> > sheet
>> > hidden. While calculating, I also format some of the columns and delete
>> > some
>> > columns.
>> >
>> > It seems like Excel dosen't like this sheet to be hidden. Any reason?
>> > Or
>> > should I do something else?
>> >
>> > TIA
>>
>>
>>

Hi Rain,

See reply to your preceding post.

I should add that the revised code is untested. Try it on a copy.

---
Regards,
Norman



"Rain" <[email protected]> wrote in message
news:[email protected]...
> Hi Norman,
>
> It seems to fail while I select the sheet to paste the data on which
> I intend to do some formating on some columns and delete some columns.
>
> Error:
> ------
> Run-time error '1004':
> Select method of Worksheet class failed
>
> Fails at the step indicated by " *** "
>
> Macro excerpt:
> --------------
> Sub Macro1(strSheet As String)
>
> If strSheet = "Car Sheet" Then
> Sheets(strSheet).Select
> Columns(strCol).Select
> Selection.Copy
> Range("A1").Select
> *** Sheets("Sheet2").Select
> Range("A1").Select
> ActiveSheet.Paste
> Columns("B:B").Select
> .
> .
> .
> .
> .
> End Sub
>
>
>
> TIA
>
>
> "Norman Jones" wrote:
>
>> Hi Rain,
>>
>> There should normally be no problem formatting cells or hiding /
>> unhiding
>> rows or columns on a hidden sheet.
>>
>> If you post your code or a portion thereof which fails, perhaps more
>> constructive help can be offered
>>
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Rain" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi,
>> >
>> > I am writing an application using Excel + VBA. I am doing a lot of
>> > calculation on data on one of the worksheets and I wish to have this
>> > sheet
>> > hidden. While calculating, I also format some of the columns and delete
>> > some
>> > columns.
>> >
>> > It seems like Excel dosen't like this sheet to be hidden. Any reason?
>> > Or
>> > should I do something else?
>> >
>> > TIA
>>
>>
>>

Hi Norman,
Thanks for the reply. Is there any way to write these lines in a similar
way ?

excerpt:
>>>
Application.CutCopyMode = False
Selection.Insert shift:=xlToRight
Selection.NumberFormat = "dd/mm/yyyy;@"
<<<

Regards,
Rain

"Rain" wrote:

> Hi Norman,
>
> It seems to fail while I select the sheet to paste the data on which
> I intend to do some formating on some columns and delete some columns.
>
> Error:
> ------
> Run-time error '1004':
> Select method of Worksheet class failed
>
> Fails at the step indicated by " *** "
>
> Macro excerpt:
> --------------
> Sub Macro1(strSheet As String)
>
> If strSheet = "Car Sheet" Then
> Sheets(strSheet).Select
> Columns(strCol).Select
> Selection.Copy
> Range("A1").Select
> *** Sheets("Sheet2").Select
> Range("A1").Select
> ActiveSheet.Paste
> Columns("B:B").Select
> .
> .
> .
> .
> .
> End Sub
>
>
>
> TIA
>
>
> "Norman Jones" wrote:
>
> > Hi Rain,
> >
> > There should normally be no problem formatting cells or hiding / unhiding
> > rows or columns on a hidden sheet.
> >
> > If you post your code or a portion thereof which fails, perhaps more
> > constructive help can be offered
> >
> >
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "Rain" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi,
> > >
> > > I am writing an application using Excel + VBA. I am doing a lot of
> > > calculation on data on one of the worksheets and I wish to have this sheet
> > > hidden. While calculating, I also format some of the columns and delete
> > > some
> > > columns.
> > >
> > > It seems like Excel dosen't like this sheet to be hidden. Any reason? Or
> > > should I do something else?
> > >
> > > TIA
> >
> >
> >

Hi Rain,

Please extend amd post the excerpt to include your.preceding selection and
copy steps.


---
Regards,
Norman



"Rain" <[email protected]> wrote in message
news:[email protected]...
> Hi Norman,
> Thanks for the reply. Is there any way to write these lines in a similar
> way ?
>
> excerpt:
>>>>
> Application.CutCopyMode = False
> Selection.Insert shift:=xlToRight
> Selection.NumberFormat = "dd/mm/yyyy;@"
> <<<
>
> Regards,
> Rain
>

Hi Norman,

Here is the macro that I am using:
Code Excerpt :
---------------
Sub Macro1(strSheet As String, strFormatRange as String, strCol as String)

If strSheet = "Select Car" Then
Sheets(strSheet).Columns(strcol).Copy
ActiveSheet.Paste Destination:= _
Sheets("Sheet2").Range("A1")
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Insert shift:=xlToRight
Selection.NumberFormat = "dd/mm/yyyy;@"
ActiveCell.FormulaR1C1 = _
"=DATEVALUE(LE --- removed to keep it short --- RC2,4))"
Range("B1").Select
Selection.AutoFill Destination:=Range(strFormatRange)

End Sub


Original Code excerpt:
--------------------------
Sub Macro1(strSheet As String, strFormatRange as String, strCol as String)

If strSheet = "Select Car" Then
Sheets(strSheet).Select
Columns(strCol).Select
Selection.Copy
Range("A1").Select
*** Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Insert shift:=xlToRight
Selection.NumberFormat = "dd/mm/yyyy;@"
ActiveCell.FormulaR1C1 = _
"=DATEVALUE(LE --- removed to keep it short --- RC2,4))"
Range("B1").Select
Selection.AutoFill Destination:=Range(strFmtRange)

End Sub

Regards,
Rain

"Norman Jones" wrote:

> Hi Rain,
>
> Please extend amd post the excerpt to include your.preceding selection and
> copy steps.
>
>
> ---
> Regards,
> Norman
>
>
>
> "Rain" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Norman,
> > Thanks for the reply. Is there any way to write these lines in a similar
> > way ?
> >
> > excerpt:
> >>>>
> > Application.CutCopyMode = False
> > Selection.Insert shift:=xlToRight
> > Selection.NumberFormat = "dd/mm/yyyy;@"
> > <<<
> >
> > Regards,
> > Rain
> >
>
>
>

Hi Rain,

My best guess is:

Sub Macro1(strSheet As String, strFormatRange As String, strCol As String)

If strSheet = "Select Car" Then
Sheets(strSheet).Columns(strCol).Copy
ActiveSheet.Paste Destination:= _
Sheets("Sheet2").Range("A1")
Application.CutCopyMode = False
Columns("B:B").Insert shift:=xlToRight
With Range(strFormatRange)
.NumberFormat = "dd/mm/yyyy;@"
.Cells(1).FormulaR1C1 = "=RC[-1]+1" '<<======= CHANGE
.Cells(1).AutoFill Destination:=Range(strFormatRange)
End With
End If

End Sub

You need to change the formula in the line marked
'<<======= CHANGE
The above formula is just a plug used for testing purposes.

As I do not know your data layout, or what you object is, I have had to make
cetain assumptions which may well be erroneous.

So please test on a *copy* of your data!


---
Regards,
Norman



"Rain" <[email protected]> wrote in message
news:[email protected]...
> Hi Norman,
>
> Here is the macro that I am using:
> Code Excerpt :
> ---------------
> Sub Macro1(strSheet As String, strFormatRange as String, strCol as String)
>
> If strSheet = "Select Car" Then
> Sheets(strSheet).Columns(strcol).Copy
> ActiveSheet.Paste Destination:= _
> Sheets("Sheet2").Range("A1")
> Columns("B:B").Select
> Application.CutCopyMode = False
> Selection.Insert shift:=xlToRight
> Selection.NumberFormat = "dd/mm/yyyy;@"
> ActiveCell.FormulaR1C1 = _
> "=DATEVALUE(LE --- removed to keep it short --- RC2,4))"
> Range("B1").Select
> Selection.AutoFill Destination:=Range(strFormatRange)
>
> End Sub
>
>
> Original Code excerpt:
> --------------------------
> Sub Macro1(strSheet As String, strFormatRange as String, strCol as String)
>
> If strSheet = "Select Car" Then
> Sheets(strSheet).Select
> Columns(strCol).Select
> Selection.Copy
> Range("A1").Select
> *** Sheets("Sheet2").Select
> Range("A1").Select
> ActiveSheet.Paste
> Columns("B:B").Select
> Application.CutCopyMode = False
> Selection.Insert shift:=xlToRight
> Selection.NumberFormat = "dd/mm/yyyy;@"
> ActiveCell.FormulaR1C1 = _
> "=DATEVALUE(LE --- removed to keep it short --- RC2,4))"
> Range("B1").Select
> Selection.AutoFill Destination:=Range(strFmtRange)
>
> End Sub
>
> Regards,
> Rain

Thanks Norman.
This worked fine for me.

Regards,
Rain

"Norman Jones" wrote:

> Hi Rain,
>
> My best guess is:
>
> Sub Macro1(strSheet As String, strFormatRange As String, strCol As String)
>
> If strSheet = "Select Car" Then
> Sheets(strSheet).Columns(strCol).Copy
> ActiveSheet.Paste Destination:= _
> Sheets("Sheet2").Range("A1")
> Application.CutCopyMode = False
> Columns("B:B").Insert shift:=xlToRight
> With Range(strFormatRange)
> .NumberFormat = "dd/mm/yyyy;@"
> .Cells(1).FormulaR1C1 = "=RC[-1]+1" '<<======= CHANGE
> .Cells(1).AutoFill Destination:=Range(strFormatRange)
> End With
> End If
>
> End Sub
>
> You need to change the formula in the line marked
> '<<======= CHANGE
> The above formula is just a plug used for testing purposes.
>
> As I do not know your data layout, or what you object is, I have had to make
> cetain assumptions which may well be erroneous.
>
> So please test on a *copy* of your data!
>
>
> ---
> Regards,
> Norman
>
>
>
> "Rain" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Norman,
> >
> > Here is the macro that I am using:
> > Code Excerpt :
> > ---------------
> > Sub Macro1(strSheet As String, strFormatRange as String, strCol as String)
> >
> > If strSheet = "Select Car" Then
> > Sheets(strSheet).Columns(strcol).Copy
> > ActiveSheet.Paste Destination:= _
> > Sheets("Sheet2").Range("A1")
> > Columns("B:B").Select
> > Application.CutCopyMode = False
> > Selection.Insert shift:=xlToRight
> > Selection.NumberFormat = "dd/mm/yyyy;@"
> > ActiveCell.FormulaR1C1 = _
> > "=DATEVALUE(LE --- removed to keep it short --- RC2,4))"
> > Range("B1").Select
> > Selection.AutoFill Destination:=Range(strFormatRange)
> >
> > End Sub
> >
> >
> > Original Code excerpt:
> > --------------------------
> > Sub Macro1(strSheet As String, strFormatRange as String, strCol as String)
> >
> > If strSheet = "Select Car" Then
> > Sheets(strSheet).Select
> > Columns(strCol).Select
> > Selection.Copy
> > Range("A1").Select
> > *** Sheets("Sheet2").Select
> > Range("A1").Select
> > ActiveSheet.Paste
> > Columns("B:B").Select
> > Application.CutCopyMode = False
> > Selection.Insert shift:=xlToRight
> > Selection.NumberFormat = "dd/mm/yyyy;@"
> > ActiveCell.FormulaR1C1 = _
> > "=DATEVALUE(LE --- removed to keep it short --- RC2,4))"
> > Range("B1").Select
> > Selection.AutoFill Destination:=Range(strFmtRange)
> >
> > End Sub
> >
> > Regards,
> > Rain
>
>
>