How to make data backup code with excel vba

Question

I make loop code based only on macros, is there any other way that can be used? or at least a simple form of code I created.

Sub xx()
Dim nom As Long
Dim bck As Workbook
Dim I As Long
Windows("LP13.xlsm").Activate
    Application.CutCopyMode = False
    Sheets("Validasi").Range("T2:T10").Copy
    Windows("backup.xlsx").Activate
    Sheets("backup").Range("F1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Windows("LP13.xlsm").Activate
    Application.CutCopyMode = False
    Sheets("Validasi").Range("V2:X11").Copy
    Windows("backup.xlsx").Activate
    Sheets("backup").Range("G1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Windows("LP13.xlsm").Activate
    Application.CutCopyMode = False
    For I = 1 To nom
    Sheets("Data").Range("A2:W" & I).Select
    Next
    Selection.Copy
    Windows("backup.xlsx").Activate
    Sheets("backup").Range("J1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
bck.Save
Application.Visible = False
bck.Close True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

it does look difficult because there are too many repetitions. I want a simpler code in the backup to another workbook.

Your description does not make sense. There is a loop in the code but the value of the variable `nom` is never set. You declare variables 'nom' and 'bck' and you use them in the code but they refer to nothing, since they are not initialised. The rest of your code reads like it's straight from the macro recorder. This is not a good basis for a question here. — teylyn, Dec 21 '17 at 06:08

score 0 · Accepted Answer · answered Dec 21 '17 at 06:30

As for strategy part of your question. you can create file versions backup on save as in http://analystcave.com/excel-quick-versioning-excel-files/

As for macro part 1. I would try to simplyfie the code with variables. You don't need to activate any thing to copy data, this is just way macro recorder works. The backup part is asking for extracting function to paste range.

I would name the ranges in "from" workbook so you don't hardcode rng.address in code. Your macro is not complex so naming range will solve the issue and will give you "documentation" what you coping and where.

If needed you can create sheet with list of source / destination ranges to copy and than pass it to "backup manager"

My take to refactor you code

Option Explicit

Private Type LP13Backup
    FileName  As String ' ?path
    Sht As String
    Rng As String
End Type

Public Sub LP13_BuckupManager() 'yes I know ..Manager ;)

     Dim From As LP13Backup
     Dim Backup As LP13Backup

     From.FileName = "LP13.xlsm"
     From.Sht = "Validasi"
     From.Rng = "A1:B1"

     With From
          Workbooks(.FileName).Worksheets(.Sht).Range(.Rng).Copy
     End With

     Backup.FileName = "backup.xlsx"
     Backup.Sht = "Backup"
     Backup.Rng = "F1"

     CopyToBackup Backup
End Sub

Sub CopyToBackup(ByRef Backup As LP13Backup)
     With Backup
          Workbooks(.FileName).Worksheets(.Sht).Range(.Rng).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
               :=False, Transpose:=False
     End With
End Sub

Word of comment on your solution

For I = 1 To nom
Sheets("Data").Range("A2:W" & I).Select
Next

This is not efecive way to select all values in column. You can go down from first cell

 Range(Range("F1"), Range("F1").End(xlDown)).Address

or go up as in https://stackoverflow.com/a/27066381/7385538

With ws
    lastRowIndex = .Cells(.Rows.Count, "A").End(xlUp).row
End With

what you write makes me understand with your detailed explanation. thanks for a brilliant solution. — Yan LimaBenua, Dec 21 '17 at 22:12

How to make data backup code with excel vba

1 Answers1