activesheet error

Hi
I've done something wrong here and I can't work out what it is...
Error msg:  "Compile Error: With object must be user-defined type, object, 
or Variant"

Sub copylplan()
Dim mySheet As String

mySheet = ActiveSheet
        
    With mySheet
    .Copy After:=Sheets(Sheets.Count)
    End With
    
    With mySheet
    .Name = "Extract Plan"
    End With
End Sub
-- 
Thank for your help
BeSmart
0
Utf
3/14/2010 4:35:01 AM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
749 Views

Similar Articles

[PageSpeed] 7

Hi,

Set the variable as a Worksheet then use Set in the line to assign the 
ActiveSheet to the variable.

You should realize that the variable remains the original ActiveSheet; it 
does not become the new ActiveSheet after the copy and therefore it is the 
original worksheet that gets renamed with the following code.

Sub copylplan()

Dim mySheet As Worksheet

Set mySheet = ActiveSheet
        
    With mySheet
    .Copy After:=Sheets(Sheets.Count)
    End With
    
    With mySheet
    .Name = "Extract Plan"
    End With
    
End Sub


If you want to apply the name to the new sheet then as follows.

Sub copylplan()

Dim mySheet As Worksheet

Set mySheet = ActiveSheet
        
    With mySheet
    .Copy After:=Sheets(Sheets.Count)
    End With
    
    With ActiveSheet
    .Name = "Extract Plan"
    End With
    
End Sub


You can then still reference the original worksheet like follows.

mySheet.Activate


-- 
Regards,

OssieMac



0
Utf
3/14/2010 4:46:01 AM
Reply:

Similar Artilces:

Address errors
I have 10 e-mails to send. The addresses look good but I get two errors. Sometimes they will all send but one will come back undeliverable. So I find the URL is good but the name is bad. Then with another group of 10 I hit 'SEND' and is says an address is immediately rejected. It may or may not tell me which is bad. The message is removed from the Outbox. Did I send the other 9? What is wrong with the address? What does the 'Tools' . 'Check names' do? Does it just check against the address book or does it check the validity of the addresses against internet URL...

Microsoft Money 2005 Internal Error
I keep getting the message "Microsoft Money has identified an internal error and needs to restart." When it restarts I get the same message. I've tried to go in through my .mny file and the same thing happens. Unfortunately, my backup opens, but it is old. Any ideas on how I can get into my current file?? In microsoft.public.money, Marion wrote: >I keep getting the message "Microsoft Money has identified an internal error >and needs to restart." When it restarts I get the same message. I've tried >to go in through my .mny file and the same thi...

Determining error associated with polynomial trend lines.
I have a plot that requires a polynomial trend line to the fourth power that has error associated with the y-axis values only. From this point on, I'm relying on the equation displayed by excel to further manipulate that data, which I have done and double checked with excel and it's corrrect. I'm having difficulty, however, in continuing the calculations of associated error. Is Excel capable of outputting the error associated with each coefficient of the generated trend line equation? If it is possible, how do you do it? - BJR See Help for the LINEST function. You mig...

mortgage amortization error
I made a large extra principal payment on my mortgage. MS Money produced an updated amortization schedule showing the new principal payment would be a much larger percentage of my total payment (that is, each payment would include much less interest than before). The actual allocation for the next payment on July 1 followed the original amortization schedule rather than the new one. In dollars, in June my monthly payment of $870 included $584 in principal. In July--after the large extra payment--the amortization schedule showed the July payment would be about $710 in princi...

550 Mailbox Unavailable Errors on Incoming Mail
Hi, We are getting a couple of domains having trouble sending to our domain on our SBS 2003 Server with Exchange 2003 SP1. The two domains are based in 1. Italy and 2. Nepal. This is the error they receive and we do not get their email. No address at our domain will receive email from these domains. Pls Help. I post the two examples below. They both appear to use Oulook Express but that doesn't seem to be the problem. We receive mail from all other domains just fine. Our MX record is: 10 mail.fpmt.org. [TTL=3601] IP=70.103.203.75 [TTL=3601] [US] which is the corect IP address. .7...

