VBA Moving rows of data between sheet to sheet

[jislandhopper]

Hello,

Hope the community can help with this one. It surrounds using VBA to cut or move a row from one sheet and pasting into another. I’ve looked online and found similar issues but nothing I found I can translate into my own problem. I have an OK understanding on excel formula but VBA is an area where I don’t know as much and trying to learn as a go. There is a lot of data and the times I have tried Excel became very slow and on the whole didn't work.

I have lines of data in sheet 1 (titled ‘Data’) relating to each job I undertake. The information runs from Column A:AU. I would like to do is type “File” into Column N which automatically moves row within A:AU into Sheet 2 (titled ‘History’)

When the information is moved into sheet 2 I would like the information to create a list where the last entry would be on the bottom of the list. Then remove the blank line in sheet 1 to bring the data together.

So in summary;
I would Type “File” in Column N sheet 1 (titled ‘Data’)
This would automatically move the line or row of data A:AU to Sheet 2 (titled ‘History’).
The row of data pasted would be at the bottom of that list in Sheet 2 (along with the other entry’s)
Sheet 1 would remove the blank row.

Hope that explains the issue, it sounds so easy as can be done in a few keystrokes but I’m making me pull my hair our trying to find a solution so any help or guidance is much appreciated,

Thanks,
Jason

 

[GWteB]

@GWteB I've had a play with your code but not getting much luck. The line from sheet 1 disappears but doesn't re-appear in sheet 2.

Did you use the modified code as of post #9?

Also 'MoveToHistory' comes up as an ambiguous name. If you change it, it returns with 'function not defined'

This means you have two or more procedures with the same name within your project.

 

[Snakehips]

@jislandhopper My humble apologies. I put XX in front of the Sub Name in order to stop it triggering on my worksheet and posted without removing it!!!

Assuming you do NOT have any other pre-existing Change _Event code then paste the below into your data sheet's code module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Column = 14 Then Exit Sub
If Target <> "File" Then Exit Sub
Response = MsgBox("Do you really wish to file this row?", vbYesNo, "Just Checking!")
If Not Response = vbYes Then Exit Sub
Application.EnableEvents = False
With Sheets("Historic")

NewRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A" & NewRow & ":AU" & NewRow) = Target.Offset(0, -13).Resize(1, 47).Value
End With
Target.EntireRow.Delete
Application.EnableEvents = True
End Sub

 

[Snakehips]

As for the formulas. It is the formulas in the data sheet that could well be upset by the deletion of a row. Test on a backed-up worksheet and see if the deletion causes any formulas to result in error.
If so then you we will need to consider another approach to this.

 

[jislandhopper]

Ah 'MoveToHistory was duplicated that seems to have solved the issue thanks to both of you.

@GWteB Taking the duplicate has part of your code working partially. The line in sheet 1 is cancelled regardless if the file was copied to sheet 2. I've tried renaming the sheet 2 to fall in line with your VBA as a test but can't seem to understand why it's not working.

@Snakehips Now I've removed the duplicate yours is working really well. The only thing I noticed was some lines don't paste over to sheet 2. I done a little trial and error and think i realize what the issue is. If Column A in sheet 1 is not populated the copied line overwrites the last line in sheet 2. Column E is constantly populated if that helps as a marker, is there a way to adjust the VBA or does Column A have to be populated? If that's the case is it possible to write into VBA if cell in Column A is empty to enter a decimal before moved to the sheet?

I've tried to make the code in marry into your existing VBA but unsure how to localize the cell. Am I close with the code below?

Dim rng As Range
Dim i As Long
Dim cell As Range
Dim sht As Worksheet
Set sht = ActiveWorkbook.Sheets("Data")
sht.Activate
If cell.Value = "" Then cell.Value = "."
Next

 

[Snakehips]

Try the below. It will check the constantly populated column E of the Historic sheet to determine the next row, rather than A.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Column = 14 Then Exit Sub
If Target <> "File" Then Exit Sub
Response = MsgBox("Do you really wish to file this row?", vbYesNo, "Just Checking!")
If Not Response = vbYes Then Exit Sub
Application.EnableEvents = False
With Sheets("Historic")

NewRow = .Range("E" & Rows.Count).End(xlUp).Row + 1
.Range("A" & NewRow & ":AU" & NewRow) = Target.Offset(0, -13).Resize(1, 47).Value
End With
Target.EntireRow.Delete
Application.EnableEvents = True
End Sub

 

[GWteB]

@GWteB Taking the duplicate has part of your code working partially. The line in sheet 1 is cancelled regardless if the file was copied to sheet 2. I've tried renaming the sheet 2 to fall in line with your VBA as a test but can't seem to understand why it's not working.

A worksheet has two names: a code name and a tab name. When you're in VBE, hit CTRL R and unfold the Microsoft Excel Objects. The code name is up front, the tab name is between brackets. I used the code name since the name "History" you used is a reserved name. My code does not check whether the copy part has taken place, assuming you had adjusted the code as required:

Set oWs = Sheet2   ' <<< code name of your History sheet; change accordingly or use:      Set oWs = ThisWorkbook.Sheets("TheTabNameOfYourSheet")

 

[jislandhopper]

@Snakehips that's worked a treat. Thanks so much for your help.

@GWteB I've had a little more time to play with the code and I've got it working but not constantly. But thanks for what you have done.

Thanks to you both, I'm not sure how you do it but im always amazed what can be achieved.

 

[GWteB]

Glad it's sorted & thanks for letting us know.