You are here:

Excel/Transfer of data from Sheet1 to Sheet2


Hi Tom

I have created a Bank Statement in the following format.

column A is the date
column B is the description
column C amounts paid in
column D amounts paid out
column E is the running balance (formula)

This data starts at row 7 (A7) and ends at row 60 (E60), as I wish each statement to be just one page deep.

Above this data, in cell E4, is the unique statement number.

What I would like, is a macro to transfer the completed data (A7:E60) to Sheet2, leaving these cells (Sheet1) blank, free
for the next statement.

However, if I subsequently type a statement number which I have used before, the data pertaining to it, on Sheet2 will reload
into Sheet1.

I hope I have explained clearly!

Please can you help?


David, the first part is straightforward, but implementing the restoration of existing data would be more complex.  Since it appears you are not an expert in VBA, let me suggest a simpler approach.

Saveing the data would be the same as you describe except I have the code write the data to a sheet named Sheet2, then renaming that sheet to use the Unique statement number as the sheet name and adding a new blank Sheet2.

then if you type in a unique statement number on sheet1 and run the macro restoredata, the macro will try to find a sheet with that name and if found, copy the data back to sheet1.  If not, it will tell you the data was not found.  This seems much simpler than trying to use the change event to detect that you have entered a value in E4.  

I also added a cleardata macro because once old data is brought into Sheet1, you did not say what you would do with that data.  If you run savedata, it would copy the data to sheet2, then try to rename the sheet and since that sheet name already exists, it would create an error and the macro would stop.  So unless you want to edit and update the data, there doesn't appear to be a reason why you would copy that old data - so you can just run the cleardata macro to clear sheet1.   All data is entered on a sheet named Sheet1 as you described.

Sub savedata()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1 As Range, r2 As Range
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
Set r1 = sh1.Range("A7:E60")
Set r2 = sh1.Range("E4")
r1.Copy sh2.Range("A7:E60")
r2.Copy sh2.Range("E4")
sh2.Name = sh2.Range("E4")
Worksheets.Add After:=sh1
ActiveSheet.Name = "Sheet2"
End Sub

Sub ClearData()
Dim sh1 As Worksheet
Set sh1 = Worksheets("Sheet1")
End Sub

Sub restoredata()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1 As Range, r2 As Range
Set sh1 = Worksheets("Sheet1")
Set r2 = sh1.Range("E4")
On Error Resume Next
Set sh2 = Worksheets(r2.Text)
On Error GoTo 0
If Not sh2 Is Nothing Then
Set r1 = sh2.Range("A7:E60")
Set r2 = sh2.Range("E4")
r1.Copy sh1.Range("A7:E60")
r2.Copy sh1.Range("E4")
 MsgBox sh1.Range("E4").Value & " not found"
End If
End Sub

All macros should be placed in a standard/general module.  In the visual basic editor, do Insert => Module

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 All rights reserved.

[an error occurred while processing this directive]