Hi

Your code worked for me with Windows 2000, XL2002.

This also worked.

Sub DeleteAndCopy()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim ws As Worksheet, ws1 As Worksheet, x As String
Set ws1 = Sheets("Inquiry Form ")
x = ws1.Name
Set ws = Sheets.Add
ws.Move after:=ws1
ws1.Rows("1:100").Copy ws.Range("A1")
ws1.Delete
ws.Name = x
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


--
XL2002
Regards

William


"EC" wrote in message
m...
| With apologies to those who may have answered similar questions in the
| past... problem for me it that I just can not get this to work.
|
| Background: VBA code was developed under Win95 (yes, client still has
| Win95 machines) in Excel-97 where it seems to work just fine. On a NT
| machine with Excel-97 it seems to work but this the code is not fully
| tested on that machine. Under Win2000 with Excel 2002, the code does
| not work. It steps through certain code but make no change to the
| worksheet(s).
|
| Issue:
| Add a new worksheet, copy first 100 rows from an existing sheet, same
| workbook to the first 100 rows of new sheet, delete the original
| sheet, rename the new sheet to the old sheet name (.delete and .name
| statements do nothing).
|
| Application.Sheets.Add Type:="Worksheet"
| Application.ActiveSheet.Move after:=Worksheets("Inquiry Form ")
| Application.ActiveSheet.Name = "NewInquiryForm"
| Sheets("Inquiry Form ").Rows.("1:100").Copy
| Sheets("NewInquiryForm").Activate
| Range("A1").Select
| ActiveSheet.Paste
|
| Sheets("Inquiry Form ").Activate
| ActiveWindow.SelectedSheets.Delete
| Sheets("NewInquiryForm").Activate
| Sheets("NewInquiryForm").Name = "Inquiry Form "
|
|
| When the above .delete and .name statements did nothing, I also tried:
|
| Sheets("Inquiry Form ").Activate
| ThisWorkBook.Names("Inquiry Form ").Delete
| Sheets("NewInquiryForm").Activate
| ThisWorkBook.Names("NewInquiryForm").Name = "Inquiry Form "
|
| Any assistance would be greatly appreciated!
| Thanks,
| Ron

William,

Thanks for taking the time to test the code and to respond.

Given that the code worked successfully for you, in your opinion,
could there be something in the environment/setup of the Win2000
machines that does not allow this code to exhibit the expected
behavior? Someone thought that under VBA References I might find
missing libraries... but I do not see anything that would indicate
this to be the case.

Do you (or anyone else) have something to add?

Thanks again,
Ron

================================================== =============================
================================================== =============================

"William" wrote in message ...
Hi

Your code worked for me with Windows 2000, XL2002.

This also worked.

Sub DeleteAndCopy()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim ws As Worksheet, ws1 As Worksheet, x As String
Set ws1 = Sheets("Inquiry Form ")
x = ws1.Name
Set ws = Sheets.Add
ws.Move after:=ws1
ws1.Rows("1:100").Copy ws.Range("A1")
ws1.Delete
ws.Name = x
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


--
XL2002
Regards

William


"EC" wrote in message
m...
| With apologies to those who may have answered similar questions in the
| past... problem for me it that I just can not get this to work.
|
| Background: VBA code was developed under Win95 (yes, client still has
| Win95 machines) in Excel-97 where it seems to work just fine. On a NT
| machine with Excel-97 it seems to work but this the code is not fully
| tested on that machine. Under Win2000 with Excel 2002, the code does
| not work. It steps through certain code but make no change to the
| worksheet(s).
|
| Issue:
| Add a new worksheet, copy first 100 rows from an existing sheet, same
| workbook to the first 100 rows of new sheet, delete the original
| sheet, rename the new sheet to the old sheet name (.delete and .name
| statements do nothing).
|
| Application.Sheets.Add Type:="Worksheet"
| Application.ActiveSheet.Move after:=Worksheets("Inquiry Form ")
| Application.ActiveSheet.Name = "NewInquiryForm"
| Sheets("Inquiry Form ").Rows.("1:100").Copy
| Sheets("NewInquiryForm").Activate
| Range("A1").Select
| ActiveSheet.Paste
|
| Sheets("Inquiry Form ").Activate
| ActiveWindow.SelectedSheets.Delete
| Sheets("NewInquiryForm").Activate
| Sheets("NewInquiryForm").Name = "Inquiry Form "
|
|
| When the above .delete and .name statements did nothing, I also tried:
|
| Sheets("Inquiry Form ").Activate
| ThisWorkBook.Names("Inquiry Form ").Delete
| Sheets("NewInquiryForm").Activate
| ThisWorkBook.Names("NewInquiryForm").Name = "Inquiry Form "
|
| Any assistance would be greatly appreciated!
| Thanks,
| Ron

