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

Maybe..

I noticed that you have spaces in (and after! the Sheet names..
maybe it helps if you put SingleQuotes ' = chr(39) around the sheetnames
for your references.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(EC) wrote:

Hello Dave,

Thanks for your post.

and .name code).

Sheets("Inquiry Form ").Rows.("1:100").Copy
Sheets("NewInquiryForm").Activate
Range("A1").Select
ActiveSheet.Paste

Next, I created a new workbook (manually) with three worksheets. One
snip

In an earlier post, you said that this was an error in the post:
Sheets("Inquiry Form ").Rows.("1:100").Copy
(but not the code)

But it showed up again. Are you positive that it's not in the code?

And I think that error message is kind of important.

And where is this code located? Is it in a General module or behind a
worksheet/Thisworkbook?

You seem reluctant to post the routine. Is it that large? Or would it take a
lot of setup time to create the test data?


EC wrote:

Hello Dave,

Thanks for your post.

Yes, there is a line of code for "On Error Resume Next" a few lines
above the subject code. Before we discuss this, let's back up to
earlier today.

First of all, I had the in-house tech reload Microsoft Office 2002.
Then, I ran the code with the same result (no action by the .delete
and .name code).

Next, I searched for all references to "On Error Resume Next", found
two, one just after the "add" and just before the "move after" and
"name" code. After commenting out both "On Error Resume Next" lines
of code, I ran the code. An error was raised on the "paste" line (see
below). Off-hand, I do not recall the error text, but I can certainly
raise the exception again.

Sheets("Inquiry Form ").Rows.("1:100").Copy
Sheets("NewInquiryForm").Activate
Range("A1").Select
ActiveSheet.Paste

Next, I created a new workbook (manually) with three worksheets. One
of the sheets is named "Inquiry Form ". I started the code and jumped
down to the section with the subject code. The code SUCCESSFULLY
executed the "add", "move after", "name", "copy", "paste",
"pasteformats", "activate", "delete", and "name". In other words, all
the code executed and yielded the expected results.

How this is possible has yet to be determined. However, I will say
this... The workbook that does NOT work on the Win2000 machines was
created in Excel97. It was protected when opened and unprotected via
the code as the first line after the "open" statement. The workbook
that processed correctly was created on a Win2000 machine in Excel
2002. This workbook was never protected.

To put this issue to rest, I need to determine what it is about the
source files that some can be processed and not others.

Thanks for your interest and help in this endevor. And thanks in
advance to you and others for any help or insight you may offer.

Ron
(EC)

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

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

William,

To save time (which I seem to have little to spare), allow me to
respond to keepITcool and Dave within your post response... then to
you... thanks!

To keepITcool: The embedded spaces in the sheetnames are as they
usually appear in the input sheets (a sample sheet used by salesmen).
But as you suggestion, I will consider the quotes... thanks.

To Dave: The reappearance of the extra period in the subject
line of code happened (again) because I did a cut and paste from my
earlier posting. I swear to you that it is not in the actual code.
Time permitting, I will recrate the error message for you. The code
is located on three test machines, as a module of PERSONAL.xls. The
code is not in the input workbook coming to us from the field. I am
not reluctant to post the code. Rather, I thought I would save space
rather than post 1300 lines (including blank lines). As for providing
a test file, I would expect that most people would be reluctant to
open anything other than text. If I were to post it, it would need to
be a direct email to you, yes? Or can files be posted to the forum?

To William: The inbound worksheet can be as large as 64K lines.
"Real" data appears in rows 8 through 100. Unfortunately, the
(inexperienced) person that designed the original sample workbook put
a table in row 64 thousand something-or-other. To save time (have all
day meeting today and tomorrow), let's just say that is part of what
the .add, .name. and-so-forth code is about. "Yes" to there being
comments in the first few rows. "Yes" to merged cells being copied to
the "added" sheet. I had to "undo the merge" as it was restricting
column inserts. As mentioned above, the code is on three machines,
stored as a module under PERSONAL.xls. "Yes", since the "add" works,
I am positive that I am in the correct workbook/sheet at the time the
code executes. I am beginning to think that Dave is correct. I
should post the entire code... but first, my meeting starts in a few
minutes and I have calls to return. I will get back to the forum as
time permits.

Before I go... I sincerely appreciate everyone's willingness to help
find a solution. Much appreciated!

Ron
(EC)

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

"William" wrote in message ...
Hi Ron

A few comments...

Before looking at the comments below, enter this before the first line of
the code you posted...
Application.EnableEvents=False
and the following line directly before the "End Sub" line
Application.EnableEvents=True
Now run the code.

