Create workbook



  • 1. X-Bar in Statistical Process Control
    Is the mean/ average. Is there a way to enter a Capital X which a "underscore" over it to get a "X-Bar"?? TIA - Bob
  • 2. How do I customise my sheet background????
    Hi, I just wantet to know if there is anyway of customising the worksheet background i.e. Changing the size of the background picture or changing the way it looks for example centered, not tiled or streched....or rotate it the way you want.... Any idea Thanks
  • 3. Excel can't find files
    I've been running Office 2003 professional on Vista for about a month. All of a sudden a couple days ago, Excel decided that it could not find and open my files when I double clicked on their icons. It would give a warning to that affect, and then open an empty excel window. If I then used the file/open method to open the same file it worked OK. But that is too cumbersome, having to use that method for every file. Plus, it just happened out of the blue, because it was working right for weeks before this. I recovered Windows to a point in time before this started happening, and also uninstalled office and re-installed office. This has not fixed anything. Does anybody know what is going on here?
  • 4. Excel 2002 and 2007 on same computer
    I just got Excel 2007 on a new Vista machine. Some good things, some not-so-good things. Can I install Excel 2002 on this machine, so I can work faster until I learn 2007? Thanks in advance, -- Alan

Create workbook

Postby SmVyZU5ldA » Sat, 22 Mar 2008 10:29:00 GMT

My school is doing a Call-a-thon to raise some money.  I have been asked to 
create a workbook to keep track of the donations as well as find out the 
largest donation, most donations (per individual), the most raised (per 
individual), and the most raised per capita for teams.  They have asked for 
me to be able to edit the names and amounts on a laptop screen while showing 
just the current winners (with team members) and stats on a projector.  I was 
wondering if anybody could create a workbook or give me some advice.  Usually 
I am pretty good at this kind of thing, but this is just stumping me.

Re: Create workbook

Postby Cimjet » Sat, 22 Mar 2008 11:20:42 GMT

Hi JereNet
Have a look at this site, you should be able to find something you can use 
if you need help with fonctions or Formulas, Post back.

Similar Threads:

1.Create Workbooks and then create Worksheets within them

 A      B      C .col
CC   22    JIM
SS   33    JIM
VV   34    TIM
AA   45    TIM
XX   77    KIM
MM   88    KIM

