Automatically move data from one sheet to another

[mccuskerj]

Hi Team,
Hoping someone can help me.

I have a sheet with boxes that captures some user data (name, email, contact etc) and I call that sheet 1 - Input Sheet. I then use this to fill information in on that person.

When I hit save, I want that data to be copied into Sheet 2 - Output sheet but across in rows. Then it should empty the contents of Sheet 1, ready to fill it in again.

I can't get it to work....maybe some one can help. I have copied screen shots of both sheets.

(Disregard my styling I will fix all that when I know its working)

 

[VBE313]

I am not sure how you want the columns in Sheet2. But for my example I kept Sheet2 the same columns as Sheet1. If you paste the following code in the "ThisWorkbook" module in VBE, it should work when pressing the Save button.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim lastRow As Long, arr As Variant, strCells As String
With Sheets("Sheet2")
    lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
    With Sheets("Sheet1")
        arr = Array(.Range("D4").Value, .Range("D6").Value, .Range("D8").Value, .Range("D9").Value, _
        .Range("D11").Value, .Range("H4").Value, .Range("H6").Value, .Range("H8").Value)
    End With
    strCells = "A" & lastRow & ":H" & lastRow
    .Range(strCells).Value = arr
End With
End Sub

 

[mccuskerj]

Fantastic...I will give it a go and let you know. I wanted the columns in sheet two to be in a line line by line format.
But I will check this out. THanks so much.

 

[mccuskerj]

That seems to have worked great. But I have an additional query.
1. Can the form in Sheet 1 have the boxes cleared after it writes the data to Sheet 2. (Basically left blank ready for the next input of data)
2. I am typing the word save into the sheet to make it write the data to Sheet 2. Could this be a 'Save Button' , like a macro that runs when it is clicked?

 

[VBE313]

Sorry, I forgot about the Removing values... Here you go

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim lastRow As Long, arr As Variant, strCells As String
With Sheets("Sheet2")
    lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
    With Sheets("Sheet1")
        arr = Array(.Range("D4").Value, .Range("D6").Value, .Range("D8").Value, .Range("D9").Value, _
        .Range("D11").Value, .Range("H4").Value, .Range("H6").Value, .Range("H8").Value)
    End With
    strCells = "A" & lastRow & ":H" & lastRow
    .Range(strCells).Value = arr
End With
With Sheets("Sheet1")
    .Range("D4").Value = ""
    .Range("D6").Value = ""
    .Range("D8").Value = ""
    .Range("D9").Value = ""
    .Range("D11").Value = ""
    .Range("H4").Value = ""
    .Range("H6").Value = ""
    .Range("H8").Value = ""
End With
End Sub

As for the button, insert a button and add this macro to it. Make sure to put this in a Module instead of ThisWorkbook, unless it is an ActiveX button, then save it in Sheet1 (Sheet1) Microsoft Excel Objects

Sub SaveWorkbook()
ThisWorkbook.Save
End Sub

 

[mccuskerj]

You have been amazing. I will certainly try these and respond. Thanks so much.

 

[VBE313]

You are welcome. Let me know if you need anything else.

 

[mccuskerj]

So it seems to have stopped on me...I've no clue what is wrong now at all. i know its relatively simple but its killing me to resolve.
It looks like its in a loop

 

[VBE313]

Is your name Jack, James, or Joseph?

 

[mccuskerj]

Joseph, but I'm known as Joe.