After install CRM 3.0 beta, logon error: Authentication Error.
I was installed Microsoft CRM 3.0 Beta(90 days trial PID)- Professional Edition But after installed CRM 3.0 beta, logon error: Authentication Error. I think the trial version should can be used. :( Any answers? "CEO" wrote: > I was installed Microsoft CRM 3.0 Beta(90 days trial PID)- Professional Edition > > But after installed CRM 3.0 beta, logon error: Authentication Error. > > I think the trial version should can be used. :( Due to NDA, questions related tot he Bea builds should be addressed in the private newsgroups. -- Matt Parks MVP - Microsoft CRM &...

Error
I want to hide the insert and format menu items when i begin my program and make them visible again before i close. This is my code for hiding them on open: Private Sub Workbook_Open() CommandBars("Worksheet Menu Bar").Controls ("Format").Visible = False CommandBars("Worksheet Menu Bar").Controls ("Insert").Visible = False End Sub ....but I'm getting Run-time error '91': Object variable or With block variable not set. Any ideas as to why? Thanks, mika. Try Applcation.CommandBars. HTH -Dave "mika." <conceptualness@ho...

#Error error in Form
Hi, I have a database in MS Access 97 which has a form consisting mainly of "Memo" fields. It is accessed by a number of people. Every now and then, if the database is left open or sometimes upon closing it and reopening it, it corrupts some of the fields on the form and displays #Error in the field instead of the actual text. You can then no longer edit any field on the form nor can you edit any field from the back-end in the table linked to the form. You cannot even copy the record directly from the table and paste it back in after removing the #Error message. As a result, you h...

Error message #14
In my last post I didn't say what the exact error message was, sorry. When opening a template, the query cannot generate the information and we receive this error message: Microsoft ODBC Driver Manager Data source name not found and no default driver specified. The computers that it does work on do not have any different ODBC drivers. It only connects to an Access Database on our server. Thanks, Trish ...

#Error when blank
When OwnerLastName is blank I am getting an #Error can it just be blank...Thanks Bob =UCase(Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = " & [tbOwnerID] & ""),""),3)) Is OwnerID a Number field, or a Text field? Also, is this on a form or a report? The Criteria is not correct. Ignoring the Left() and UCase() until you get it working, try this if OwnerID is a Number field: =DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] " = Nz([tbOwnerID],0)) Or, for a Text field: =DLoo...

Getting error in outlook
hi steve here i am getting error in outlook o/s : 98 Error: Unable to display the folder. Microsoft Outlook could not access the specified folder location. Errors have been detected in the file C:\WINDOWS\Local Settings\Application Data\Microsoft\Outlook\Outlook.pst. Quit all mail-enabled applications, and then use the Inbox Repair Tool. any help would be appreciated >hi steve here >i am getting error in outlook >o/s : 98 > >Error: >Unable to display the folder. >Microsoft Outlook could not access the specified folder location. >Errors have been detected in ...

error 80244019 and 80070005 and 8024416 and 80246005
What are the steps to fix them? Thanks -- Kristen Rios How to ask a question http://support.microsoft.com/kb/555375 MowGreen ================ *-343-* FDNY Never Forgotten ================ banthecheck.com "Security updates should *never* have *non-security content* prechecked ...

Count and order by
I'm trying to count how many time each user have been logged in to my website and then sort them desc. But I keep getting an error. SELECT user, count(user) AS logins FROM tsigninlog GROUP BY user order by logins desc; This SQL shows a form where I must enter a parameter value. Why does it not works ? -- My Photo Gallery www.anothermasterpiece.com There is no field called logins. logins doesn't exist until after the query is executed. Therefore your order by column should be count(user). Cheers, Jason Lepack On Aug 16, 2:32 pm, SpookiePower <boxjunk2...@gmail.com> wrote:...

exmerge errors
Hi, i am haivng problem extracting the mailboxes using exmerge any ideas from this log that it creates Than Gar *************************************************************** Microsoft Exchange Mailbox Merge Program, v4.00.02 Start Logging:February 27, 2004 20:37:1 *************************************************************** [20:37:19] Logging Level: Non [20:37:19] Reading settings from file 'C:\Program Files\Exchsrvr\BIN\EXMERGE.INI' [20:37:20] Error 8007203a opening an LDAP connection. ('LDAP://EXCHANGE/rootDSE') (CADRoutines::GetNamingContextData [20:37:20] Accessin...

card reader and errors
Toshiba a305-6905 3gb laptop. 1.5 years. All of a sudden I stick a Sony memory stick and a I tried a filled sd card to ensure it wasn't one of the either and regardless I get an error. Shows up when inserted on my computer but no pics will be displayed because it says the media card needs to be formatted first even with pics on it. So I played along and let the computer tried to format the card and it computer is unable to format. Storage on portable media is not in the lock position. Pics will show up on camera and printer so I know this is computer related only. Shows up o...

Runtime error #10
I suddenly can't send or forward email from my account. A pop up message reads at the top- Microsoft Visual ++ Routine Library Then- Runtime Error! Program c:\programfiles\microsoftoutlook.EXE Abnormal program termination Thanks! ...

Program Error -- EXCEL.exe has generated errors and will be closed by Windows.
Program Error -- EXCEL.exe has generated errors and will be closed by Windows. You will need to restart the program. An error log is being created. [OK] I get this message almost everytime I open Excel now. I have Office Standard 2003 SR-1 and have all the patches from Windows Update. I have not made any changes to Excel or Office in the last several months -- no patches, etc. I ran the Help...Detect & Repair... feature and this still happens. It's about 8/10 times I try to open Excel I get this message now. Any advice??? THanks! Hi Pablo! Close down and re-boot. Empty your Wind...

Error I have never seen before in Exchange 5.5
Just wondering if any other exchange gurus have ever seen this error. I am running exchange 5.5 on a windows 2000 server both the os and exchange are running sp4. The network is also a 2003 active directory network The error is as states: Event Type: Erro Event Source: MSExchangeF Event Category: Non Event ID: 103 Date: 11/14/200 Time: 2:32:11 P User: N/ Computer: EXCHANG Description Unable to open or read a message in the Microsoft Schedule+ Free/Busy Connector inbox from Microsoft Schedule+ Free/Busy Connector (EXCHANGE). It shows up many times a day in the event viewer. It does not seem...

_AfxFindFirstDlgItem and _AfxFindNextDlgItem compilation errors
Hi, I want to use these APIs but since these are not documented anywhere in msdn i cant make out which files to include and that's why it is generating compilation error : undeclared identifier. Any idea how to use these APIs ? thanks & regards ~ Rudresh Hi Rudresh you can try documented GetNextDlgGroupItem or GetNextDlgTabItem -- With Regards Alok Gupta Visit me at http://alok.bizhat.com "I Believe this will Help" "Rudresh" <Rudresh@discussions.microsoft.com> wrote in message news:DEB7B671-A12C-47CF-8782-6B...

SaveAs wk3 file type error message
I'm trying to convert an Excel worksheet to a Lotus 123, wk3, file format. I've had to use Excel 2003 as Excel 2007 no longer appears to offer the Lotus 123 file formats. When I do the SaveAs command in Excel 2003 an error is generated saying "You are attempting to save a file type that is blocked by your registry policy setting." Does anyone know a workaround for this issue? Ken Error message when you try to save a file in Excel 2007 or in Excel 2003: "You are attempting to save a file that is blocked by your registry policy setting" http://s...

errors when emailing
when attempting to email I get an error message saying " The host SMTP could no be found. Please verify that the server name is entered correctly." Thanks -- wildcat Please post the error message in its entirety. You can left click on it to highlight and then right click to copy and then paste it into this thread. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "wildcat" <wildcat@discussions.microsoft.com> wrote in message news:17C80870-EC03-4B14-A5A6-E4B7BFD3D3C6@microsoft.com... > > when attempting to email I g...

SharePoint Workflow
If anyone can help with this. I have a number of records that are entered in a SharePoint List daily. In addition, I have a few workflows that are created whenever a record is added/changed. However, sometimes when a person is in the record the workflow errors out and I receive the "Error Occured" statement. Is there anyway of clearing these errors out without having to go into each record and terminating the workflow? ...

Polling Error
I keep receiving this error when polling one location. "<<Post Message>> No response was received for Message Type 3 (Worksheet Status) We are running RMS V1.2 sp2. The site polls to HQ twice a night. The worksheet is a 401. We get the store data and batch information but it disrupts the interstore transfers. any Help? I'd start checking the event viewer logs on all machines involved, and any other logs the transfer program might have as a start... "Paul Houston / Eastern National" wrote: > I keep receiving this error when polling one location. > &q...

compile error
I have a module that's returning a compile error during a compile. I've placed <<>> around the line that the debugger points to. Any ideas? Thanks in advance! Function Strip(vPhrase, sBadChars As String) ' Purpose: remove any of the characters in sBadChars from vPhrase Dim sPhrase As String Dim i As Integer If IsNull(vPhrase) Or IsEmpty(vPhrase) Or Len(sBadChars) = 0 Then Strip = vPhrase Else sPhrase = vPhrase i = 1 Do Until i > Len(sPhrase) << Do Until InStr(sBadChars, Mid$(sPhrase, i, 1)) = 0 O...

Ribbon error following previous versions of my application
I have been working on an access database for while. I recently decided to edit the ribbon in order to add a new button (a spell check tool). I just added a few lines to the USysRibbons table. I did not do anything else. The button worked as expected for a while. Something eventually went wrong for some reason and I get the below error. However previous versions of my database (developed just before I edited the ribbon and consequently the USysRibbons table) no longer work. It looks that THIS ERROR IS FOLLOWING PREVIOUS VERSIONS OF MY DATABASE. The error I get is as follows: ...