is there anyway to create a clone of an excel sheet in vba?

is there anyway to create a clone of an excel sheet in vba?


0
7/6/2005 4:28:32 AM
excel 39879 articles. 2 followers. Follow

7 Replies
543 Views

Similar Articles

[PageSpeed] 28

Hi Daniel,

To add a copy in the same workbook, try:

Sub Tester
With ActiveWorkbook
    .Sheets("MySheet").Copy After:=.Sheets(Sheets.Count)
End With
End Sub

To create a new single-sheet workbook containing a copy of the sheet, try:

Sub Tester2
ActiveWorkbook.Sheets("MySheet").copy
End sub

---
Regards,
Norman



"Daniel" <softwareengineer98037@yahoo.com> wrote in message 
news:uIBBqHegFHA.2156@TK2MSFTNGP14.phx.gbl...
> is there anyway to create a clone of an excel sheet in vba?
>
> 


0
normanjones (1047)
7/6/2005 5:04:19 AM
Daniel,

Norman is right as usual, but just in case you meant workbook rather than 
worksheet, have a look at the SaveCopyAs method in the vba help file.

Robin Hammond
www.enhanceddatasystems.com

"Norman Jones" <normanjones@whereforartthou.com> wrote in message 
news:%23xV1ngegFHA.460@TK2MSFTNGP09.phx.gbl...
> Hi Daniel,
>
> To add a copy in the same workbook, try:
>
> Sub Tester
> With ActiveWorkbook
>    .Sheets("MySheet").Copy After:=.Sheets(Sheets.Count)
> End With
> End Sub
>
> To create a new single-sheet workbook containing a copy of the sheet, try:
>
> Sub Tester2
> ActiveWorkbook.Sheets("MySheet").copy
> End sub
>
> ---
> Regards,
> Norman
>
>
>
> "Daniel" <softwareengineer98037@yahoo.com> wrote in message 
> news:uIBBqHegFHA.2156@TK2MSFTNGP14.phx.gbl...
>> is there anyway to create a clone of an excel sheet in vba?
>>
>>
>
> 


0
rjNOrhSPAM (52)
7/6/2005 10:00:10 AM
Just a typo that won't matter if it's running against the activeworkbook, but
could matter if the workbook isn't active...

Sub Tester
With ActiveWorkbook
    .Sheets("MySheet").Copy After:=.Sheets(Sheets.Count)
End With
End Sub

is missing a dot in front of sheets.count.

Sub Tester
With ActiveWorkbook
    .Sheets("MySheet").Copy After:=.Sheets(.Sheets.Count)
End With
End Sub

Norman Jones wrote:
> 
> Hi Daniel,
> 
> To add a copy in the same workbook, try:
> 
> Sub Tester
> With ActiveWorkbook
>     .Sheets("MySheet").Copy After:=.Sheets(Sheets.Count)
> End With
> End Sub
> 
> To create a new single-sheet workbook containing a copy of the sheet, try:
> 
> Sub Tester2
> ActiveWorkbook.Sheets("MySheet").copy
> End sub
> 
> ---
> Regards,
> Norman
> 
> "Daniel" <softwareengineer98037@yahoo.com> wrote in message
> news:uIBBqHegFHA.2156@TK2MSFTNGP14.phx.gbl...
> > is there anyway to create a clone of an excel sheet in vba?
> >
> >

-- 

Dave Peterson
0
ec357201 (5290)
7/6/2005 12:14:46 PM
Hi Dave,

Thank  you!

---
Regards,
Norman



"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message 
news:42CBCB36.BA34F06C@netscapeXSPAM.com...
> Just a typo that won't matter if it's running against the activeworkbook, 
> but
> could matter if the workbook isn't active...
>
> Sub Tester
> With ActiveWorkbook
>    .Sheets("MySheet").Copy After:=.Sheets(Sheets.Count)
> End With
> End Sub
>
> is missing a dot in front of sheets.count.
>
> Sub Tester
> With ActiveWorkbook
>    .Sheets("MySheet").Copy After:=.Sheets(.Sheets.Count)
> End With
> End Sub
>
> Norman Jones wrote:
>>
>> Hi Daniel,
>>
>> To add a copy in the same workbook, try:
>>
>> Sub Tester
>> With ActiveWorkbook
>>     .Sheets("MySheet").Copy After:=.Sheets(Sheets.Count)
>> End With
>> End Sub
>>
>> To create a new single-sheet workbook containing a copy of the sheet, 
>> try:
>>
>> Sub Tester2
>> ActiveWorkbook.Sheets("MySheet").copy
>> End sub
>>
>> ---
>> Regards,
>> Norman
>>
>> "Daniel" <softwareengineer98037@yahoo.com> wrote in message
>> news:uIBBqHegFHA.2156@TK2MSFTNGP14.phx.gbl...
>> > is there anyway to create a clone of an excel sheet in vba?
>> >
>> >
>
> -- 
>
> Dave Peterson 


