Hiding Sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
Hi Rain,

See reply to your preceding post.

I should add that the revised code is untested. Try it on a copy.
 
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,

Please extend amd post the excerpt to include your.preceding selection and
copy steps.
 
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
 
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!
 
Thanks Norman.
This worked fine for me.

Regards,
Rain

Norman Jones said:
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 said:
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
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Columns moving up and down on their own in a worksheet. What Goes? 2
Hide columns based on row values 2
Match and delete rows on two different sheets from reference numbers 4
Hiding columns in Excel 2003 2
Cannot shift objects off sheet 2
hide columns on a protected sheet 2
How to Stop calculation on one specific sheets only 7
Linked cells not refreshing 2

Back
Top