Transfer a value or a range of values from one tab to another in @MSExcel

---

This week I will demonstrate how to transfer values or a range of values from one tab to another using VBA code. The alternative of course is to reference the cells which is not always desirable especially in large spreadsheets or in spreadsheets where calculations are set as manual.

Using Manual calculation gives you the flexibility to tell excel when to calculate, rather than Excel taking its own decisions. This is not something that speeds up your Excel workbook, but if you have a slow Excel spreadsheet, it definitely saves time by not making Excel recalculate again and again. The logic is simple – formulas in your worksheet don’t have to go far to get the data when it is right in the next tab.

Consider the table below containing Revenue and Net profit related data for the full year.

Say we want to transfer data belonging to February to a second tab. Here is the code that will get this done for us. Click F11 and insert the code below within the module tab of the VBA editor.

Sub transferdata()
  Application.ScreenUpdating = False
  Dim Copytab As Worksheet
  Dim Pastetab As Worksheet
 
  Set Copytab = Worksheets("2018 Full Year")
  Set Pastetab = Worksheets("Feb 2018")
 
  Copytab.Range("A33:A60").Copy
  Pastetab.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 
  Copytab.Range("B33:B60").Copy
  Pastetab.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 
  Copytab.Range("C33:C60").Copy
  Pastetab.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 
  Copytab.Range("D33:D60").Copy
  Pastetab.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 
  Copytab.Range("E33:E60").Copy
  Pastetab.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  
Application.CutCopyMode = False
End Sub 

How this code works?

• Lines 6 and 7 of this code are where the name of the source and destination tabs are inputted (i.e. “2018 Full Year” and “Feb 2018” respectively)
  
• 
  

  Set Copytab = Worksheets("2018 Full Year")
  Set Pastetab = Worksheets("Feb 2018")

• Lines 9 to 22 contain details on which ranges need to be copied from the source tab and to which column of the destination tab they need to be transferred to. For example, in the piece of code below, “A33:A60” is the range belonging to the month of February in the “2018 Full Year” tab and this range is programmed to be transferred to the 1st columns of the “Feb 2018” tab as referenced  (i.e. Pastetab.Cells(Rows.Count, 1)….) . The same principal applied to the four other fields as well.
  

Copytab.Range("A33:A60").Copy
Pastetab.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 
 Here is the outcome in the “Feb 2018” tab after running the macro

---