Inserting worksheet

I would like to insert a worksheet template into a workbook by simpl
inserting a specified number into a cell.  For example

1 = DA.xlt
2 = CPC.xlt

It would put these files into my workbook by inserting 1 or 2
Any help is appreciated

--
Message posted from http://www.ExcelForum.com

0
7/16/2004 3:53:32 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
564 Views

Similar Articles

[PageSpeed] 28

Hi,

Here's some code that should do it for you - you may
need to adjust the DIR line.

good luck!
jeff

Sub CopySheets(booknum As Integer)
'MsgBox "made it"
'Exit Sub
    Dim basebook As Workbook
    Dim mybook As Workbook
    Dim sourceRange As Range
    Dim destrange As Range
    Dim FNames As String
    Dim MyPath As String
    Dim SaveDriveDir As String
 
    SaveDriveDir = CurDir
    MyPath = SaveDriveDir   ' or set your own  "C:\Data"
    ChDrive MyPath
    ChDir MyPath
    If booknum = 1 Then
        FNames = "DA.xlt"
    Else
        FNames = "CPC.xlt"
    End If
    'FNames = Dir("*.xls")
    'If Len(FNames) = 0 Then
    '    MsgBox "No files in the Directory"
    '    ChDrive SaveDriveDir
    '    ChDir SaveDriveDir
    '    Exit Sub
    'End If
 
    Application.ScreenUpdating = False
    Set basebook = ThisWorkbook
    'Do While FNames <> ""
    If FNames <> ThisWorkbook.Name Then
        Set mybook = Workbooks.Open(FNames)
        mybook.Worksheets(1).Copy after:= _
            basebook.Sheets(basebook.Sheets.Count)
        On Error Resume Next
        ActiveSheet.Name = mybook.Name
        On Error GoTo 0

        ' You can use this if you want to copy only the 
values
        '        With ActiveSheet.UsedRange
        '            .Value = .Value
        '        End With

        mybook.Close False
    End If
    'FNames = Dir()
    'Loop
    ChDrive SaveDriveDir
    ChDir SaveDriveDir
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("c1:c1")) Is Nothing Then 
Exit Sub
If Target.Value = 1 Or Target.Value = 2 Then
    CopySheets (Val(Target.Value))
End If
End Sub

>-----Original Message-----
>I would like to insert a worksheet template into a 
workbook by simply
>inserting a specified number into a cell.  For example
>
>1 = DA.xlt
>2 = CPC.xlt
>
>It would put these files into my workbook by inserting 1 
or 2
>Any help is appreciated.
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
anonymous (74719)
7/16/2004 5:51:46 PM
Thanks I will try i

--
Message posted from http://www.ExcelForum.com

0
7/16/2004 7:53:05 PM
Reply:

Similar Artilces:

How do I insert Roman Numerals?
New to Word 2007 would someone please explain to me how to insert Roman Numerlas in my document? "ckj" <ckj@discussions.microsoft.com> wrote in message news:6CF0C580-BA6E-463D-B4FD-E6A6FDD9E814@microsoft.com... > New to Word 2007 would someone please explain to me how to insert Roman > Numerlas in my document? Just type them... I II III IV V VI VII VIII IX X XI etc etc. Type a number e.g. 21, select it and run the following macro Dim oRng As Range Set oRng = Selection.Range oRng.Fields.Add oRng, 34, oRng.Text & " \*Roman"...

Insert trigger
Looking for some advice on SQL 2005. I have a table that will usually be populated by an SSIS package. I want to set the "loaddate" column to the current time after a record is inserted. Should i do this via trigger or should i just build a step in the SSIS package to update the column after the file loads? If trigger is the way to go, what is the syntax to create the after insert trigger? Thanks in advance. You can create a default constraint on the table set to CURRENT_TIMESTAMP. That will handle the automatic date assignment without any need for coding. -...

empty rows at bottom of worksheet
My worksheet has about 66,000 empty rows at the bottom of the sheet and that affects my usage of the scrollbar on the right of the screen. Anyone know how i can get rid of those miscellaneous rows? Michael If you will turn on the page break preview (piece of paper with a magnifying glass) which is the icon on the on the Standard Toolbar to the right of the Printer icon it will show you exactly what is going to print. There are solid blue lines that the edge of the page, the dashed blue lines are soft page breaks. Find the solid blue line at the bottom of the spreadsheet and drag it ...

inserting hrs and minutes
I have a cell in my time card that displays total weekly time -ex- "40:15" is there a way to make it more like this...40hrs,15mins -- Message posted from http://www.ExcelForum.com Use a custom format hh"hrs",mm"mins" -- Regards, Peo Sjoblom "-Brian-H- >" <<Brian-H-.110wgs@excelforum-nospam.com> wrote in message news:Brian-H-.110wgs@excelforum-nospam.com... > I have a cell in my time card that displays total weekly time -ex- > "40:15" is there a way to make it more like this...40hrs,15mins ? > > > ...

insert an interactive excel file into word web page
I'm trying to insert a excel file into a word document with text, and then save it was as a web page, but I want to keep the excel part interactive. Any ideas? ...

