simple macro to copy a sheet, object undefined error

I'm still having trouble with objects, but i can't figure out which
one.

Sub CopySheet()
Dim wb As Workbook
Set wb = ThisWorkbook

wb.Sheets(2).Copy Type:=xlWorksheet, after:=Sheets(2)

End Sub

tnx,
0
jlrough (56)
1/18/2008 9:05:47 PM
excel 39879 articles. 2 followers. Follow

7 Replies
571 Views

Similar Articles

[PageSpeed] 11

How about:

Option Explicit
Sub CopySheet()
  Dim wb As Workbook
  Set wb = ThisWorkbook
  wb.Sheets(2).Copy after:=wb.Sheets(2)
End Sub


Excel-General wrote:
> 
> I'm still having trouble with objects, but i can't figure out which
> one.
> 
> Sub CopySheet()
> Dim wb As Workbook
> Set wb = ThisWorkbook
> 
> wb.Sheets(2).Copy Type:=xlWorksheet, after:=Sheets(2)
> 
> End Sub
> 
> tnx,

-- 

Dave Peterson
0
petersod (12005)
1/18/2008 9:26:10 PM
On Jan 18, 1:26 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> How about:
>
> Option Explicit
> Sub CopySheet()
>   Dim wb As Workbook
>   Set wb = ThisWorkbook
>   wb.Sheets(2).Copy after:=wb.Sheets(2)
> End Sub
>
> Excel-General wrote:
>
> > I'm still having trouble with objects, but i can't figure out which
> > one.
>
> > Sub CopySheet()
> > Dim wb As Workbook
> > Set wb = ThisWorkbook
>
> > wb.Sheets(2).Copy Type:=xlWorksheet, after:=Sheets(2)
>
> > End Sub
>
> > tnx,
>
> --
>
> Dave Peterson

thanks,
0
jlrough (56)
1/18/2008 10:01:50 PM


On Jan 18, 1:26 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> How about:
>
> Option Explicit
> Sub CopySheet()
>   Dim wb As Workbook
>   Set wb = ThisWorkbook
>   wb.Sheets(2).Copy after:=wb.Sheets(2)
> End Sub
>
> Excel-General wrote:
>
> > I'm still having trouble with objects, but i can't figure out which
> > one.
I revised it slightly to rename the copied sheet.  It works but it
doens't rename the sheet.  How can I fix it so it names my new sheet
with a global variable name.  Thanks
> > Sub CopySheet()
> > Dim wb As Workbook
> > Set wb = ThisWorkbook
>
> > wb.Sheets(2).Copy Type:=xlWorksheet, after:=Sheets(2)
>
> > End Sub
>
> > tnx,
>
> --
>
> Dave Peterson

Hi, I revised it slightly to rename the new sheet however it doesn't
rename the sheet.  g_Fname is a global variable.
I would like it to name the new sheet moved to index 2 with the global
first name.  Thanks.

Sub CopySheet()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook

wb.Sheets(2).Copy after:=Sheets(2)
Set ws = wb.Sheets(3)
ws.Name = "m_Fname"
End Sub

0
jlrough (56)
1/18/2008 10:47:53 PM
You didn't qualify the "after:=" sheets.  You didn't use g_Fname anywhere in
your code.  And if you use "before:=" instead of "after:=", then the sheet will
be in the correct location.  (Or use after:=wb.sheets(1))

Sub CopySheet()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook

wb.Sheets(2).Copy before:=wb.Sheets(2)
Set ws = wb.Sheets(2)
ws.Name = g_Fname 
End Sub

Excel-General wrote:
<<snipped>>
> 
> Hi, I revised it slightly to rename the new sheet however it doesn't
> rename the sheet.  g_Fname is a global variable.
> I would like it to name the new sheet moved to index 2 with the global
> first name.  Thanks.
> 
> Sub CopySheet()
> Dim wb As Workbook
> Dim ws As Worksheet
> Set wb = ThisWorkbook
> 
> wb.Sheets(2).Copy after:=Sheets(2)
> Set ws = wb.Sheets(3)
> ws.Name = "m_Fname"
> End Sub

-- 

