+ Reply to Thread
Results 1 to 6 of 6

Save wb to Desktop?

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-26-2014
    Location
    Bosnia&Herzegovina
    MS-Off Ver
    Office2013
    Posts
    323

    Save wb to Desktop?

    Hello, kind people!
    i have a problem with vb script where i want to save active workbook to desktop.
    List1(CRO) is Sheet1
    List2(CRO) is Sheet2

    Run-Time error 13 Type mismatch at line: imeKopirajListe = Array(wsList1.Name, wsList2.Name)

    Sub SpremiNaDesktop()
        Dim datoteka As String, datum As String, imeKopirajListe() As String
        Dim novaKoija As Workbook, original As Workbook
        Dim ws As Workshet
        Dim wsList1 As Worksheet, wsList2 As Worksheet
        Dim wb As Workbook
        Set wb = ActiveWorkbook
        Set wsList1 = wb.Workshets("Lst1")
        Set wsList2 = wb.Worksheets("List2")
        
        'Postavi nazive listova koje ?eli? kopirati
        imeKoprajListe = Array(wsList1.Name, wsList2.Name)
        
        'Postavi naziv datoteke i datum
        datoteka = Environ("USERPROFILE") & "\Desktop\" & Format(Date, "dd-mm-yy") & ".xlsx"
        datum = Format(Date, "dd-mm-yy")
        
        'Stvori novu radnu knjigu i kopiraj listove
        Set novaKopja = Workbooks.Add
        For Each sheetName In imeKoprajListe
            Set ws = wb.Sheets(sheetName)
            ws.Copy After:=novaKopja.Sheets(novaKopija.Sheets.Count)
        Next sheetName
        
        'Kopiraj vrijednosti i oblikovanja, ali bez formula
        For Each ws In novaKopija.Worksheets
            ws.Cells.Copy
            ws.Cells.PasteSpecial xlPasteValuesAndNumberFormats
            ws.Cells.PasteSpecial xlPasteFormats
            Application.CutCopyMode = False
        Next ws
        
        Set wsList1 = Nothing
        Set wsList2 = Nothing
        Set ws = Nothing
        Set wb = Nothing
        
        'Spremi novu radnu knjigu i zatvori
        novaKopja.SaveAs fileName:=datoteka, FileFormat:=xlOpenXMLWorkbook
        novaKopja.Close
        
        'Obavijesti korisnika da je radna knjiga spremljena
        MsgBox "Nova radna knjiga spremljena na Desktopu: " & datoteka, vbInformation, "Kopiranje listova"
    End Sub
    I assume that there will or is problwem when there are more excel workbooks is opened with same sheet name.
    Thanks in advance for your precious time!
    Last edited by B.W.B.; 02-28-2023 at 03:20 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,949

    Re: Save wb to Desktop?

    The return of an Array function should be assigned to a Variant.

        Dim datoteka As String, datum As String, imeKopirajListe As Variant
    The rest of your code should without other changes.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    11-26-2014
    Location
    Bosnia&Herzegovina
    MS-Off Ver
    Office2013
    Posts
    323

    Re: Save wb to Desktop?

    Thank you , sir. Thank you ExcelForum.
    Code is a bit slow. Should i add Application,ScreenUpdating??

    Can i add author to new excel file? Code author name to new file??
    Last edited by B.W.B.; 02-28-2023 at 02:22 PM. Reason: add some query

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,949

    Re: Save wb to Desktop?

    What is "a bit slow"?

    Application.ScreenUpdating = False
    usually speeds things up. You are creating a new file and doing a save, that takes time.

  5. #5
    Forum Contributor
    Join Date
    11-26-2014
    Location
    Bosnia&Herzegovina
    MS-Off Ver
    Office2013
    Posts
    323

    Re: Save wb to Desktop?

    6StringJazzer, u r right, Application.ScreenUpdating do speed things up.

    Where to insert Author.name property??
    Thanks in advance to everyone

  6. #6
    Forum Contributor
    Join Date
    11-26-2014
    Location
    Bosnia&Herzegovina
    MS-Off Ver
    Office2013
    Posts
    323

    Re: Save wb to Desktop?

    'Spremi novu radnu knjigu i zatvori
        novaKopja.BuiltinDocumentProperties("Author").Value = "Humans Rules Not ChatGPT"
        novaKopija.SaveAs fileName:=datoteka, FileFormat:=xlOpenXMLWorkbook
        novaKopja.Close
    Thank YOU 6StringJazzer and ExcelForum!
    Last edited by B.W.B.; 02-28-2023 at 03:34 PM. Reason: sppeling

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. how to save to desktop
    By joee1975 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-29-2022, 06:49 AM
  2. [SOLVED] Save file to Desktop
    By scrabtree23 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-09-2016, 01:24 PM
  3. Save As Button save to Desktop instead of My Documents
    By accurateone in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 08-13-2013, 02:21 PM
  4. [SOLVED] save to desktop macro
    By mike02 in forum Excel General
    Replies: 5
    Last Post: 08-01-2012, 06:12 PM
  5. [SOLVED] save to desktop
    By mike02 in forum Excel General
    Replies: 1
    Last Post: 07-11-2012, 11:43 AM
  6. Save to desktop
    By zerodegreec in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-22-2009, 03:13 AM
  7. [SOLVED] Save .xls as .txt In DeskTop
    By Antonyo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-18-2005, 09:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1