If that fails.....
-1- I'm guessing you have a fairly large workbook. As you have run this
code a number of times, are you absolutely certain that you do not already
have a sheet in the workbook called "NewInquiryForm"
-2- Enter this line directly after the "ActiveSheet.Paste" line
Application.CutCopyMode = False
-3- Any shapes, objects or comments in (or partly in) rows 1:100?
-4- Any "merged" cells being copied?
-5- In your original post you said "There are no hidden sheets other than
PERSONAL.xls." Presumably you meant no hidden workbooks? Where are you
running this code from - your Personal.xls file or the workbook you are
working on?
-6- When this code kicks in, are you sure the active workbook is the
workbook you want to add the new sheet to?

--
XL2002
Regards

William


"EC" wrote in message
om...
| Hello Dave,
|
| Thanks for your post.
|
| Yes, there is a line of code for "On Error Resume Next" a few lines
| above the subject code. Before we discuss this, let's back up to
| earlier today.
|
| First of all, I had the in-house tech reload Microsoft Office 2002.
| Then, I ran the code with the same result (no action by the .delete
| and .name code).
|
| Next, I searched for all references to "On Error Resume Next", found
| two, one just after the "add" and just before the "move after" and
| "name" code. After commenting out both "On Error Resume Next" lines
| of code, I ran the code. An error was raised on the "paste" line (see
| below). Off-hand, I do not recall the error text, but I can certainly
| raise the exception again.
|
| Sheets("Inquiry Form ").Rows.("1:100").Copy
| Sheets("NewInquiryForm").Activate
| Range("A1").Select
| ActiveSheet.Paste
|
| Next, I created a new workbook (manually) with three worksheets. One
| of the sheets is named "Inquiry Form ". I started the code and jumped
| down to the section with the subject code. The code SUCCESSFULLY
| executed the "add", "move after", "name", "copy", "paste",
| "pasteformats", "activate", "delete", and "name". In other words, all
| the code executed and yielded the expected results.
|
| How this is possible has yet to be determined. However, I will say
| this... The workbook that does NOT work on the Win2000 machines was
| created in Excel97. It was protected when opened and unprotected via
| the code as the first line after the "open" statement. The workbook
| that processed correctly was created on a Win2000 machine in Excel
| 2002. This workbook was never protected.
|
| To put this issue to rest, I need to determine what it is about the
| source files that some can be processed and not others.
|
| Thanks for your interest and help in this endevor. And thanks in
| advance to you and others for any help or insight you may offer.
|
| Ron
| (EC)
|
| ===========================================
| ===========================================
|
| Dave Peterson wrote in message
...
| 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

Ahh. I see why you were hesitant.

I skinnied down your code to just the apply_format section and that portion
worked ok for me (xl2002/win98).

But this section looked plenty weird to me:

Sheets("Inquiry Form ").Activate
'
ActiveWindow.SelectedSheets.Delete
Sheets("NewInquiryForm").Activate
'
Sheets("NewInquiryForm").Name = "Inquiry Form"

You delete "inquiry From " (with a trailing space) and rename the
"newinquiryform" to "inquiry form" without the trailing space.

With the "on error resume next" still active, maybe that caused the error.

With all the workbooks and folders that it uses, I don't think I'd want to
recreate the environment to make testing worthwhile.

But if I were debugging this, I'd turn off all "on error resume next" lines
(looks like you got both). And I'd turn off all the "application.displayalerts
= false" lines. I'd step through the code with F8's (answering the alert
warnings nicely).

It might help find it.

Sorry about not being any real help.

Good luck,





EC wrote:

--

Dave Peterson

Hi Ron, Dave

In the original code posted, the line
Sheets("NewInquiryForm").Name = "Inquiry Form"
did have a trailing space
Sheets("NewInquiryForm").Name = "Inquiry Form "

Like Dave, I did not recreate the environment to run the entire macro but if
the original code you posted ran without problem in a different workbook,
then you should step through that code line by line in the "real" workbook
to identify where the first error is.

--
XL2002
Regards

William


"Dave Peterson" wrote in message
...
| Ahh. I see why you were hesitant.
|
| I skinnied down your code to just the apply_format section and that
portion
| worked ok for me (xl2002/win98).
|
| But this section looked plenty weird to me:
|
| Sheets("Inquiry Form ").Activate
| '
| ActiveWindow.SelectedSheets.Delete
| Sheets("NewInquiryForm").Activate
| '
| Sheets("NewInquiryForm").Name = "Inquiry Form"
|
| You delete "inquiry From " (with a trailing space) and rename the
| "newinquiryform" to "inquiry form" without the trailing space.
|
| With the "on error resume next" still active, maybe that caused the error.
|
| With all the workbooks and folders that it uses, I don't think I'd want to
| recreate the environment to make testing worthwhile.
|
| But if I were debugging this, I'd turn off all "on error resume next"
lines
| (looks like you got both). And I'd turn off all the
"application.displayalerts
| = false" lines. I'd step through the code with F8's (answering the alert
| warnings nicely).
|
| It might help find it.
|
| Sorry about not being any real help.
|
| Good luck,
|
|
|
|
|
| EC wrote:
|
| --
|
| Dave Peterson
|

