VBA Copy Cells Stable

Question

I'm very new to VBA and have put together a macro that currently does exactly what I want: copy cells from one worksheet to another, clear the cells copied, refresh my pivot tables and save the file.

Here is what I am using for it:

Sub Copyinfo()

    On Error GoTo Err_Execute

    Sheet1.Range("A17:R17").Copy
    Sheet2.Range("A2").Rows("2:2").Insert shift:=xlDown
    Sheet2.Range("A2").Rows("2:2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

    Sheet1.Range("C5:I16, Q5:R5").ClearContents
Err_Execute:
    If Err.Number = 0 Then MsgBox "Copied Data", vbInformation Else _
    MsgBox Err.Description

    ThisWorkbook.RefreshAll

    ThisWorkbook.Save
End Sub

What I want to know is whether this macro will be stable as I continue to add lines of data to it in the future. Essentially I want to minimize the risk of the file crashing or my data being erased. Otherwise, would anyone have any suggestions to offer me to improve this? Thank you.

Answer 1

few things that come to mind on a quick look:

You might consider naming this something other than "Copyinfo". If it is archiving transactions, for example, you might name it "ArchiveTransactions". If you do decide to stick with the name, you should go with the Pascal Case version: "CopyInfo". While you're at it: it's always good to specify public instead of leaving it implicit.

You don't need to do Sheet2.Range("A2").Rows("2:2"), you can also do Sheet2.Range("2:2") or just Sheet2.Rows("2:2"), or (and most simply) Sheet2.Rows(2). I like putting things in With blocks (although many people won't for just two things) because I like reducing code duplication.

As for the syntax with the If statement... If there is an Else, it should be on multiple lines, in my opinion. The way you had it above with the line continuation seems to go out of it's way to stay on one line (which, it doesn't anyway!). Clarity always wins over conciseness on my book.

If you use Application.ScreenUpdating as shown below, it will eliminate the screen flicker that can happen when you switch sheets, as well as speed up the performance (which, since this Sub doesn't do a whole lot, performance tuning isn't necessary).

Public Sub Copyinfo()

    Application.ScreenUpdating = False

    On Error GoTo Err_Execute

    Sheet1.Range("A17:R17").Copy

    With Sheet2.Rows(2)
        .Insert shift:=xlDown
        .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    End With

    Sheet1.Range("C5:I16, Q5:R5").ClearContents

Err_Execute:
    If Err.Number = 0 Then
        MsgBox "Copied Data", vbInformation
    Else
        MsgBox Err.Description
    End If

    ThisWorkbook.RefreshAll
    ThisWorkbook.Save

    Application.ScreenUpdating = True

End Sub

Answer 2

You wrote some code, but you didn't use any variables! Using variables will allow you to expand the code by one single change to the variable rather than all over the place. Even something simple

Option Explicit
Sub Copyinfo()

    On Error GoTo Err_Execute
    Dim sourceRange As Range
    Dim destinationRange As Range
    Dim rangeToClear As Range
    Set rangeToClear = Sheet1.Range("C5:I16, Q5:R5")

    Set sourceRange = Sheet3.Range("A1:i1")
    Set destinationRange = Sheet2.Range("B1")

    destinationRange.EntireRow.Insert shift:=xlDown
    sourceRange.Copy Destination:=destinationRange

    rangeToClear.ClearContents

Err_Execute:
    If Err.Number = 0 Then
        MsgBox "Copied Data", vbInformation
    Else: MsgBox Err.Description
    End If

    ThisWorkbook.RefreshAll

End Sub