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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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