Results 1 to 8 of 8

Thread: Excel macros VBA (creating new workbook from existing one)

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2015
    Posts
    1

    Question Excel macros VBA (creating new workbook from existing one)

    Hi All,

    I'm trying to write a code for my excel tool to generate a new workbook from the existing one and I've encountered an error. The existing workbook has 4 sheets. I want the to write a code so that it copies 3 of these sheets to a new workbook (as they are). Additionally it should create 4th sheet there and should summarize the table that is in the remaining sheet of the existing workbook. I would appreciate you support.

    Thanks!
    Jumshud

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,612

    Re: Excel macros VBA (creating new workbook from existing one)

    Moved to office development

    Realistically you're going to need to provide more information before you can get a meaningful answer. What code do you have so far? What error are you encountering?
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,965

    Re: Excel macros VBA (creating new workbook from existing one)

    you can try like
    Code:
    thisworkbook.savecopyas thisworkbook.path & "\somenewfilename.xls"
    set newbk = workbook.open thisworkbook.path & "\somenewfilename.xls"
    set newsht = newbk.sheets.add "Summary sheet"
    'here you can work with the ranges of newsht to produce your summary
    
    'also delete the extra sheet you do not want
    application.displayalerts = false
    newbk.sheets("extra sheet").delete
    application.displayalerts = true
    newbk.close true  ' close and save, or just save
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4
    New Member
    Join Date
    Dec 2015
    Posts
    3

    Re: Excel macros VBA (creating new workbook from existing one)

    The task i am trying to do is
    I have two worksheets one named Invoice and the other sales
    In invoice i create the invoice and the sales sheet gets update on the update button so that all the invoices summary is copied in the sales sheet
    Now i have another button knowas save invoice - what this should do is

    take the customer name which is in the sales sheet in column 1 second row (as the first row is heading) and save the file in a location path mentioned in the code below. so as soon as i create a single invoice for a single customer and hit save it should save the full workbook or only the invoice worksheet as a new workbook with the file name as customer + date of invoice. if custname is ABC Technology then the file name saved should be "ABC technology 19 Dec 2015.xlsx"

    Private Sub CommandButton1_Click()
    Dim path As String
    Dim Filename1 As String

    path = "d:\invoicedata\"

    Worksheets("Sales").Select
    Filename1 = Sales.Range("A1")
    ActiveWorkbook.SaveAs Filename:=path & ".xls", FileFormat:=xlNormal
    Worksheets("Invoice").Select
    Worksheets("Invoice").Range("D13").Select
    End Sub

    kindly let me know if the above is possible.

    Kind regards and many many thanks,

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,965

    Re: Excel macros VBA (creating new workbook from existing one)

    try like
    Code:
    ActiveWorkbook.SaveAs Filename:=path & sheets("sales").range("a2").value & " " & format(date, "dd mmm yyyy") & ".xls", FileFormat:=xlNormal
    saveAs will make the new workbook the activeworkbook and close the original
    probably better to use saveCopyAs

    or to just save the one worksheet
    Code:
    Sheets("invoice").Copy
    Set b = Workbooks(Workbooks.Count)    ' find the newly added workbook
    cust = sheets("sales").Range("a2").Value     ' if you are adding invoices to the sales sheet, you may need to determine the last row, rather than just using row 2
    b.SaveAs Path & cust & " " & Format(Date, "dd mmm yyyy") & ".xls", FileFormat:=xlNormal
    b.Close
    you should avoid selecting or activating any sheets or ranges, just use fully qualified objects

    if you are going to save every invoice as workbook, you could generate many workbooks, maybe better to save them as .pdfs
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6
    New Member
    Join Date
    Dec 2015
    Posts
    3

    Re: Excel macros VBA (creating new workbook from existing one)

    Many thanks for your efforts,
    I am trying the below code it saving the file as the name of the client but still it says runs into error and return an error of run-time 1004 and the sheet becomes a false.xls.
    As you rightly said, My endevour is to save certains cells of the invoice worksheet as a .xls only or .pdf only. So the file that should get saved will have no graphics no commandbuttons but only the data of the relevant cells and save the file with the customer name.
    ______________________________________________________________________________________________
    Private Sub CommandButton1_Click()

    Dim Filename1 As String
    Dim mydate As String
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Invoice")
    Set ws2 = Worksheets("Sales")
    erow = ws1.Cells(Rows.Count, 1).End(xlUp).Row

    Dim path As String
    ws2.Range("G2") = Date
    mydate = ws2.Range("G2")
    mydate = Format(mydate, "mm_dd_yyyy")

    path = "d:\invoicedata\"

    Worksheets("Sales").Select
    Worksheets("Sales").Range("A1").Select
    'If Worksheets("Sales").Range("A1").Offset(1, 0) <> "" Then
    'Worksheets("Sales").Range("A1").End(xlDown).Select

    ActiveWorkbook.SaveAs Filename:=path & Sheets("sales").Range("a2").Value & " " & Format(Date, "dd mmm yyyy") & ".xls", FileFormat:=xlNormal


    Application.DisplayAlerts = False
    ActiveWorkbook.ActiveSheet.SaveAs Filename = path & Range("A2") & "." & mydate & ".xlsx"

    ActiveWorkbook.ActiveSheet.SaveAs Filename:=path & Range(“A2”) & “ - ” & Range(“G2”) & “ - ” & mydate & “.xlsx”, FileFormat:=51
    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path & Range(“A2”) & “ - ” & Range(“G2”) & “ - ” & mydate & “.pdf”, OpenAfterPublish:=False

    'End If
    Application.DisplayAlerts = True
    ActiveWorkbook.ActiveSheet.Shapes("CommandButton1").Delete


    Application.Quit
    ActiveWorkbook.CloseSaveChanges = True



    'Worksheets("Sales").Select
    'Filename1 = Sales.Range("A1")
    'ActiveWorkbook.SaveAs Filename:=path & ".xls", FileFormat:=xlNormal
    Worksheets("Invoice").Select
    Worksheets("Invoice").Range("D13").Select
    End Sub

    _______________________________________________________________________________________________



    Quote Originally Posted by westconn1 View Post
    try like
    Code:
    ActiveWorkbook.SaveAs Filename:=path & sheets("sales").range("a2").value & " " & format(date, "dd mmm yyyy") & ".xls", FileFormat:=xlNormal
    saveAs will make the new workbook the activeworkbook and close the original
    probably better to use saveCopyAs

    or to just save the one worksheet
    Code:
    Sheets("invoice").Copy
    Set b = Workbooks(Workbooks.Count)    ' find the newly added workbook
    cust = sheets("sales").Range("a2").Value     ' if you are adding invoices to the sales sheet, you may need to determine the last row, rather than just using row 2
    b.SaveAs Path & cust & " " & Format(Date, "dd mmm yyyy") & ".xls", FileFormat:=xlNormal
    b.Close
    you should avoid selecting or activating any sheets or ranges, just use fully qualified objects

    if you are going to save every invoice as workbook, you could generate many workbooks, maybe better to save them as .pdfs

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,965

    Re: Excel macros VBA (creating new workbook from existing one)

    ActiveWorkbook.ActiveSheet.SaveAs Filename = path & Range("A2") & "." & mydate & ".xlsx"
    this still saves the entire workbook and makes it the active one

    to detect error try
    Code:
    myfile =path & Sheets("sales").Range("a2").Value & " " & Format(Date, "dd mmm yyyy") & ".xls"
    msgbox myfile
    ActiveWorkbook.SaveAs Filename:=myfile, FileFormat:=xlNormal
    does the messagebox show a valid path\filename?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8
    New Member
    Join Date
    Dec 2015
    Posts
    3

    Re: Excel macros VBA (creating new workbook from existing one)

    Request if you could please correct in the code i had submitted earlier so that i can reproduce the same at our office.

    Also can we hard code also that the printout which goes to default printer is set in the code so that we dont need to select the printer that is irrespective of the whichever the default printer is set on client machine it should go to the printer which is shared on the network and is already installed in all client Pc's. Inmy case the printer is installed on a computer o usb port and is being shared with the clients.

    Thanks and regards



    Quote Originally Posted by westconn1 View Post
    you can try like
    Code:
    thisworkbook.savecopyas thisworkbook.path & "\somenewfilename.xls"
    set newbk = workbook.open thisworkbook.path & "\somenewfilename.xls"
    set newsht = newbk.sheets.add "Summary sheet"
    'here you can work with the ranges of newsht to produce your summary
    
    'also delete the extra sheet you do not want
    application.displayalerts = false
    newbk.sheets("extra sheet").delete
    application.displayalerts = true
    newbk.close true  ' close and save, or just save

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width