0
normanjones (1047)
7/6/2005 12:21:31 PM
in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference to
the newly created copy of this sheet?

"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:%23xV1ngegFHA.460@TK2MSFTNGP09.phx.gbl...
> Hi Daniel,
>
> To add a copy in the same workbook, try:
>
> Sub Tester
> With ActiveWorkbook
>     .Sheets("MySheet").Copy After:=.Sheets(Sheets.Count)
> End With
> End Sub
>
> To create a new single-sheet workbook containing a copy of the sheet, try:
>
> Sub Tester2
> ActiveWorkbook.Sheets("MySheet").copy
> End sub
>
> ---
> Regards,
> Norman
>
>
>
> "Daniel" <softwareengineer98037@yahoo.com> wrote in message
> news:uIBBqHegFHA.2156@TK2MSFTNGP14.phx.gbl...
> > is there anyway to create a clone of an excel sheet in vba?
> >
> >
>
>


0
7/6/2005 8:57:14 PM
You have this at your other post:

dim newWks as worksheet

with activeworkbook
  .sheets("mysheet).copy _
       before:=.sheets(1)
end with
set newwks = activesheet
newwks.name = "this is a new sheet!"

(the activesheet is the one that just got created.)

Daniel wrote:
> 
> in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference to
> the newly created copy of this sheet?
> 
> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
> news:%23xV1ngegFHA.460@TK2MSFTNGP09.phx.gbl...
> > Hi Daniel,
> >
> > To add a copy in the same workbook, try:
> >
> > Sub Tester
> > With ActiveWorkbook
> >     .Sheets("MySheet").Copy After:=.Sheets(Sheets.Count)
> > End With
> > End Sub
> >
> > To create a new single-sheet workbook containing a copy of the sheet, try:
> >
> > Sub Tester2
> > ActiveWorkbook.Sheets("MySheet").copy
> > End sub
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "Daniel" <softwareengineer98037@yahoo.com> wrote in message
> > news:uIBBqHegFHA.2156@TK2MSFTNGP14.phx.gbl...
> > > is there anyway to create a clone of an excel sheet in vba?
> > >
> > >
> >
> >

-- 

Dave Peterson
0
ec357201 (5290)
7/6/2005 9:09:20 PM
Hi Daniel,

Try something like:

    Dim WB As Workbook
    Dim WS As Worksheet

    Set WB = ActiveWorkbook
    With WB
        .Sheets("Mysheet").Copy Before:=.Sheets(1)
    End With

    Set WS = ActiveSheet

---
Regards,
Norman



"Daniel" <softwareengineer98037@yahoo.com> wrote in message 
news:ee4BIwmgFHA.1048@tk2msftngp13.phx.gbl...
> in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference 
> to
> the newly created copy of this sheet?
>
> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
> news:%23xV1ngegFHA.460@TK2MSFTNGP09.phx.gbl...
>> Hi Daniel,
>>
>> To add a copy in the same workbook, try:
>>
>> Sub Tester
>> With ActiveWorkbook
>>     .Sheets("MySheet").Copy After:=.Sheets(Sheets.Count)
>> End With
>> End Sub
>>
>> To create a new single-sheet workbook containing a copy of the sheet, 
>> try:
>>
>> Sub Tester2
>> ActiveWorkbook.Sheets("MySheet").copy
>> End sub
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Daniel" <softwareengineer98037@yahoo.com> wrote in message
>> news:uIBBqHegFHA.2156@TK2MSFTNGP14.phx.gbl...
>> > is there anyway to create a clone of an excel sheet in vba?
>> >
>> >
>>
>>
>
> 


0
normanjones (1047)
7/6/2005 9:10:10 PM
Reply:

Similar Artilces:

How do I create an average collum in my spreadsheet?
I just about have my spreadsheet ready to go. But there is one piece to the puzzle that needs to be fixed. Here's what I need. I have a spreadsheet with 7 collums in it. The fields are the following: Date User Name Number of request received Total time Total time per request I would like to know how to get a average of the number of request received times the total of each request. Thank you in advance for your assistance. ...

exporting/linking data from a row on one sheet into another sheet
I have a master sheet with all the sales information that my company tracks. I would like to create seperate sheets for each sales rep. I have all their initials listed under one column. Can I take each row and put the information onto another sheet based on what I put into the initials colum? exporting/linking data from a row on one sheet into another sheet Hi Clark see responses in Excel.worksheet.functions NG Frank Clark Haddock wrote: > I have a master sheet with all the sales information that > my company tracks. I would like to create seperate > sheets for each sa...

Is there anyway to simplify the Deployment process?
Hello, I am looking for any best practices that will aid in deplyment of my application. My application has one SQL 2008 database. When I make changes and update/upgrade the schema, I need to track the changes and prepare a script to deliver to each of my Clients. They run the script and everything is wonderful again. However, the issue I have been facing is that different Users are at different levels of patches applied. So, for instance, User 1 may be running a version from last December, while User 2 is running a version from last Tuesday. When patches to the schema/stored pr...

How to create a formula for a specific pattern
I'm trying to do the following but don't know if it is possible. I want to generate a formula such that when I do a Fill-Down it will automatically generate the following: Basically, it will group 1,2 and print it out twice and then it will increment and 3,4 and print it out twice in the column and then it will increment. Any pointers would be very much appreciated. Please send email to akb_@yahoo.com Thanks, 1 1 1 1 2 2 2 2 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4 4 3 3 3 3 4 4 4 4 5 5 5 5 6 6 6 6 5 5 5 5 6 6 6 6 -- anita1999 -------------------------------------------------------------...

How do I change the default font color in Excel
In Excel, when I open it and start working, the font color is black. I know how to change that particular workbook to have a blue text color, but how do I change it to be the default when I open Excel again? Leslie, I think you will have to change the default template that excel uses, Set up a workbook as you want it (Change the font color, you could also change things like margins, number of sheets, cell formats, headers, footers, gridlines to print, etc). Now Save As, and change the file type to "Excel Template (.xlt)" not (.xls). In the save in box, locate your XLStart fo...

MS KB: How to create and use a home mortgage escrow account in Money 2003, in Money 2004, and in Money 2005
Thought this would be useful to the readers of this newsgroup: <http://support.microsoft.com/default.aspx?scid=kb;en-us;899623> -- Himanshu ...

Create another tab just like Notes
Is there any way to create another tab just like the Notes tab, how could I see the jscript behing that tab? When I click Change Properties, it says this field belongs to a locked section and cannot have its properties modified. See this post: http://groups.google.com/group/microsoft.public.crm.developer/browse_thread/thread/3f8a7a8289bd6b8d/4a886d86dc276fa6 It covers putting notes in a custom iframe - of course, this does not allow you to have another *field* like notes, it just lets you edit the notes field in more then one place. To create another field, your best bet is to create a ...

Cell contents in Excel??
Hi all, I am developing an app with excel using VBA. I want to determine the type of content of a cell ie, is it text, or numeric, or a formula, or a hyperlink. How can I possibly do that? Any methods, events, or functions to obtain this? Please suggest. if ActiveCell.Hasformula then if ActiveCell.Hyperlinks.count > 0 then if vartype(activeCell.Value) see help on vartype for the values returned. -- Regards, Tom Ogilvy "A.K." <anonymous@discussions.com> wrote in message news:uHZvIDaaEHA.2792@TK2MSFTNGP09.phx.gbl... > Hi all, > I am developing an app with exc...

creating a template that cant be edited?
Hi people, I am having trouble putting our logo and address behind the body of the letter. It works fine if i insert them into headers and footers but the problem is the turn into water marks when i go back to page view. I dont ant them as watermarks. Any ideas?? They only look like watermarks--all text in headers/footers is faded out in page view, and there's nothing you can do about it. They will print out fine. On 10/7/04 4:07 AM, "sublimesoul" wrote: > Hi people, > > I am having trouble putting our logo and address behind the body of the > letter. &...

Create VBA function (UDF) in Excel 2003
How do I code a function CellVal(rownum, colnum [, sheetname]) that will be the equivalent of the worksheet formula: INDIRECT(ADDRESS(rownum, colnum [, sheetname])) that will return the value of the specified single cell? My knowledge of VBA is not good enough for me to find how to specify a single-cell RANGE by its co-ordinates. Try the below. Sheetname is optional.. Function CellVal(lngRow As Long, lngColumn As Long, _ Optional strSheet As String) As Variant If strSheet = vbNullString Then strSheet = Application.Caller.Worksheet.Name CellVal = Sheets(strSheet).Ce...

Create a variable
Dear all, In my VBA project I have a lot of codes... But I want that in one code create a variable to use in another code. For example: sub code_1() dim x as variant set x =3D 10 end sub sub code_2() for i =3D x .. .. .. end sub How can I do this? And what I need to do for update de variable? Thanks a lot!!! Andr=E9. One way: Option Explicit Dim x as long 'this variable is visible to just this module Public y as long 'this variable is visible to all modules sub code_1() x = 10 'no set required end sub sub code_2() dim i as long for i = x ... next i en...

Create random file
Hi, I wish create a large random file (for testing copy in powershell between servers on WAN) i'm using a get-random function but it's very slow. A file can have a size of 1-5 Gb Do you have ideas for accelerate this program? Thanks and happy new year. My tiny program function GenChaine([int]$n) # G�n�ration d'une chaine al�atoire de n caract�res { $caracteres = "a|z|e|r|t|y|u|i|o|p|q|s|d|f|g|h|j|k|l|m|w|x|c|v|b|n" $caracteres += "A|Z|E|R|T|Y|U|I|O|P|Q|S|D|F|G|H|J|K|L|M|W|X|C|V|B|N" $caracteres += "0|1|2|3|4|5|6|7|8|9&...

Unexpected error message on closing an Excel file
Suddenly I am getting the following message when I try to close a workbook: "Your formula contains an invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct, and try again" The mysterious thing is that it does not happen consistently and that, after I click OK after the above message, I can still save the file. What might be the cause of this error message and can the "invalid reference" be tracked down using one of the utility add-ins such as J. Walkenbach's PUP? If it only happens when you close ...

Creating Custom Stationary
I want to create stationary that resembles my company letterhead. How do I do this? Thanks, Scott ...

Excel & Access Reporting
I have a report in access where I am extracting some data out of excel to generate the report. The code runs with out error, but comes out blank. On the report the controls are lbl20-lbl27, & txt20-txt27. All of them are labels. In the past I haven't been able to write excel to a text box. Below is my code: ClosingCount = 20 CenterCount = 40 CellNum = 3 PercentVAR = 5# Set XLAPP = GetObject("Excel.Application") 'checks to see if an instance of excel is running If Err.Number <> 0 Then ' excel was not running Set XLAPP = Cr...

Microsoft Excel #9
I got kicked out while using Excel. Now when I go back in, I have save and print buttons, but they are not accessible. They are grayed out. Hi does this happen for all files or only a specific one? -- Regards Frank Kabel Frankfurt, Germany connief wrote: > I got kicked out while using Excel. Now when I go back in, I have > save and print buttons, but they are not accessible. They are grayed > out. Hello Frank, Thank you for replying to my predicament. We did find a solution. The problem was that the user got kicked out of her Citrix session abruptly with an Excel file stil...

create data point line in a column chart
I would like to know how to create a data point line as a point of reference for a column chart so that the data for the previous year could be displayed as a line amidst the columns. Thank you for your help. Add the previous year's data to the column chart Right click on the previous year column Choose Chart Type Select a line chart, click OK Annie wrote: > I would like to know how to create a data point line as a > point of reference for a column chart so that the data > for the previous year could be displayed as a line amidst > the columns. Thank you for your help. ...

Error in a large excel File
I have two files i use a formula and count some informaton. Since i made some more entires it show the error message in the attachement What can that be it is a lrge file about 8000 lines with 24 collums. but is this to large ? Formula =COUNTIF(DATA!$F$2:$F$8469;(B48&"x"&C48)) in the Cell DATA$F$2:$F$8469 is this formula =IF('E:\Documents and Settings\RS\My Documents\Inventory\[Timberstocklistactually test4.xls]INVENTORY'!U2=$E$1;IF('E:\Documents and Settings\RS\My Documents\Inventory\[Timberstocklistactually test4.xls]INVENTORY'!T2=0;'E:\Docume...

Please create a POS module
We are a retailer and bought GP from our partner who also sold us Compass Technologies POS product. Without the POS product, GP would not be very usable for us. However, had I understood the level of support we were going to receive and the level of dependency we now have on this little third-party, we would not have purchased a Microsoft product and will never recommend MS to any other retailer until Microsoft supports this part of the software directly. If Microsoft is serious at all about selling to the retailer segment, it is critical that this interface be supported directly by Mi...

How do I create a school directory in Publisher 2000?
I am trying to create a school directory listing all families in our school, sort of like an address book. I would like to use the mail merge feature, but can't figure out how to make the merge work. Every time I merge (whether in the preview or actual printing) it prints the first listing in the database over and over, so that the first family is the only family in the directory and none of the other families are included. The second copy contains only the family listed second in my database, and so on. Please help! You can setup your page as if they were labels. Determine how...

excel #91
i am working in excel , my name box & formula bar disappeared View/Toolbars and be sure there's a check mark in front of "Standard" On Sat, 9 Oct 2004 12:03:02 -0700, "excelca" <excelca@discussions.microsoft.com> wrote: >i am working in excel , my name box & formula bar disappeared View>Formula Bar. Gord Dibben Excel MVP On Sat, 9 Oct 2004 12:03:02 -0700, "excelca" <excelca@discussions.microsoft.com> wrote: >i am working in excel , my name box & formula bar disappeared ...

Create mailboxes
Hi, All For legal reasons.. I'm moving my users to a new exchange server / org and I need to recreate all mail boxes on this new server with the same email settings e.g email address etc. Is there a way to export and import the creation on these mailboxes on my new exchange server. Thanks -Darren Darren, Are you migrating to Exchange 2003, 2000, or 5.5? What are you moving away from? Lotus Notes? GroupWise? Steve From exhange 5.5 to Exchange 5.5 , however diffrent server / org.. "Steven Fossedal" <micronetworks@charter.net> wrote in message news:28D7BEBF-AA38-4839-...

My Recent Documents lists several Excel Charts
Hi, I like using My Recent documents to quickly pull up Excel 2007 files that I work on. Many of my Excel files have graphs in them. When I go to My Recent Documents, there may be two or three Excel files and the other 7-8 "documents" listed are things like Pchart.xlm, Vchart.xlm and scatter.xlm. How do I exclude these files from appearing in My Recent Documents. I basically just want Excel, Access and Word documents to appear. Thanks, Chuck Chuck W <ChuckW@discussions.microsoft.com> wrote: > Hi, > I like using My Recent documents to quickly pull...

Flashing sentence makes sheet flash
Thanks for the answers given to my first question! Now I've got tw other questions and I hope somebody can help me with these matters. The formula: Dim NextTime As Date Sub Flash() NextTime = Now + TimeValue("00:00:01") With ActiveWorkbook.Styles("Flash").Font If .ColorIndex = 2 Then .ColorIndex = 3 Else .ColorIndex = 2 End With Application.OnTime NextTime, "Flash" End Sub to let a sentence flash works, but the problem is that the entire shee and other sheets flash a bit as well (not like the sentence but the move as well) I've chosen the format flas...

opening an excel file #2
I recently loaded office xp Pro in my computer, the problem that I am having is when I click on an excel icon where the file is saved, it does open excel, but not the file that I clicked on. I get a grey screen with toolbars on top. I then have to go to file, open, then go to where the file was saved. I have never had to do this before. When I clicked on an excel file that file opened. Can someone help me with this? Hi try the following: goto 'Tools-Options-General " and uncheck "Ignore other Applications" Exit Excel and try again If this doesn't work try to re-...