|
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" 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 |
| All times are GMT +1. The time now is 09:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com