Create workbook

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
"SS")
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
         Templatesh.Copy
         Set newbk = ActiveWorkbook
         Set NewSht = ActiveSheet
         NewSht.Name = ShtName

         OldbkName = BkName
      Else
         Templatesh.Copy _
            after:=newbk.Sheets(newbk.Sheets.Count)
         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"
         newbk.Close
      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.


Macro*******************

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"

Else
Templatesh.Copy after:=bk.Worksheets(bk.Worksheets.Count)
ActiveSheet.Name = cell.Offset(0, -1)
End If
Next
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 
wrong.

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 
stumped.

Thanks in advance for your suggestions.

Dave
-- 
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
Next
End With
ActiveWorkbook.SaveAs Filename:="NewFile.xls", _
  FileFormat:=xlNormal
End Sub


any hints.

Thanks

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

7. link to not-yet-created workbook

8. Create workbook from a template