Insert | File > Attachmnet-Button Drop Down ;What is the difference between Insert and Insert As Attachmnet
re: "Outlook2003, File-Insert-Options" On making new-email with Attachment-File(s), ** File Menu | Insert | File >>> (Brows and select File to insert ) then we can see the button "Insert", and write side Drop Down Arrow lower-right side of Dialog Box; If it clicked, we can see three options as follows: ** Insert Insert as text Insert as Attachment I can not recognize/understand the difference between "Insert" and "Insert as Attachment" *** What is the difference between Insert and Insert As Attachment ? I would appreciate y...

Worksheet name in cell formula
I asked this question a while ago and got a prompt answer which I thought was what I wanted but alas its not... I want to be able to change my worksheet names.....ie: from Sheet 1, Sheet 2, etc etc.....to something more meaningful.....eg Sales, Expenses....etc etc.... and have these changes reflect on the worksheet. For example, I might want Sheet 1 Cell A1 to say.....This is the Sales worksheet (assuming I have renamed it to Sales). The answer I was given some time back worked.....but it changed the formula on every worksheet to reflect the name of the last changed sheet. By this I mean.....

How do I put worksheets in alphabetical order
I have created a database which has been worked on by someone else. I have made the necessary alterations but how do I check for duplicate entries on each worksheet. Thansk Take a look at http://cpearson.com/excel/duplicat.htm In article <F87E0D91-E192-42EC-A8BA-E1FEC70CCB6E@microsoft.com>, "DLee" <DLee@discussions.microsoft.com> wrote: > I have created a database which has been worked on by someone else. > > I have made the necessary alterations but how do I check for duplicate > entries on each worksheet. > > Thansk First you need two ...

Inserting Hyperlinks in a Protected Sheet
Hi I run Excel 2000 and I have a protected worksheet that I share wit users in my organisation. I want to allow the insertion of a hyperlin to a specific file type within a specified directory on our server. 3 Questions: 1.Protection on disables the insert hyperlink command. Can this b overcome with worksheet activate code? 2.Can I limit the types of files (preferably by requiring the file t meet a mask format eg "z-*.xls")? 3.Can I limit the directory that can be linked, by referring to pathname stored in a cell on the active sheet? Would appreciate your suggestions. Thanks S...

Insert with a where condition
Hi, sql 2005 I have an insert statement that is ignoring the where condition. That is, I want to insert records when they do not already exist in the destination table. INSERT INTO dbo.tblmnuGroupPerm ( gId ,mtfID ,... ) SELECT @gID ,mtfID ,... FROM dbo.locmnuTabFunction AS ltf WHERE ltf.mtfID NOT IN ( SELECT gp.mtfID FROM dbo.tblmnuGroupPerm AS gp WHERE gp.gId=@gID AND gp.Deleted=0 ) Any ideas or recommendations appreciated :-) Many thanks, Jonathan It's OK... <oops "redFace">I did not correctly se...

Extracting Data To New Worksheet
Hi all I am attempting to filter a large data source to a new worksheet without much success. I want to filter the data by multiple paramenters. I have built a Macro that sorts the data by value and then pastes in the new sheet. I only want to paste 3 of the columns, not all the data. E.G Location, Value 1, Value 3 and Manufacturer Is this as complicated as I am making it? Location Model Value 1 Value 2 Value 3 Value 4 Value 5 Value 6 Value 7 Manufacturer text/numerical text Numerical Numerical Numerical Numerical Numerical Numerical text/numerical Text te...

Insert
I want to overtype in a Publisher text box. I find I can only insert. The "insert" key doesn't do anything. ...

How do I delete the header in my worksheet?
I used the blank for header instead of footer and now cannot delete the header portion on my worksheet. What do I do next? File > Page setup > Header/Footer > Custom header... Thenb clear right, center, and left. -- Gary's Student gsnu200711 The information I received was correct, I just did something wrong myself! I think I forgot to save before getting out because my instructer did the same thing and it worked, of course. Thanks again. "quiltingstudent" wrote: > I used the blank for header instead of footer and now cannot delete the > header port...

how to insert data in a table
Hi Exprets; I am creating an access database in which I want to insert data in already created table. Kindly help. Regards, Vikky Vikky <love.excel@gmail.com> wrote in news:1194124711.012302.269990 @e34g2000pro.googlegroups.com: > Hi Exprets; > > I am creating an access database in which I want to insert data in > already created table. > > Kindly help. > > Regards, > > Vikky > Data from where? Do you want to import it from excel, from a text file, copy it from another table or type it in manually? -- Bob Quintal PA is y I've altere...

How do you insert page numbers larger than 1000?
I have my purchase orders set up as a Publisher document. When our organization upgraded from Publisher 2000 to Publisher 2002, the new version set parameters on the page numbers. This was one of those things that worked just fine in the previous version... Does anyone know how to turn it off or change it? Hi mregen (mregen@discussions.microsoft.com), in the newsgroups you posted: || I have my purchase orders set up as a Publisher document. When our || organization upgraded from Publisher 2000 to Publisher 2002, the new || version set parameters on the page numbers. This was one of those...

