VBA Hepl

[CCaliva]

I am trying to design a VBA application so that information that has been placed on say Sheet One is:
Is posted to Sheet Two, then
The details are cleared from Sheet One - so that additional details can be placed on Sheet One
How do I get Excel to move so that the summary from the subsequent set of details is copied to a new row on Sheet Two??

Any help would be appreciated.

Francis P CalivaJr., CPA

 

[JohnG]

Can you be more specificas to the information to be moved. Is it 1 or more rows?

 

[Joe Was]

On your second sheet use 3D references to your data, if Sheet1 cell "A1" has the data you want on sheet2, then on Sheet2 in the cell you want the data from Sheet1 to be, use:

='Sheet1'!A1

Then record a macro that deletes all the cells that you want to reset on Sheet1 to blank.

Now whenever you want to reset your input sheet just run your recorded macro. JSW

 

[CCaliva]

The information from sheet one is summerized into an area of one row by five columns.

Hope this clarifies the original post

Thanks

 

[JohnG]

Try something like this:-

Dim InfoMovedCount As Integer
Dim LastInfoRow As Integer

Sub MoveInfo()
'assuming that the last row in sheet1 contains the info
'sheet2 contains the stored data
'Checks from top of info sheet if there is more than 1 row of data
'assumes continous data with no blanks
If Sheets("sheet1").Range("A1").Value <> "" And _
Sheets("sheet1").Range("A2").Value <> "" Then
LastInfoRow = Sheets("sheet1").Range("A1").End(xlDown).Row
ElseIf Sheets("sheet1").Range("A1").Value <> "" Then
LastInfoRow = Sheets("sheet1").Range("A1").Row
Else
MsgBox "No data found", vbCritical
Exit Sub
End If
If Sheets("sheet2").Range("A1").Value <> "" And _
Sheets("sheet2").Range("A2").Value <> "" Then
InfoMovedCount = Sheets("sheet2").Range("A1").End(xlDown).Row
ElseIf Sheets("sheet2").Range("A1").Value <> "" Then
InfoMovedCount = Sheets("sheet2").Range("A1").Row
Else
InfoMovedCount = 0
End If
'increment for new info
InfoMovedCount = InfoMovedCount + 1
'Copy data
Sheets("sheet2").Range(Rows(InfoMovedCount & ":" & InfoMovedCount).Address).Value = _
Sheets("sheet1").Range(Rows(LastInfoRow & ":" & LastInfoRow).Address).Value
'Delete Original data
Sheets("sheet1").Range(Rows(InfoMovedCount & ":" & InfoMovedCount).Address).Delete

End Sub