Move row in Excel to another sheet based on a number entered

[djkeller]

I have an Account Contact Management Excel workbook with multiple sheets for different sales reps. I want to find a way to write a code that when a rep would like to move an account into the "Inactive" tab.

Each row in the sheet corresponds to one account contact. I'm imagining a button that the rep could push and it would say "Enter Account number to Inactivate." The macro would search the sheet for the row with that account number and cut the row out of the sheet and paste it into the Inactive tab.

I imagine there is a way to do this, but I'm fairly new to VBA. Any help would be greatly appreciated!

 

[SkipVought]

Hi,

I'd venture a guess that your basic workbook design is gravely flawed.

This sort of status change is routinely done in database systems. 1) All the data is in ONE TABLE. 2) The change is simply changing the value in a STATUS column and possibly posting a date/time stamp.

You would probably be much better served by redesigning your workbook to have ALL your data in one table with one or two additional columns. Report, analysis, maintenance will be a WHOLE LOT SIMPLER, as Excel's features are not designed to work very will on common data in multiple locations.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[djkeller]

Skip,

I'd agree. I'm just another sales rep here, but I quickly fell into a position where I've been helping a lot of people out with their computer issues. I inherited this list as an Excel file. I think this should eventually go into an Access program, but that's a whole other project. Thanks for the advice,

Dan

 

[SkipVought]

OK, if you must retain this dreadful monstrosity,

turn on your macro recorder and record...

use Edit > Find tp find the row.

CUT the row

Activate the Inactive sheet

select the first row/column of data.

END+[DOWN ARROW] [DOWN ARROW]

Edit > Paste

Turn off your recorder.

alt+F11 to toggle between sheet and VB Editor.

COPY your recorded code and post back if you need help customizing.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[djkeller]

Columns("A:A").Select
Selection.Find(What:="30334495", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Rows("413:413").Select
Selection.Cut
Sheets("Inactive").Select
Rows("2:2").Select
Selection.End(xlDown).Select
Range("A8").Select
ActiveSheet.Paste
End Sub


Skip,

That seemed to do the trick, but only for that specific account number. Is there a way to prompt the user to enter in the account number that they want to inactivate (find)?

 

[SkipVought]

    Dim sValue, rFound As Range
    
    sValue = InputBox("Enter value")
    
    If sValue <> "" Then
        Set rFound = Columns("A:A").Find(sValue)
        
        If Not rFound Is Nothing Then
            rFound.EntireRow.Cut
            Sheets("Inactive").Cells(2, 1).End(xlDown).Offset(1).PasteSpecial xlPasteAll
        End If
    End If

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[djkeller]

It is giving me a message
Run Time Error '1004'

PasteSpecial method of Range class failed


and highlighting line

Sheets("Inactive").Cells(2, 1).End(xlDown).Offset(1).PasteSpecial xlPasteAll

 

[SkipVought]

    Dim sValue, rFound As Range
    
    sValue = InputBox("Enter value")
    
    If sValue <> "" Then
        Set rFound = Columns("A:A").Find(sValue)
        
        If Not rFound Is Nothing Then
            rFound.EntireRow.Copy
            Sheets("Inactive").Cells(2, 1).End(xlDown).Offset(1).PasteSpecial xlPasteAll
            rFound.EntireRow.Delete xlShiftUp
        End If
    End If

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[djkeller]

Works perfectly. Thanks for all your help!

 

[Markus4]

Off topic (of course), but I really cannot resist to post.

How beautifully put:
- U'd venture a guess
- dreadful monstrosity

M.

 

[SkipVought]

In certain circumstances, I am want to hyperbole.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}