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