Dave Peterson
0
petersod (12005)
1/19/2008 12:57:14 AM
On Jan 18, 4:57 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> You didn't qualify the "after:=" sheets.  You didn't use g_Fname anywhere in
> your code.  And if you use "before:=" instead of "after:=", then the sheet will
> be in the correct location.  (Or use after:=wb.sheets(1))
>
> Sub CopySheet()
> Dim wb As Workbook
> Dim ws As Worksheet
> Set wb = ThisWorkbook
>
> wb.Sheets(2).Copy before:=wb.Sheets(2)
> Set ws = wb.Sheets(2)
> ws.Name = g_Fname
> End Sub
>
> Excel-General wrote:
>
> <<snipped>>
>
>
>
> > Hi, I revised it slightly to rename the new sheet however it doesn't
> > rename the sheet.  g_Fname is a global variable.
> > I would like it to name the new sheet moved to index 2 with the global
> > first name.  Thanks.
thanks,
>
> > Sub CopySheet()
> > Dim wb As Workbook
> > Dim ws As Worksheet
> > Set wb = ThisWorkbook
>
> > wb.Sheets(2).Copy after:=Sheets(2)
> > Set ws = wb.Sheets(3)
> > ws.Name = "m_Fname"
> > End Sub
>
> --
>
> Dave Peterson

0
jlrough (56)
1/19/2008 1:24:08 AM
On Jan 18, 4:57 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> You didn't qualify the "after:=" sheets.  You didn't use g_Fname anywhere in
> your code.  And if you use "before:=" instead of "after:=", then the sheet will
> be in the correct location.  (Or use after:=wb.sheets(1))
>
> Sub CopySheet()
> Dim wb As Workbook
> Dim ws As Worksheet
> Set wb = ThisWorkbook
>
> wb.Sheets(2).Copy before:=wb.Sheets(2)
> Set ws = wb.Sheets(2)
> ws.Name = g_Fname
> End Sub
>
> Excel-General wrote:
>
> <<snipped>>
>
>
>
> > Hi, I revised it slightly to rename the new sheet however it doesn't
> > rename the sheet.  g_Fname is a global variable.
> > I would like it to name the new sheet moved to index 2 with the global
> > first name.  Thanks.
>
> > Sub CopySheet()
> > Dim wb As Workbook
> > Dim ws As Worksheet
> > Set wb = ThisWorkbook
>
> > wb.Sheets(2).Copy after:=Sheets(2)
> > Set ws = wb.Sheets(3)
> > ws.Name = "m_Fname"
> > End Sub
>
> --
>
> Dave Peterson

That was really smart by the way to do it before and put it in front
of itself.
0
jlrough (56)
1/19/2008 1:25:26 AM
Sometimes, it's an extra pair of eyes helping to get what you need.

Excel-General wrote:
> 
<<snipped>>
> 
> That was really smart by the way to do it before and put it in front
> of itself.

-- 

Dave Peterson
0
petersod (12005)
1/19/2008 1:39:41 AM
Reply:

Similar Artilces:

simplyfy and help on errors
Hello All, I have a formula that is almost working. I am trying make sure that if two specific criteria are met that the revenue column(f in this case) gets multiplied by K1 or K2 depending on the evaluation. I am hoping to simplify the following and be error free. =IF(OR(C50="",D50=""),"",IF(D50="Services A",F50*$K$1,IF(AND(SEARCH("Company A",C50),D50="Services B"),F50*$K$2,IF(D50="Services B",F50*$K$1,"")))) this is returning an Value Error any suggestions on how to go about this. This portion of you...

What's wrong with my macro code?
This problem occours on Office 2007 (with updates) as well as Office 2010 (with (and without) updates). I have a spredsheet containing multiple sheets. When running the macro I want to copy two matrices from sheet 1 ("Scorekort") to sheet 2 ("Statistik") where the values are added to existing matrices. Previously, this was running smoothly. However, since Office 2007 was updated with various updates and now the arrival of Office 2010 the macro doesn't work anymore. It simply screws up the 2 existing matrices on sheet 2 ("Statistik"), which now holds en...

how do I step thru a macro for troubleshooting ?
I am new to macros in Excel. I cannot find any documentation on how to go thru a macro step - by - step to check where a problem may be. Press alt + F11 to open the VBE, find your macro in the project pane to the left, double click, now press F8 overe and over again or press alt + F8, select the macro and choose step into and then keep pressing F8 Regards, Peo Sjoblom "chrisq" wrote: > I am new to macros in Excel. I cannot find any documentation on how to go > thru a macro step - by - step to check where a problem may be. > ...

Problem copying Price Lists
Recently, we seem to have lost the ability to copy price lists. When trying to copy, we can select an item to copy from, but when we click process, GP freezes, and doesn't come back. This also prevents us from copying inventory items. When we tried this, GP froze again. During this process, all the data seems to copy correctly, but the price list will be left blank. We are running version 9, and this function has been working for us up until a couple of days ago. We have not installed any additional GP modules or modifications, so I'm not sure exactly what triggered this pr...

