PDA

View Full Version : Solved: Copy deleted info



ads_3131
07-25-2011, 06:03 AM
Hey, some help would be great...

basically i have attached a example sheet, sheet 1 has a table which you can enter data in... and a clear button which deletes the data in that row.

Sheet 2 has a backup log and what im hoping to do is when the clear button is clicked , not only will it clear the row but copy it into sheet 2s table (see example.xls provided).

Now the tricky bit......... doing it random, so any row on sheet one could be cleared but would like to copies on sheet 2 to go in order down.?

Any help would be great

Regards
ads_3131

Kenneth Hobs
07-25-2011, 08:58 AM
Do you really have to use data setup like that? It is easier if you use database methods where fieldnames are the column names in row 1 for each sheet. You can always do something in a 3rd sheet or output the data to an MSWord file to get a nice report.

ads_3131
07-26-2011, 12:34 AM
Thanks for the quick reponse .... the purpose is for a backing up data that isnt used , originally i will be implementing it into a works production scheduler

where sheets one & two hold active jobs but when the clear button is clicked it would then clear the job on that row and archive onto sheet three....

I agree a database setup would be more suited for this but that isnt what they want :( and im stuck....

need help

ads_3131
07-26-2011, 12:56 AM
Just a update, got some code added into the clear button which copies a set range from one sheet to a set range in another before clearing that row......

Thats half the battle, its the random part where any row, in any order can be removed and it would go in logical 1,2,3,4,5 etc in the second worksheet

see example attached.....

Kenneth Hobs
07-26-2011, 05:46 AM
I recommend that you not use object names like CLRx where x is a number. That could be a range that Excel will complain about.

This does it for all, one, or many. Sub ClearAll()
Dim row As Range
For Each row In Range("D5").CurrentRegion.Rows
If row.Address(False, False) <> "D4:I4" Then
row.Copy Worksheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Offset(1)
row.ClearContents
End If
Next row
End Sub

Sub ClearCurrent()
Dim r As Long
r = ActiveCell.row
Range("D" & r & ":I" & r).Copy Worksheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Offset(1)
Range("D" & r & ":I" & r).ClearContents
End Sub

Sub ClearMany()
Dim r As Long, cell As Range
For Each cell In Selection
r = cell.row
Range("D" & r & ":I" & r).Copy Worksheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Offset(1)
Range("D" & r & ":I" & r).ClearContents
Next cell
End Sub

ads_3131
08-02-2011, 03:27 AM
Tar Ken, Your a star!