I have above data in sheet and I need macro which should do things
(see below)
1 - Looking at column C values in above data, macro should create
workbooks according to the numbers of one dublicate value (for above
data it will be 3 workbooks) and name them "JIM" , "TIM" and "KIM".
2 - Then looking at column A values in above data macro should create
2 worksheets in workbook "JIM" and name them "CC" and "SS" as in above
data in column C where the value is "JIM" in same row of column A it
has the value "CC" and "SS".
3 - In cell A1 of sheets "CC" and "SS" put the value which appear in
column B. (which will be 22 for worksheet "CC" and 33 for worksheet
4 - Repeat no. 2 and 3  for workbook "TIM" and "KIM".

In other words macro should produce below result
1 - Workbook("JIM") with Worksheets("CC") & Worksheets("SS") and in
Worksheets("CC").Range("A1").Value = 22 and Worksheets("SS").Range
("A1").value = 33
2 - Workbook("TIM") with Worksheets("VV") & Worksheets("AA") and in
Worksheets("VV").Range("A1").Value = 34 and Worksheets("AA").Range
("A1").value = 45
3 - Workbook("KIM") with Worksheets("XX") & Worksheets("MM") and in
Worksheets("XX").Range("A1").Value = 77 and Worksheets("MM").Range
("A1").value = 88

I'll be very greateful if any friend can help.

2.Create Workbooks and Copy Template Worksheet to those Workbook

try these changes

Sub NewList()
Dim StartRow As Long
Dim LastRow As Long
Dim Templatesh As Worksheet
Dim rng As Range, cell As Range
Dim bk As Workbook
StartRow = 2
Set Templatesh = ThisWorkbook.Worksheets("Template")

With ThisWorkbook.Worksheets("Sheet1")
   MstBkName = ThisWorkbook.Name
   LastRow = .Range("B" & 2).End(xlDown).Row

   OldbkName = ""
   For RowCount = StartRow To LastRow
      BkName = .Range("D" & RowCount).Value
      If BkName <> OldbkName Then
         ShtName = .Range("A" & RowCount).Value
         'copy without after or before creates new workbook
         Set newbk = ActiveWorkbook
         Set NewSht = ActiveSheet
         NewSht.Name = ShtName

         OldbkName = BkName
         Templatesh.Copy _
         Set NewSht = ActiveSheet
         NewSht.Name = ShtName
      End If
      Data_1 = Evaluate("SUMPRODUCT(" & _
         "--(" & MstBkName & "!A" & RowCount & "=" & MstBkName & "!A$1:A$" & 
LastRow & ")," & _
         "--(" & MstBkName & "!D" & RowCount & "=" & MstBkName & "!D$1:D$" & 
LastRow & ")," & _
         MstBkName & "!B$1:B$" & LastRow & ")")

      NewSht.Range("A1") = Data_1
      Data_2 = Evaluate("SUMPRODUCT(" & _
         "--(" & MstBkName & "!A" & RowCount & "=" & MstBkName & "!A$1:A$" & 
LastRow & ")," & _
         "--(" & MstBkName & "!D" & RowCount & "=" & MstBkName & "!D$1:D$" & 
LastRow & ")," & _
         MstBkName & "!C$1:C$" & LastRow & ")")
      NewSht.Range("B1") = Data_2
      If OldbkName <> cell.Offset(1, 0) Then
         newbk.SaveAs "C:\My Document\Record\" & BkName & ".xlsx"
      End If
   Next RowCount

End With

End Sub

"K" wrote:

>          A                       B
> C                       D€€ol
> Worksheets     Data_1     Data_2       Workbookshedings
>>         CC                   100           200                    JIM
>>         CC                   100           200                    JIM
>>         XX                   100           200                    JIM
>>         XX                   100           200                    JIM
>>         VV                  100           200                    KIM
>>         VV                  100           200                    KIM
>>         AA                  100           200                    KIM
>>         AA                  100           200                    KIM
>>         RR                  100           200                    SAM
>> Thanks for replying joel, you been always very helpful.  I want your
>> help little more.  Lets say if I have above data and required same
>> thing  which I mensioned in my above question but little different.
>> What changes can be done in your macro so I can achive result (see
>> below) according to above data.
>> 1 - 3 Workbooks with name JIM , KIM & SAM (according to column D
>> uniqre value)
>> 2 - Workbook JIM should have 2 worksheets with name CC & XX (according
>> to column A uniqre value)
>> 2a - In cell A1 of Worksheet CC macro should put 200 and in cell B1
>> fig should be 400. (these are the total amount from column B & C which
>> appearing in next cell of sheet name)
>> 2b - Same thing for Worksheet XX
>> 3 - Prosses 2 , 2a & 2b should be repated on Workbook KIM & SAM
>> I'll be very greatful if you can solve this for me.

3.Create Workbooks and Copy Template Worksheet to those Workbooks

         A                       B..col
Worksheets     WorkbooksHeadings
       CC                    JIM
       DD                    JIM
       XX                    KIM
       EE                    KIM
       ZZ                    TIM
       AA                    TIM

I have two worksheets in workbook.  One name is "Sheet1" and the other
name is "Template".  In "Sheet1" I have above list of data.  I need to
create a unique workbook for every unique name in column B, while
copying  the worsheet "Template" to new workbook and giving the
worksheets names which are in column A assigned to that workbook in
column B.   I have research the group and found the macro below and
done few changes in it but its not working.


Sub NewList()
Dim startrow As Long
Dim Templatesh As Worksheet
Dim rng As Range, cell As Range
Dim bk As Workbook
startrow = 2
Set Templatesh = Worksheets("Template")

With Worksheets("Sheet1")
Set rng = .Range(.Cells(startrow, 2), .Cells(startrow, 2).End(xlDown))
End With

For Each cell In rng
If cell.Value <> cell.Offset(-1, 0) Then
If Not bk Is Nothing Then bk.Close Savechanges:=True
Set bk = Workbooks.Add
Templatesh.Copy after:=bk.Worksheets(bk.Worksheets.Count)
ActiveSheet.Name = cell.Offset(0, -1)

bk.SaveAs "C:\My Document\Record\" & cell.Value & ".xlsx"

Templatesh.Copy after:=bk.Worksheets(bk.Worksheets.Count)
ActiveSheet.Name = cell.Offset(0, -1)
End If
If Not bk Is Nothing Then bk.Close Savechanges:=True
End Sub


I am getting error on line "Templatesh.Copy after:=bk.Worksheets
(bk.Worksheets.Count)".  Please can any friend can help

4.Can't create workbook object.

I keep getting an ActiveX component can't create object error message (#429) 
while trying to create a workbook instance. Google research leads me to 
suspect some misconfiguration on my machine but I can't imagine what that 
would be, since I'm having no other problems. The debugger stops on the SET 
oBook line of code. 

CODE snippet:
Private Sub Form_Open(Cancel As Integer)
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Excel.Workbook
     Set oExcel = New Excel.Application
     Set oBook = CreateObject("Excel.Workbook")
    oBook = oExcel.Workbooks.Add
    With oExcel
    End With
    MsgBox oExcel.Name
    Set oExcel = Nothing

End Sub

I have Microsoft Excel 11.0 Object Library as a reference, pushed up to the 
highest priority it will go (3rd, behind VBA and Access 11.0)  

I've looked at any number of KBs and none identify anything that I'm doing 

I can run XL, I can open an instance of it from Access using a SHELL script, 
the registry settings seem OK.  XL doesn't have a dll to register.  I'm 

Thanks in advance for your suggestions.

Providing Small Business Server and SharePoint solutions to Rhode Island and 
the South Coast of Massachusetts.

5.How to create workbook with multiple sheets

I need to copy a certain sheet in my workbook a number of times to a
new workbook, I can do this with this code to a point i get errors,
sort of new to excel macros. not sure how to place the saveworkbook
command (without it i can copy multiple sheets(according to list) in
the same workbook, but i need to create a new workbook with the
multiple worksheet copies.

Private Sub CommandButton1_Click()
With Worksheets("START")
For Each cell In .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
  If Not IsEmpty(cell) Then
    Worksheets("template").Copy After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = cell.Value
  End If
End With
ActiveWorkbook.SaveAs Filename:="NewFile.xls", _
End Sub

any hints.


6. using a cell value to name a newly created workbook

7. link to not-yet-created workbook

8. Create workbook from a template

Return to MS EXCEL


Who is online

Users browsing this forum: No registered users and 55 guest