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