Hi EC

You have the syntax in you original post of ........Sheets("Inquiry Form
").Rows.("1:100").Copy
There should not be a full stop after "Rows". Is other code running in the
background to prevent your code from executing? Is the workbook protected?
Are any sheets hidden since your code selects both sheets and ranges (both
of which are unnecessary actually)? Does the sheet "Inquiry Form " actually
have a trailing space at the end?

To isolate the problem I'd copy the code to a new workbook, name a sheet
"Inquiry Form " and run the code - if that works then something is
preventing the code from executing in the "real" workbook. I'd be interested
to know the cause if you do find out.

--
XL2002
Regards

William


"EC" wrote in message
om...
| William,
|
| Thanks for taking the time to test the code and to respond.
|
| Given that the code worked successfully for you, in your opinion,
| could there be something in the environment/setup of the Win2000
| machines that does not allow this code to exhibit the expected
| behavior? Someone thought that under VBA References I might find
| missing libraries... but I do not see anything that would indicate
| this to be the case.
|
| Do you (or anyone else) have something to add?
|
| Thanks again,
| Ron
|
|
================================================== ==========================
===
|
================================================== ==========================
===
|
| "William" wrote in message
...
| Hi
|
| Your code worked for me with Windows 2000, XL2002.
|
| This also worked.
|
| Sub DeleteAndCopy()
| Application.DisplayAlerts = False
| Application.ScreenUpdating = False
| Dim ws As Worksheet, ws1 As Worksheet, x As String
| Set ws1 = Sheets("Inquiry Form ")
| x = ws1.Name
| Set ws = Sheets.Add
| ws.Move after:=ws1
| ws1.Rows("1:100").Copy ws.Range("A1")
| ws1.Delete
| ws.Name = x
| Application.DisplayAlerts = True
| Application.ScreenUpdating = True
| End Sub
|
|
| --
| XL2002
| Regards
|
| William
|
|
| "EC" wrote in message
| m...
| | With apologies to those who may have answered similar questions in the
| | past... problem for me it that I just can not get this to work.
| |
| | Background: VBA code was developed under Win95 (yes, client still has
| | Win95 machines) in Excel-97 where it seems to work just fine. On a NT
| | machine with Excel-97 it seems to work but this the code is not fully
| | tested on that machine. Under Win2000 with Excel 2002, the code does
| | not work. It steps through certain code but make no change to the
| | worksheet(s).
| |
| | Issue:
| | Add a new worksheet, copy first 100 rows from an existing sheet, same
| | workbook to the first 100 rows of new sheet, delete the original
| | sheet, rename the new sheet to the old sheet name (.delete and .name
| | statements do nothing).
| |
| | Application.Sheets.Add Type:="Worksheet"
| | Application.ActiveSheet.Move after:=Worksheets("Inquiry Form ")
| | Application.ActiveSheet.Name = "NewInquiryForm"
| | Sheets("Inquiry Form ").Rows.("1:100").Copy
| | Sheets("NewInquiryForm").Activate
| | Range("A1").Select
| | ActiveSheet.Paste
| |
| | Sheets("Inquiry Form ").Activate
| | ActiveWindow.SelectedSheets.Delete
| | Sheets("NewInquiryForm").Activate
| | Sheets("NewInquiryForm").Name = "Inquiry Form "
| |
| |
| | When the above .delete and .name statements did nothing, I also tried:
| |
| | Sheets("Inquiry Form ").Activate
| | ThisWorkBook.Names("Inquiry Form ").Delete
| | Sheets("NewInquiryForm").Activate
| | ThisWorkBook.Names("NewInquiryForm").Name = "Inquiry Form "
| |
| | Any assistance would be greatly appreciated!
| | Thanks,
| | Ron

The extra stop after "rows" was a typographical error caused by my
typing skills while needing sleep. I was re-typing a few lines of
code to add to the original post. The typo error does NOT appear in
my VBA code.

In answer to your questions, I execute an UNPROTECT statement,
immediately after opening the workbook. There are no hidden sheets
other than PERSONAL.xls.
Yes, the trailing space is part of the actual filename. This is
inherited from the workbook distributed to the field and is outside my
control to correct. Well, I can make the correction to the
distributed form, but I can not force the field force to actually use
the new/corrected form.

The next three days are *crazy* for me, but hopefully in the next 24
hours I will be able to try your suggestion to put the code in another
workbook and repeat the test. Although not required, I should
probably remove the code from the personal macro file "PERSONAL.xls"
where it is currently stored and executed.

Yes, for offering assistance, you are certainly entitled to know the
solution if/when one is found.

As an side, did you see the posting by Chip Pearson in answer to a
post titled, "Upgrading from Office 97 to XP"? In it he states...

"VBA version 6 (Office 2000 and Office XP/2002) can *perfectly*
read and execute code written in VBA version 5 (Office 97). As
a professional developer, I have Office 97, 2000, and 2002 all
on the same computer. I develop programs in the earliest version
required by the client, and they all work fine in later versions
(e.g., writing code in 97 works fine in 2002). Note, however,
that the reverse is not true. You cannot write code in 2002 and
expect that it will necessarily work in 97, although if you are
careful, it will.

The rumors and your friend are wrong. Office97 and Office 2002
co-exist very peacefully. Don't worry about it."

Makes me believe that the issue may be environmental. The machines I
am testing the code on were all formatted from the same "build". If
the original has a defect or missing component, then likely all the
machines have the same defect. Perhaps I should have Excel
reinstalled to eliminate that as a possibility.

Thanks for continuing to post...

Regards,
Ron (a.k.a. "EC")

===========================================
===========================================
"William" wrote in message ...
Hi EC

You have the syntax in you original post of ........Sheets("Inquiry Form
").Rows.("1:100").Copy
There should not be a full stop after "Rows". Is other code running in the
background to prevent your code from executing? Is the workbook protected?
Are any sheets hidden since your code selects both sheets and ranges (both
of which are unnecessary actually)? Does the sheet "Inquiry Form " actually
have a trailing space at the end?

To isolate the problem I'd copy the code to a new workbook, name a sheet
"Inquiry Form " and run the code - if that works then something is
preventing the code from executing in the "real" workbook. I'd be interested
to know the cause if you do find out.

--
XL2002
Regards

William


"EC" wrote in message
om...
| William,
|
| Thanks for taking the time to test the code and to respond.
|
| Given that the code worked successfully for you, in your opinion,
| could there be something in the environment/setup of the Win2000
| machines that does not allow this code to exhibit the expected
| behavior? Someone thought that under VBA References I might find
| missing libraries... but I do not see anything that would indicate
| this to be the case.
|
| Do you (or anyone else) have something to add?
|
| Thanks again,
| Ron
|
|
| ===========================================
| ===========================================
|
| "William" wrote in message
...
| Hi
|
| Your code worked for me with Windows 2000, XL2002.
|
| This also worked.
|
| Sub DeleteAndCopy()
| Application.DisplayAlerts = False
| Application.ScreenUpdating = False
| Dim ws As Worksheet, ws1 As Worksheet, x As String
| Set ws1 = Sheets("Inquiry Form ")
| x = ws1.Name
| Set ws = Sheets.Add
| ws.Move after:=ws1
| ws1.Rows("1:100").Copy ws.Range("A1")
| ws1.Delete
| ws.Name = x
| Application.DisplayAlerts = True
| Application.ScreenUpdating = True
| End Sub
|
|
| --
| XL2002
| Regards
|
| William
|
|
| "EC" wrote in message
| m...
| | With apologies to those who may have answered similar questions in the
| | past... problem for me it that I just can not get this to work.
| |
| | Background: VBA code was developed under Win95 (yes, client still has
| | Win95 machines) in Excel-97 where it seems to work just fine. On a NT
| | machine with Excel-97 it seems to work but this the code is not fully
| | tested on that machine. Under Win2000 with Excel 2002, the code does
| | not work. It steps through certain code but make no change to the
| | worksheet(s).
| |
| | Issue:
| | Add a new worksheet, copy first 100 rows from an existing sheet, same
| | workbook to the first 100 rows of new sheet, delete the original
| | sheet, rename the new sheet to the old sheet name (.delete and .name
| | statements do nothing).
| |
| | Application.Sheets.Add Type:="Worksheet"
| | Application.ActiveSheet.Move after:=Worksheets("Inquiry Form ")
| | Application.ActiveSheet.Name = "NewInquiryForm"
| | Sheets("Inquiry Form ").Rows.("1:100").Copy
| | Sheets("NewInquiryForm").Activate
| | Range("A1").Select
| | ActiveSheet.Paste
| |
| | Sheets("Inquiry Form ").Activate
| | ActiveWindow.SelectedSheets.Delete
| | Sheets("NewInquiryForm").Activate
| | Sheets("NewInquiryForm").Name = "Inquiry Form "
| |
| |
| | When the above .delete and .name statements did nothing, I also tried:
| |
| | Sheets("Inquiry Form ").Activate
| | ThisWorkBook.Names("Inquiry Form ").Delete
| | Sheets("NewInquiryForm").Activate
| | ThisWorkBook.Names("NewInquiryForm").Name = "Inquiry Form "
| |
| | Any assistance would be greatly appreciated!
| | Thanks,
| | Ron

You only posted a snippet of code.

Is there a chance that you have an "on error resume next" somewhere in your
code. If yes, comment it out or add "on error goto 0" right after the code you
know may fail.



EC wrote:

The extra stop after "rows" was a typographical error caused by my
typing skills while needing sleep. I was re-typing a few lines of
code to add to the original post. The typo error does NOT appear in
my VBA code.

In answer to your questions, I execute an UNPROTECT statement,
immediately after opening the workbook. There are no hidden sheets
other than PERSONAL.xls.
Yes, the trailing space is part of the actual filename. This is
inherited from the workbook distributed to the field and is outside my
control to correct. Well, I can make the correction to the
distributed form, but I can not force the field force to actually use
the new/corrected form.

The next three days are *crazy* for me, but hopefully in the next 24
hours I will be able to try your suggestion to put the code in another
workbook and repeat the test. Although not required, I should
probably remove the code from the personal macro file "PERSONAL.xls"
where it is currently stored and executed.

Yes, for offering assistance, you are certainly entitled to know the
solution if/when one is found.

As an side, did you see the posting by Chip Pearson in answer to a
post titled, "Upgrading from Office 97 to XP"? In it he states...

"VBA version 6 (Office 2000 and Office XP/2002) can *perfectly*
read and execute code written in VBA version 5 (Office 97). As
a professional developer, I have Office 97, 2000, and 2002 all
on the same computer. I develop programs in the earliest version
required by the client, and they all work fine in later versions
(e.g., writing code in 97 works fine in 2002). Note, however,
that the reverse is not true. You cannot write code in 2002 and
expect that it will necessarily work in 97, although if you are
careful, it will.

The rumors and your friend are wrong. Office97 and Office 2002
co-exist very peacefully. Don't worry about it."

Makes me believe that the issue may be environmental. The machines I
am testing the code on were all formatted from the same "build". If
the original has a defect or missing component, then likely all the
machines have the same defect. Perhaps I should have Excel
reinstalled to eliminate that as a possibility.

Thanks for continuing to post...

Regards,
Ron (a.k.a. "EC")

===========================================
===========================================
"William" wrote in message ...
Hi EC

You have the syntax in you original post of ........Sheets("Inquiry Form
").Rows.("1:100").Copy
There should not be a full stop after "Rows". Is other code running in the
background to prevent your code from executing? Is the workbook protected?
Are any sheets hidden since your code selects both sheets and ranges (both
of which are unnecessary actually)? Does the sheet "Inquiry Form " actually
have a trailing space at the end?

To isolate the problem I'd copy the code to a new workbook, name a sheet
"Inquiry Form " and run the code - if that works then something is
preventing the code from executing in the "real" workbook. I'd be interested
to know the cause if you do find out.

--
XL2002
Regards

William


"EC" wrote in message
om...
| William,
|
| Thanks for taking the time to test the code and to respond.
|
| Given that the code worked successfully for you, in your opinion,
| could there be something in the environment/setup of the Win2000
| machines that does not allow this code to exhibit the expected
| behavior? Someone thought that under VBA References I might find
| missing libraries... but I do not see anything that would indicate
| this to be the case.
|
| Do you (or anyone else) have something to add?
|
| Thanks again,
| Ron
|
|
| ===========================================
| ===========================================
|
| "William" wrote in message
...
| Hi
|
| Your code worked for me with Windows 2000, XL2002.
|
| This also worked.
|
| Sub DeleteAndCopy()
| Application.DisplayAlerts = False
| Application.ScreenUpdating = False
| Dim ws As Worksheet, ws1 As Worksheet, x As String
| Set ws1 = Sheets("Inquiry Form ")
| x = ws1.Name
| Set ws = Sheets.Add
| ws.Move after:=ws1
| ws1.Rows("1:100").Copy ws.Range("A1")
| ws1.Delete
| ws.Name = x
| Application.DisplayAlerts = True
| Application.ScreenUpdating = True
| End Sub
|
|
| --
| XL2002
| Regards
|
| William
|
|
| "EC" wrote in message
| m...
| | With apologies to those who may have answered similar questions in the
| | past... problem for me it that I just can not get this to work.
| |
| | Background: VBA code was developed under Win95 (yes, client still has
| | Win95 machines) in Excel-97 where it seems to work just fine. On a NT
| | machine with Excel-97 it seems to work but this the code is not fully
| | tested on that machine. Under Win2000 with Excel 2002, the code does
| | not work. It steps through certain code but make no change to the
| | worksheet(s).
| |
| | Issue:
| | Add a new worksheet, copy first 100 rows from an existing sheet, same
| | workbook to the first 100 rows of new sheet, delete the original
| | sheet, rename the new sheet to the old sheet name (.delete and .name
| | statements do nothing).
| |
| | Application.Sheets.Add Type:="Worksheet"
| | Application.ActiveSheet.Move after:=Worksheets("Inquiry Form ")
| | Application.ActiveSheet.Name = "NewInquiryForm"
| | Sheets("Inquiry Form ").Rows.("1:100").Copy
| | Sheets("NewInquiryForm").Activate
| | Range("A1").Select
| | ActiveSheet.Paste
| |
| | Sheets("Inquiry Form ").Activate
| | ActiveWindow.SelectedSheets.Delete
| | Sheets("NewInquiryForm").Activate
| | Sheets("NewInquiryForm").Name = "Inquiry Form "
| |
| |
| | When the above .delete and .name statements did nothing, I also tried:
| |
| | Sheets("Inquiry Form ").Activate
| | ThisWorkBook.Names("Inquiry Form ").Delete
| | Sheets("NewInquiryForm").Activate
| | ThisWorkBook.Names("NewInquiryForm").Name = "Inquiry Form "
| |
| | Any assistance would be greatly appreciated!
| | Thanks,
| | Ron

--

Dave Peterson