What is error 0x8004014F?
The operation failed. An object could not be found. The above error code. I can't use my outlook. Help. ...

Copy cell of one sheet to another sheet
I need a macro that will copy Cell C3 from one sheet and add it to another sheet starting at A5. If A5 already has something in it then use A6 Thx Bob Sub dk() Dim lr As Long, sh As Worksheet, sh2 As Worksheet Set sh = Sheets("Sheet1") Set sh2 = Sheets("Sheet2") lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row sh.Range("C3").Copy sh2.Range("A" & lr+1) End Sub Change the sheet names to suit. "Bobbo" <Bobbo@discussions.microsoft.com> wrote in message news:8C59F170-A8C0-47F1-A9E2-CB26EE4E1BBD@microsoft.com... &g...

Why do I get an error when comparing two documents?
I am comparing 2 docx documents but an error keeps popping up with "Word cannot compare the documents" ...

custom button error
i am trying to make a custom button in html and javascript to get item history of a special customer. everything is working fine except when i input a value from user in javascript and then call a function it gives me an error Line : 1, Char:1, Object expected error. i am attaching my code at botton if anybody had the same problem and they would like to share the solution. this is the code in javascript . after i click on the submit button it gives me the error object expected at line 1 char 1 imp = imp + " </table><FORM NAME='myform' action=''> What ...

eseutil /p Illegal duplicate key error
Hi all, I have an exchange 2003 server, and when I try to repaire my priv1.edb file I get Operation terminated with error -1605 (JET_errKeyDuplicate, Illegal duplicate ke y) after 64.16 seconds. I can not find any article about this please help me... "hasan" wrote: > Hi all, > I have an exchange 2003 server, and when I try to repaire my priv1.edb file > I get Operation terminated with error -1605 (JET_errKeyDuplicate, Illegal > duplicate ke > y) after 64.16 seconds. > > I can not find any article about this please help me... This error is unrecoverable...

Macro to run without spreadsheet being open
Is there a way to et a macro to run without the sheet being open? Within my macro there is a timer of 00:00:01 to run he macro, but this will only work with the spreadsheet open. Is there a way to run the macro overnight without the sheet being open? See http://www.cpearson.com/excel/OnTime.aspx -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dan Wood" <DanWood@discussions.microsoft.com> wrote in message news:E4BF08BB-6B56-46EB-A1E3-DD312AF8DB6E@microsoft.com... > Is there a way to et a macro to run without the sheet being open? Within my ...

count in a sheet
Hi in an excel's sheet I register customers of a shop in various records, including name, surname, date of birth, address, date of the first purchase, the closing date, paid (where I write yes or not). At the end of each month I have to count for the previous month how many customers the shop has, inlcluding new customers, current adn closed customers, if those customers are new entrants (based on date of first purchase) and how many customers have closed the report. Does it possible to do everything automatically, putting the results in another sheet? (I think yes, but I am not able to d...

... Can I Copy Conditional Formatting...
I have set-up Conditional Formatting on Worksheet # 1, and would like to copy that Conditional Formatting to all my Worksheets. Can I copy the Conditional Formatting I have on Worksheet # 1 to any or all of my additional Worksheets? Darrell "Dr. Darrell" <DrDarrell@discussions.microsoft.com> wrote in message news:7853A626-37FF-404A-8713-E9047AE6AC47@microsoft.com... >I have set-up Conditional Formatting on Worksheet # 1, and would like to >copy > that Conditional Formatting to all my Worksheets. > > Can I copy the Conditional Formatting I have on Workshe...

Linked sheets combined with pivot tables in Access
I have two different kinds of databases (xls and txt) link to this access file and both "creates" this one Pivot table which I can no longer refresh, by only pressing the refresh button. All other kinds of forms were already refreshed, but the pivots not. What am I missing...? How can I refresh the Pivot Queries? Thanks in advance, Rgds, Patricia ...

Updated Calendar Tool (No Macros)
Hi Group, This yearly calendar tool allows users to choose years from 1900 to 2400. Pretty challenging task to do this using spreadsheet programming rather than macro programming. http://www.techronology.com/index.php?pageID=004002&prodID=tool001 Thanks, Alex ...

TreeView Object
Hi there, Can omeone explain to me how to get data to be displayed in the treeview object. I have set it up how i would like the object to display the items in there but how do i get items in the object. bach -- bach ------------------------------------------------------------------------ bach's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26134 View this thread: http://www.excelforum.com/showthread.php?threadid=395664 I think there are a number of treeview controls with different methods & properties Here's some Microsoft guidance on using the...

