Consulting

Results 1 to 12 of 12

Thread: Create a Hardcoded Workbook Copy

  1. #1
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location

    Create a Hardcoded Workbook Copy

    Hi

    I' getting the following error while running the trailing code. Could somebody please help me to correct the codes?

    Thanks in advance

    error.jpg

    Sub Hardcoded_Workbook_Copy()
    
        Dim FilePath As String
        Dim HCName As String
            
        FilePath = ActiveWorkbook.Path & "\"
        HCName = "HC_" & VBA.Format(Date, "ddmmyy") & "_" & ActiveWorkbook.Name
        ActiveWorkbook.Save
        Sheets.Select
        Cells.Copy
        Cells.PasteSpecial xlPasteValues
        Range("A1").Select
        Sheets(1).Select
        ActiveWorkbook.SaveAs Filename:=FilePath & HCName
        
    End Sub
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    I'd suggest to follow a course in VBA basics first before continuing this path.

  3. #3
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    Thanks for your advice VBAX Guru. I'm in the middle of a VBA course

  4. #4
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    Try this :

    Option Explicit
    
    
    Sub Hardcoded_Workbook_Copy()
    
    
        Dim FilePath As String
        Dim HCName As String
            
        FilePath = ActiveWorkbook.Path & "\"
        HCName = "HC_" & VBA.Format(Date, "ddmmyy") & "_" & ActiveWorkbook.Name
        'ActiveWorkbook.Save
        'Sheets.Select
        'Cells.Copy
        'Cells.PasteSpecial xlPasteValues
        
        ActiveWorkbook.SaveAs Filename:=FilePath & HCName
        Sheets(1).Select
        Range("A1").Select
    End Sub
    You are going through a number of steps in your original macro which results in a backup of your workbook. Rather than copy / paste all the sheets
    ... just copy the entire workbook and re-create it as a backup.

    Your path will force VBA to create a COPY. If you don't want the term COPY at the end of the file name ... code a different folder for the backup to
    be saved in.

  5. #5
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    @ Logit
    Thanks for your response. But I need to create a copy of the workbook without any formulas

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    It'd be helpful if your sample had data, formulas, multiple sheets, and links that worked

    If you provided an updated one, it's be easier to see
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .

    See if you can start with this ...

    Option Explicit
    
    
    Sub abcd()
    Dim myWB As Workbook, WB As Workbook
    Dim myName
    Dim ws As Worksheet
    
    
    Set myWB = ThisWorkbook
    myName = ThisWorkbook.Name
    
    
    Dim r As Range
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    
    For Each ws In Sheets
        For Each r In ws.UsedRange
            If Not IsEmpty(r) Then r.Value = r.Value
        Next
    Next
    
    
    ActiveWorkbook.SaveAs (ThisWorkbook.Path & "\FILE XLSX.xlsm")
    Set WB = ActiveWorkbook
    
    
    Application.DisplayAlerts = True
    
    
    Workbooks.Open (ThisWorkbook.Path & "\" & myName)
    WB.Close
    
    
    Application.ScreenUpdating = True
    End Sub

  8. #8
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    Thanks Logit

  9. #9
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    You are welcome. Let us know if you need more help.

  10. #10
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub M_snb()
      For Each it In Sheets
        it.UsedRange.Value = it.UsedRange.Value
      Next
    
      ActiveWorkbook.SaveCopyAs ActiveWorkbook.Path & "\" & Replace(ActiveWorkbook.Name, ".", "_backup.")
    End Sub
    Last edited by snb; 11-30-2020 at 04:40 AM.

  11. #11
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    Thanks for all your support.
    I have replaced 'Thisworkbook' to 'Activeworkbook to have this code in my personal.xlsb to use as and when required

    Option Explicit
    
    
    Sub Hardcoded_Workbook_Copy()
    
    
    Dim Original_WB As Workbook
    Dim MyName As String, HCName As String
    Dim WS As Worksheet
    
    
    
    
    Set Original_WB = ActiveWorkbook
    MyName = Original_WB.Name
    HCName = "HC_" & VBA.Format(Date, "ddmmyyyy") & "_" & Original_WB.Name
    
    
    Dim r As Range
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    
    Original_WB.Save
    
    
    For Each WS In Sheets
        For Each r In WS.UsedRange
            If Not IsEmpty(r) Then r.Value = r.Value
        Next
    Next
    
    
    
    
    Original_WB.SaveAs (Original_WB.Path & "\" & HCName)
    
    
    Application.DisplayAlerts = True
    
    
    Workbooks.Open (Original_WB.Path & "\" & MyName)
    Workbooks(HCName).Close
    
    
    Application.ScreenUpdating = True
    
    
    End Sub

  12. #12
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .

Posting Permissions

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