Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro working in Excel 2003; not working in Excel 2000
Can someone help me figure out why this macro works in Excel 2003 and not in Excel 2000
I developed these worksheets on my computer which has Excel 2003. They need to run on another computer that uses Excel 2000. I'll appreciate any help I can get on this. The sooner, the better Private Sub Workbook_Open( ' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.ht ' Insert Rows -- 1997/09/24 Mark Hill 'Dim vRows As Intege ' row selection based on active cell -- rev. 2000-09-02 David McRitchi Cells(65536, ActiveCell.Column).End(xlUp).EntireRow.Selec ' ActiveCell.EntireRow.Select 'So you do not have to preselect entire r vRows = ' If vRows < 1 The ' vRows = Application.InputBox(prompt:= ' "How many rows do you want to add?", Title:="Add Rows", ' Default:=1, Type:=1) 'type 1 is numbe ' If vRows = False Then Exit Su ' End I 'if you just want to add cells and not entire row 'then delete ".EntireRow" in the following lin 'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheet Dim sht As Worksheet, shts() As String, i As Intege ReDim shts(1 To Worksheets.Application.ActiveWorkbook. Windows(1).SelectedSheets.Count i = For Each sht In Application.ActiveWorkbook.Windows(1).SelectedShee t Sheets(sht.Name).Selec i = i + shts(i) = sht.Nam Selection.Resize(rowsize:=2).Rows(2).EntireRow. Resize(rowsize:=vRows).Insert Shift:=xlDow Selection.AutoFill Selection.Resize( rowsize:=vRows + 1), xlFillDefaul On Error Resume Next 'to handle no constants in range -- John McKee 2000/02/0 ' to remove the non-formulas -- 1998/03/11 Bill Manvill Selection.Offset(1).Resize(vRows).EntireRow. SpecialCells(xlConstants).ClearContent Next sh Worksheets(shts).Selec End Su Thank Leslie Barberi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro working in Excel 2003; not working in Excel 2000
It runs through okay for me on Excel 2000. What do you get?
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Leslie Barberie" wrote in message ... Can someone help me figure out why this macro works in Excel 2003 and not in Excel 2000? I developed these worksheets on my computer which has Excel 2003. They need to run on another computer that uses Excel 2000. I'll appreciate any help I can get on this. The sooner, the better. Private Sub Workbook_Open() ' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm ' Insert Rows -- 1997/09/24 Mark Hill 'Dim vRows As Integer ' row selection based on active cell -- rev. 2000-09-02 David McRitchie Cells(65536, ActiveCell.Column).End(xlUp).EntireRow.Select ' ActiveCell.EntireRow.Select 'So you do not have to preselect entire ro vRows = 1 ' If vRows < 1 Then ' vRows = Application.InputBox(prompt:= _ ' "How many rows do you want to add?", Title:="Add Rows", _ ' Default:=1, Type:=1) 'type 1 is number ' If vRows = False Then Exit Sub ' End If 'if you just want to add cells and not entire rows 'then delete ".EntireRow" in the following line 'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets Dim sht As Worksheet, shts() As String, i As Integer ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _ Windows(1).SelectedSheets.Count) i = 0 For Each sht In _ Application.ActiveWorkbook.Windows(1).SelectedShee ts Sheets(sht.Name).Select i = i + 1 shts(i) = sht.Name Selection.Resize(rowsize:=2).Rows(2).EntireRow. _ Resize(rowsize:=vRows).Insert Shift:=xlDown Selection.AutoFill Selection.Resize( _ rowsize:=vRows + 1), xlFillDefault On Error Resume Next 'to handle no constants in range -- John McKee 2000/02/01 ' to remove the non-formulas -- 1998/03/11 Bill Manville Selection.Offset(1).Resize(vRows).EntireRow. _ SpecialCells(xlConstants).ClearContents Next sht Worksheets(shts).Select End Sub Thanks Leslie Barberie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro working in Excel 2003; not working in Excel 2000
I ran without error for me in Excel 2000 and copied down a row in the
grouped sheets when I opened the workbook. Perhaps you need to define what does not work means specifically. the macro appears to be heavily dependent on what the situation was in terms of selection when the workbook was last saved. This would appear to be a poor approach for a workbook_open macro. -- Regards, Tom Ogilvy "Leslie Barberie" wrote in message ... Can someone help me figure out why this macro works in Excel 2003 and not in Excel 2000? I developed these worksheets on my computer which has Excel 2003. They need to run on another computer that uses Excel 2000. I'll appreciate any help I can get on this. The sooner, the better. Private Sub Workbook_Open() ' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm ' Insert Rows -- 1997/09/24 Mark Hill 'Dim vRows As Integer ' row selection based on active cell -- rev. 2000-09-02 David McRitchie Cells(65536, ActiveCell.Column).End(xlUp).EntireRow.Select ' ActiveCell.EntireRow.Select 'So you do not have to preselect entire ro vRows = 1 ' If vRows < 1 Then ' vRows = Application.InputBox(prompt:= _ ' "How many rows do you want to add?", Title:="Add Rows", _ ' Default:=1, Type:=1) 'type 1 is number ' If vRows = False Then Exit Sub ' End If 'if you just want to add cells and not entire rows 'then delete ".EntireRow" in the following line 'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets Dim sht As Worksheet, shts() As String, i As Integer ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _ Windows(1).SelectedSheets.Count) i = 0 For Each sht In _ Application.ActiveWorkbook.Windows(1).SelectedShee ts Sheets(sht.Name).Select i = i + 1 shts(i) = sht.Name Selection.Resize(rowsize:=2).Rows(2).EntireRow. _ Resize(rowsize:=vRows).Insert Shift:=xlDown Selection.AutoFill Selection.Resize( _ rowsize:=vRows + 1), xlFillDefault On Error Resume Next 'to handle no constants in range -- John McKee 2000/02/01 ' to remove the non-formulas -- 1998/03/11 Bill Manville Selection.Offset(1).Resize(vRows).EntireRow. _ SpecialCells(xlConstants).ClearContents Next sht Worksheets(shts).Select End Sub Thanks Leslie Barberie |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro working in Excel 2003; not working in Excel 2000
How are you developing this? Are you just using the Excel 2003 to write the
code, then copying the code into an existing 2000 workbook? Or are you saving the 2003 workbook and opening it with 2000? If the latter, check your library references to see if you are missing any. Ed "Leslie Barberie" wrote in message ... Can someone help me figure out why this macro works in Excel 2003 and not in Excel 2000? I developed these worksheets on my computer which has Excel 2003. They need to run on another computer that uses Excel 2000. I'll appreciate any help I can get on this. The sooner, the better. Private Sub Workbook_Open() ' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm ' Insert Rows -- 1997/09/24 Mark Hill 'Dim vRows As Integer ' row selection based on active cell -- rev. 2000-09-02 David McRitchie Cells(65536, ActiveCell.Column).End(xlUp).EntireRow.Select ' ActiveCell.EntireRow.Select 'So you do not have to preselect entire ro vRows = 1 ' If vRows < 1 Then ' vRows = Application.InputBox(prompt:= _ ' "How many rows do you want to add?", Title:="Add Rows", _ ' Default:=1, Type:=1) 'type 1 is number ' If vRows = False Then Exit Sub ' End If 'if you just want to add cells and not entire rows 'then delete ".EntireRow" in the following line 'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets Dim sht As Worksheet, shts() As String, i As Integer ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _ Windows(1).SelectedSheets.Count) i = 0 For Each sht In _ Application.ActiveWorkbook.Windows(1).SelectedShee ts Sheets(sht.Name).Select i = i + 1 shts(i) = sht.Name Selection.Resize(rowsize:=2).Rows(2).EntireRow. _ Resize(rowsize:=vRows).Insert Shift:=xlDown Selection.AutoFill Selection.Resize( _ rowsize:=vRows + 1), xlFillDefault On Error Resume Next 'to handle no constants in range -- John McKee 2000/02/01 ' to remove the non-formulas -- 1998/03/11 Bill Manville Selection.Offset(1).Resize(vRows).EntireRow. _ SpecialCells(xlConstants).ClearContents Next sht Worksheets(shts).Select End Sub Thanks Leslie Barberie |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro working in Excel 2003; not working in Excel 2000
Tom Thanks for your help. I figured it out. The Excel 2000 computer automatically disabled the macro Leslie Barberi ----- Tom Ogilvy wrote: ---- I ran without error for me in Excel 2000 and copied down a row in th grouped sheets when I opened the workbook. Perhaps you need to define wha does not work means specifically the macro appears to be heavily dependent on what the situation was in term of selection when the workbook was last saved. This would appear to be poor approach for a workbook_open macro -- Regards Tom Ogilv "Leslie Barberie" wrote in messag .. Can someone help me figure out why this macro works in Excel 2003 and no in Excel 2000 I developed these worksheets on my computer which has Excel 2003. The need to run on another computer that uses Excel 2000 I'll appreciate any help I can get on this. The sooner, the better Private Sub Workbook_Open( ' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.ht ' Insert Rows -- 1997/09/24 Mark Hill 'Dim vRows As Intege ' row selection based on active cell -- rev. 2000-09-02 David McRitchi Cells(65536, ActiveCell.Column).End(xlUp).EntireRow.Selec ' ActiveCell.EntireRow.Select 'So you do not have to preselect entir r vRows = ' If vRows < 1 The ' vRows = Application.InputBox(prompt:= ' "How many rows do you want to add?", Title:="Add Rows", ' Default:=1, Type:=1) 'type 1 is numbe ' If vRows = False Then Exit Su ' End I 'if you just want to add cells and not entire row 'then delete ".EntireRow" in the following lin 'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheet Dim sht As Worksheet, shts() As String, i As Intege ReDim shts(1 To Worksheets.Application.ActiveWorkbook. Windows(1).SelectedSheets.Count i = For Each sht In Application.ActiveWorkbook.Windows(1).SelectedShee t Sheets(sht.Name).Selec i = i + shts(i) = sht.Nam Selection.Resize(rowsize:=2).Rows(2).EntireRow. Resize(rowsize:=vRows).Insert Shift:=xlDow Selection.AutoFill Selection.Resize( rowsize:=vRows + 1), xlFillDefaul On Error Resume Next 'to handle no constants in range -- John McKe 2000/02/0 ' to remove the non-formulas -- 1998/03/11 Bill Manvill Selection.Offset(1).Resize(vRows).EntireRow. SpecialCells(xlConstants).ClearContent Next sh Worksheets(shts).Selec End Su Thank Leslie Barberi |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro working in Excel 2003; not working in Excel 2000
Ed Thank you for your response. I figured it out. The Excel 2000 computer automatically disabled the macro Leslie Barberi ----- Ed wrote: ---- How are you developing this? Are you just using the Excel 2003 to write th code, then copying the code into an existing 2000 workbook? Or are yo saving the 2003 workbook and opening it with 2000? If the latter, chec your library references to see if you are missing any E "Leslie Barberie" wrote in messag .. Can someone help me figure out why this macro works in Excel 2003 and no in Excel 2000 I developed these worksheets on my computer which has Excel 2003. The need to run on another computer that uses Excel 2000 I'll appreciate any help I can get on this. The sooner, the better Private Sub Workbook_Open( ' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.ht ' Insert Rows -- 1997/09/24 Mark Hill 'Dim vRows As Intege ' row selection based on active cell -- rev. 2000-09-02 David McRitchi Cells(65536, ActiveCell.Column).End(xlUp).EntireRow.Selec ' ActiveCell.EntireRow.Select 'So you do not have to preselect entir r vRows = ' If vRows < 1 The ' vRows = Application.InputBox(prompt:= ' "How many rows do you want to add?", Title:="Add Rows", ' Default:=1, Type:=1) 'type 1 is numbe ' If vRows = False Then Exit Su ' End I 'if you just want to add cells and not entire row 'then delete ".EntireRow" in the following lin 'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheet Dim sht As Worksheet, shts() As String, i As Intege ReDim shts(1 To Worksheets.Application.ActiveWorkbook. Windows(1).SelectedSheets.Count i = For Each sht In Application.ActiveWorkbook.Windows(1).SelectedShee t Sheets(sht.Name).Selec i = i + shts(i) = sht.Nam Selection.Resize(rowsize:=2).Rows(2).EntireRow. Resize(rowsize:=vRows).Insert Shift:=xlDow Selection.AutoFill Selection.Resize( rowsize:=vRows + 1), xlFillDefaul On Error Resume Next 'to handle no constants in range -- John McKe 2000/02/0 ' to remove the non-formulas -- 1998/03/11 Bill Manvill Selection.Offset(1).Resize(vRows).EntireRow. SpecialCells(xlConstants).ClearContent Next sh Worksheets(shts).Selec End Su Thank Leslie Barberi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2000 macro not working in Excel 2003 | Excel Discussion (Misc queries) | |||
excel 2003 slow when working with excel 2000 files | Excel Discussion (Misc queries) | |||
Excel VB 2000 not working in 2003 | New Users to Excel | |||
XL 2003 macro not working in XL 2000 | Excel Programming | |||
macro written in Excel 2000 not working in Excel 2002 | Excel Programming |