How do I update copy of worksheet
Is there a way to update a copy of a spreadsheet(2 worksheets of 4 on source) without manually opening the copy and updating the links? If I am understanding your question correctly, then when changing links, you should be prompted when re-openning the file to "Update Links" (yes, no). -- wmaughan ------------------------------------------------------------------------ wmaughan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29743 View this thread: http://www.excelforum.com/showthread.php?threadid=493284 ...

Macro doesn't work
I have a macro which saves my Word doc as a pdf based on words within the document itself. Sometimes I get an error and the macro stops on the following line: ActiveDocument.ExportAsFixedFormat OutputFileName:=sFileName, _ ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _ wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _ Item:=wdExportDocumentContent, IncludeDocProps:=False, KeepIRM:=True, _ CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _ BitmapMissingFonts:=True, UseISO1900...

Macro to save spreadsheet as different excel version
Hi, basically I have a copy of approach '97 which when you export a file to excel does it as excel version 3.0 (no I can't u/g approach unfortunately) Consequently when my macro in Excel imports the data, formats and eventually saves the spreadsheet it saves it as excel version 3.0 The problem is that I use conditional formatting which isn't supported in 3.0 so it doesn't save the spreadsheet properly. Question - how do I amend the following part of my macro to save as the latest version as this only saves as version 3.0? Dim str As String str = Trim(VBA.Format(Now(), &qu...

Copying from one ListControl to Another...
Hello everyone, I have been studying how List Controls work, and now I have two List Controls, A and B. Both of the list controls are in Report_View and they both have the same # of columns. What I would like to do is copy each entry of A into B. I use CListCtrl::GetItem(..) to get the item, and I use CListCtrl::InsertItem(..) to insert the item into B. However, this is not working. It is only inserting the first subitem. Is it possible to copy the entire item i.e, subitems 0-2 from CListCtrl A into CListCtrl B? Thanks ...

Error number: 0x80090008 02-08-10
I've been working with Microsoft for over a month without resolution and it has now come down to reinstalling my operating system which I really would like to avoid. I noticed in December that I was not getting any update notifications so after about 2 weeks I went to the website for updates and when I click express I now get Error number: 0x80090008. This even comes up when I try to update windows defender software. I can't believe that there is no reference to what this error number refers to. Anyone have any suggestions? Thanks. Windows version would be...? W...

Copying my MS Outlook contacts from window based PC to iMAC with entourage
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop <i>was using iPhone for last 3 years &amp; all the contact information was updated / synced with my windows based Laptop through MS Outlook. Now I have moved to iMAC with MS entourage but i am not able to get all my contacts migrated as it is (under all the detailed categories as earlier) to Entourage. Can you please advise me how to proceed?</i> On 2010-05-27 11:43:27 -0400, Paramjeet_S_Rana@officeformac.com said: > was using iPhone for last 3 years & all the cont...

Exchange SSL Error
When attempting to view properties on public folders I get the following err: Exchange System Manager The SSL certificate server name is incorrect. ID no: c103b404 Exchange System Manager I've looked at article Q324345 and made the suggested changes but the error message still comes back. Can anyone out there please help? Thanks Peter ...

Money 2003 Open Error: "Cannot open EULA, reinstall Money"
Microsoft Money 2003 Standard came with my Dell computer that I bought back in December 2003. First time that I tried to open Money03 today, I got an error message immediately: "Cannot display the End User License Agreement which must be displayed and accepted before you can use the application. To display the agreement, reinstall the application." I've reinstalled about 5 times now, using both the Add/Change Programs tool, and by clicking the appropriate installers on the CD. I've also fully removed the program, rebooted, then tried an install. No such EULA ev...

Sheet format
Hi all, Just trying to get the sheet to be fixed in width from 1 to 11 so that when I tab to the next column from the last on the right, it automatically goes down to the next row and starts out at 1 on that next row again. I know it can be done but just haven't found it and really need it. Can anyone help please? Thanks. One way right click sheet tab>insert this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 11 Then Cells(Target.Row + 1, 1).Select End Sub -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Diamontina Cocktail" <lrb...

runtime error 380
i have a store location that is receiving a runtime error 380 when they do a transfer inventory in from another store location. (interstore transfer) when they close the error screen, store operations closes, and they have to log back in. does anyone have any idea what may be causing this and how to remedy the problem? thanks, James Conway It sounds like there may be an item on the transfer that does not exist at the store, but I can't quite tell from your description. Are they trying to receive the transfer in? Are they trying to create a new transfer in request? Is this happe...