insert downloads into power point
i downloaded an application called "BioDigital Simulator" of an animated cleft lip/palate surgery and need it put into my power point presentation, but can't fiugre out how to do so.... very frustrating... What kind of file is this application? Is it a video? If so, what kind? MPEG? AVI? MOV? Or is it an EXE file? Something else? Which version of PPT are you using? -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover...

Insert a blank row
Hi, I need some help to insert a blank row in a range where column A has a series of dates. There will be several of the same dates and I need to both sort the dates and then insert the blank row at the end of each sequence. In the blank row I need to total figures that will be in columns B through to G. Thanks, Jim S Hi Jim maybe an easier solution 1. Sort your data (use 'Data - Sort', e.g., column A ascending) 2. Use 'Data - Subtotals' This will insert a row after each date and calculate subtotals automatically for you HTH Frank anon wrote: > Hi, > I need some...

Combining two worksheets with same case identifiers
Hello, I am trying to merge two workbooks containing data from an experiment. The first three columns have identical data in both workbooks, and include a participant number, value for Variable1,and value for Variable2. Each participant takes up about 12 rows of the worksheet, this includes all possible combinations of Variable1 and Variable2. The last three columns represent judge ratings. Half of these ratings are entered in Worksheet I, and half of them in Worksheet II. Some rows in Worksheet I contain missing data which is contained in Worksheet II. How do I go about combining these...

Insert dataset to another database
I'm posting this to this group also since it pertains to queries, primarily. Hello, Using VB6/ADO, I'm thinking I can create a recordset and insert it into another table in a different Jet database, all within the execution of a single query. But, I can't seem to get it to work, even in an experiment in Access 2K. My sql in Access 2K: SELECT D.lorder as Ord, A.Lorder FROM [;Database = C:\MyDocuments\Acc2K\Wrk.mdb].OrdersData as A INNER JOIN [;Database = C:\Access\Work\Sales06.mdb].Detl1 as D On D.Lorder = a.lorder WHERE ((D.fg)= 'MXX-NC' Or (D.fg)= 'MXX.NC')...

How do I insert a letter into an existing word document?
I am working on a large document that I need to add several letters that are on our letterhead. If I cut and paste the letter, the letterhead header becomes skewed. This is just one issue I'm having with the document! Also need to know how to merge 3 separate documents together once I insert the letters that I need! Help! What do you mean by "skewed"? To preserve the data from the header of the document, you will need to insert it into a Section of its own separated from the rest of the document by Next page Section breaks before and after it with the headers...

How do I insert backround into my picture inserts?
Could you please elaborate on your question, and give us details such as your version of Publisher. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rights. "TAMOS" <TAMOS@discussions.microsoft.com> wrote in message news:A1B3ABEC-F208-4A23-B695-9C0EEFBF649F@microsoft.com... > How to Compose a Good Newsgroup Post http://dts-l.org/goodpost.htm How to Act Smart on Usenet http://www.catb.org/~esr/faqs/smart-questions.html Getting Your Post Noticed - and Answered http:...

one cell in my worksheet appears blank, but answr shows in formula
I have Excel 2003 I have a worksheet that is linked to another worksheet. The sheet is a YTD of all the data collected for several units. It's a simple formula where I typed the equal sign in , then clicked on the cell in the other worksheet so it shows in this worksheet. One cell out of many appears to be blank, yet when I click on it the actual answer shows up in the formula bar. I've checked all the formatting and all appears to be well, the font color is black. I tried re-doing the formula with the same result. I deleted the formula from that cell and typed the ans...

Inserting images into excel conditionally
I have a large product price list (1200 items) with one column for quantity. I would like excel to import the appropriate image for a product once the condition entered is that quantity is > 0. Is this possible? If so then please help. Thanks Pav One way http://www.mcgimpsey.com/excel/lookuppics.html can be adapted to your situation -- Regards, Peo Sjoblom "Pav" <Pav@discussions.microsoft.com> wrote in message news:37DC7373-14E6-4F11-9136-71DC3AD93CC2@microsoft.com... > I have a large product price list (1200 items) with one column for quantity. > I would l...

How do I insert a reference to lookup and insert a name
How do I insert a reference code in one column which will then look up a reference table and then insert the name in the next column. eg if I type the letter b in column 1 then the word 'bank' will appear in column 2. If I type the letter i in column 1 then the word 'insurance' will appear in column 2 etc etc look at VLOOKUP in Help. -- HTH Bob Phillips "Jan K" <Jan K@discussions.microsoft.com> wrote in message news:C6CDAC92-DB48-49A7-803F-BF494F5221AB@microsoft.com... > How do I insert a reference code in one column which will then look up a > r...

Triggers after Insert
Hi, I'm having a temporary table in SQL Server 2005, which receives the data from external Orace DB. The data from Oracle DB inserts the data once in a month time. How to create a trigger which calls the storedprocedures, after all the data is get inserted to the temporary table, not for each row insertion. On Sat, 12 Jun 2010 04:01:21 GMT, "Visha" <u60840@uwe> wrote: >Hi, > I'm having a temporary table in SQL Server 2005, which receives the data >from external Orace DB. The data from Oracle DB inserts the data once in a >month time. >...