William & Dave,

Sorry for taking so long to respond to your prior posts. Here's what
has happened since I last posted.

As recommended, I commented all "On Error Resume Next" statements and
stepped through the code. In the "problematic" section (the earlier
posted "snippet"), I found two errors:

(1)
Sheets("NewInquiryForm").Select
Range("A1").Select
ActiveSheet.Paste

Raised Error: Method 'Paste' of object '_Worksheet' failed


(2)
Sheets("NewInquiryForm").Select
ActiveSheet.Paste Destination:=Worksheets("NewInquiryForm").Range("A 1")

Raised Error: Automation error; The object invoked has
disconnected from its clients

I have reworked this section of code with the macro recorder and
pasting the replacement code into the macro. So far, all works
well... looks like this:

Application.Sheets.Add Type:="Worksheet"
Application.ActiveSheet.Name = "NewInquiryForm"
Application.ActiveSheet.Move After:=Worksheets("Inquiry Form ")
Sheets("NewInquiryForm").Select
Application.CutCopyMode = False
Sheets("Inquiry Form ").Select
Range("A1:X100").Select
Selection.Copy
Sheets("NewInquiryForm").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Inquiry Form ").Select
Range("A1:X100").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("NewInquiryForm").Select
Range("A1:X100").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Inquiry Form ").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("NewInquiryForm").Select
Sheets("NewInquiryForm").Name = "Inquiry Form "

Incidently, I have not reinstated the "On Error Resume Next"
statements in any of the logic. Futher, there is only one reference to
"DisplayAlerts = False" which in followed, two lines later with
"DisplayAlerts = True".

The plan for today is to test with more input sheets on the
Win2000-Excel2002 machine. If all continues to work, then I will
re-test with Excel97.

Thanks for taking the time for help me to work through this issue. If
you have any further comments or suggestions, please post... always
good to get another perspective.

Regards,
Ron (EC)


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

"William" wrote in message ...
Hi Ron, Dave

In the original code posted, the line
Sheets("NewInquiryForm").Name = "Inquiry Form"
did have a trailing space
Sheets("NewInquiryForm").Name = "Inquiry Form "

Like Dave, I did not recreate the environment to run the entire macro but if
the original code you posted ran without problem in a different workbook,
then you should step through that code line by line in the "real" workbook
to identify where the first error is.

--
XL2002
Regards

William


"Dave Peterson" wrote in message
...
| Ahh. I see why you were hesitant.
|
| I skinnied down your code to just the apply_format section and that
portion
| worked ok for me (xl2002/win98).
|
| But this section looked plenty weird to me:
|
| Sheets("Inquiry Form ").Activate
| '
| ActiveWindow.SelectedSheets.Delete
| Sheets("NewInquiryForm").Activate
| '
| Sheets("NewInquiryForm").Name = "Inquiry Form"
|
| You delete "inquiry From " (with a trailing space) and rename the
| "newinquiryform" to "inquiry form" without the trailing space.
|
| With the "on error resume next" still active, maybe that caused the error.
|
| With all the workbooks and folders that it uses, I don't think I'd want to
| recreate the environment to make testing worthwhile.
|
| But if I were debugging this, I'd turn off all "on error resume next"
lines
| (looks like you got both). And I'd turn off all the
"application.displayalerts
| = false" lines. I'd step through the code with F8's (answering the alert
| warnings nicely).
|
| It might help find it.
|
| Sorry about not being any real help.
|
| Good luck,
|
|
|
|
|
| EC wrote:
|
| --
|
| Dave Peterson
|

Glad you got it working.

I feel a lot better when I use "on error resume next" sparingly. And then turn
it off right after the code that causes error I expect to sometimes occur is
finished. Same with .displayalerts.

And just because the code runs to completion, it doesn't mean that it did what I
wanted. Those resume next lines mask too much to be left on.

EC wrote:

William & Dave,

Sorry for taking so long to respond to your prior posts. Here's what
has happened since I last posted.

As recommended, I commented all "On Error Resume Next" statements and
stepped through the code. In the "problematic" section (the earlier
posted "snippet"), I found two errors:

--

Dave Peterson