Deleting rows of cells from one sheet and moving specific cells to a different sheet

M

m0aje

New Member
Joined
Mar 1, 2012
Messages
11
Hello All,

I have a workbook with two sheets. The "Server" sheet (example below) and "Servers Pulled" Column "B" is "Shipping status" with a drop down list. When I choose "SHIPPED" from the drop-down list, I would like to be able to have that row delete from the "Server" sheet and move to the next available line on the "Servers Pulled". However, I only need columns A, and I through AE displayed on the "Servers Pulled" sheet. Not sure if that is possible or not.
I have had some experience with formulas (I get lucky), but none with macros or VBA code. I have looked at some examples and tried copying some into my sheet, but I must not be doing it right. I get no indications that the code is even being applied. Any help would be appreciated along with some guidence on how to actually copy the code into the sheet. I am using Excel 2007.

Many thanks!

m0jae


[TABLE="width: 2855"]
<TBODY>[TR]
[TD="class: xl108, width: 84, bgcolor: yellow"]Barcode No.
[/TD]
[TD="class: xl108, width: 87, bgcolor: yellow"]Shipping
[/TD]
[TD="class: xl89, width: 95, bgcolor: yellow"]DATE FOR
[/TD]
[TD="class: xl89, width: 161, bgcolor: yellow"]TEST DATE
[/TD]
[TD="class: xl76, width: 95, bgcolor: #00b0f0"]TEST DATE
[/TD]
[TD="class: xl111, width: 51, bgcolor: #00b0f0"]SAT
[/TD]
[TD="class: xl76, width: 34, bgcolor: #00b0f0"]TECH
[/TD]
[TD="class: xl88, width: 30, bgcolor: yellow"]TEST
[/TD]
[TD="class: xl78, width: 80, bgcolor: yellow"]Part No.
[/TD]
[TD="class: xl110, width: 98, bgcolor: yellow"]Server Serial No.
[/TD]
[TD="class: xl79, width: 74, bgcolor: yellow"]Folder No.
[/TD]
[TD="class: xl80, width: 342, bgcolor: yellow"]Description
[/TD]
[TD="class: xl77, width: 144, bgcolor: yellow"]Location
[/TD]
[TD="class: xl76, width: 110, bgcolor: #00b0f0"]Ethernet
[/TD]
[TD="class: xl76, width: 116, bgcolor: #00b0f0"]Ethernet
[/TD]
[TD="class: xl76, width: 109, bgcolor: #00b0f0"]Ethernet
[/TD]
[TD="class: xl76, width: 108, bgcolor: #00b0f0"]Ethernet
[/TD]
[TD="class: xl76, width: 108, bgcolor: #00b0f0"]Ethernet
[/TD]
[TD="class: xl76, width: 108, bgcolor: #00b0f0"]Ethernet
[/TD]
[TD="class: xl76, width: 108, bgcolor: #00b0f0"]Ethernet
[/TD]
[TD="class: xl76, width: 108, bgcolor: #00b0f0"]Ethernet
[/TD]
[TD="class: xl76, width: 108, bgcolor: #00b0f0"]Ethernet
[/TD]
[TD="class: xl76, width: 108, bgcolor: #00b0f0"]Ethernet
[/TD]
[TD="class: xl76, width: 108, bgcolor: #00b0f0"]Ethernet
[/TD]
[TD="class: xl76, width: 108, bgcolor: #00b0f0"]Ethernet
[/TD]
[TD="class: xl76, width: 108, bgcolor: #00b0f0"]FIBER NIC
[/TD]
[TD="class: xl76, width: 108, bgcolor: #00b0f0"]FIBER NIC
[/TD]
[TD="class: xl79, width: 277, bgcolor: yellow"]Assigned by / Warehouse Tech.
[/TD]
[TD="class: xl79, width: 224, bgcolor: yellow"]LABEL CLEARLY MARKING SERVER
[/TD]
[TD="class: xl79, width: 253, bgcolor: yellow"]VISUAL INSPECTION OF MATERIAL BY
[/TD]
[TD="class: xl79, width: 150, bgcolor: yellow"]SHIPPED DATE
[/TD]
[/TR]
[TR]
[TD="class: xl122, bgcolor: yellow"][/TD]
[TD="class: xl106, bgcolor: yellow"][/TD]
[TD="class: xl99, bgcolor: yellow"][/TD]
[TD="class: xl100, bgcolor: yellow"][/TD]
[TD="class: xl98, bgcolor: #00b0f0"][/TD]
[TD="class: xl112, bgcolor: #00b0f0"][/TD]
[TD="class: xl98, bgcolor: #00b0f0"][/TD]
[TD="class: xl102, bgcolor: yellow"]IN
[/TD]
[TD="class: xl94, bgcolor: yellow"][/TD]
[TD="class: xl95, bgcolor: yellow"][/TD]
[TD="class: xl96, bgcolor: yellow"][/TD]
[TD="class: xl97, bgcolor: yellow"][/TD]
[TD="class: xl93, bgcolor: yellow"][/TD]
[TD="class: xl98, bgcolor: #00b0f0"][/TD]
[TD="class: xl98, bgcolor: #00b0f0"][/TD]
[TD="class: xl101, bgcolor: #00b0f0"][/TD]
[TD="class: xl98, bgcolor: #00b0f0"][/TD]
[TD="class: xl98, bgcolor: #00b0f0"][/TD]
[TD="class: xl98, bgcolor: #00b0f0"][/TD]
[TD="class: xl98, bgcolor: #00b0f0"][/TD]
[TD="class: xl98, bgcolor: #00b0f0"][/TD]
[TD="class: xl98, bgcolor: #00b0f0"][/TD]
[TD="class: xl98, bgcolor: #00b0f0"][/TD]
[TD="class: xl98, bgcolor: #00b0f0"][/TD]
[TD="class: xl98, bgcolor: #00b0f0"][/TD]
[TD="class: xl98, bgcolor: #00b0f0"][/TD]
[TD="class: xl98, bgcolor: #00b0f0"][/TD]
[TD="class: xl96, bgcolor: yellow"][/TD]
[TD="class: xl96, bgcolor: yellow"][/TD]
[TD="class: xl96, bgcolor: yellow"][/TD]
[TD="class: xl96, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl123, bgcolor: yellow"][/TD]
[TD="class: xl107, bgcolor: yellow"]Status
[/TD]
[TD="class: xl91, bgcolor: yellow"]RE-TEST
[/TD]
[TD="class: xl92, bgcolor: yellow"]STATUS
[/TD]
[TD="class: xl113, bgcolor: #00b0f0"](DD-MM-YYYY)
[/TD]
[TD="class: xl113, bgcolor: #00b0f0"]UNSAT
[/TD]
[TD="class: xl86, bgcolor: #00b0f0"][/TD]
[TD="class: xl90, bgcolor: yellow"]MO.
[/TD]
[TD="class: xl82, bgcolor: yellow"][/TD]
[TD="class: xl83, bgcolor: yellow"][/TD]
[TD="class: xl84, bgcolor: yellow"][/TD]
[TD="class: xl85, bgcolor: yellow"][/TD]
[TD="class: xl81, bgcolor: yellow"][/TD]
[TD="class: xl86, bgcolor: #00b0f0"]MAC Address 1
[/TD]
[TD="class: xl86, bgcolor: #00b0f0"]MAC Address 2
[/TD]
[TD="class: xl87, bgcolor: #00b0f0"]MAC Address 3
[/TD]
[TD="class: xl86, bgcolor: #00b0f0"]MAC Address 4
[/TD]
[TD="class: xl86, bgcolor: #00b0f0"]MAC Address 5
[/TD]
[TD="class: xl86, bgcolor: #00b0f0"]MAC Address 6
[/TD]
[TD="class: xl86, bgcolor: #00b0f0"]MAC Address 7
[/TD]
[TD="class: xl86, bgcolor: #00b0f0"]MAC Address 8
[/TD]
[TD="class: xl86, bgcolor: #00b0f0"]MAC Address 9
[/TD]
[TD="class: xl86, bgcolor: #00b0f0"]MAC Address 10
[/TD]
[TD="class: xl86, bgcolor: #00b0f0"]MAC Address 11
[/TD]
[TD="class: xl86, bgcolor: #00b0f0"]MAC Address 12
[/TD]
[TD="class: xl86, bgcolor: #00b0f0"]MAC Address 1
[/TD]
[TD="class: xl86, bgcolor: #00b0f0"]MAC Address 2
[/TD]
[TD="class: xl84, bgcolor: yellow"][/TD]
[TD="class: xl84, bgcolor: yellow"][/TD]
[TD="class: xl84, bgcolor: yellow"][/TD]
[TD="class: xl84, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl104, bgcolor: yellow"]640024
[/TD]
[TD="class: xl109"]SHIPPED
[/TD]
[TD="class: xl114"]NRFI
[/TD]
[TD="class: xl72, bgcolor: transparent"]Enter Test Date
[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl73"]SAT
[/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"]3
[/TD]
[TD="class: xl75, bgcolor: yellow"]ABC-00103
[/TD]
[TD="class: xl69, bgcolor: yellow"]11051062
[/TD]
[TD="class: xl68, bgcolor: yellow"]151-11-049
[/TD]
[TD="class: xl69, bgcolor: yellow"]EQUIPMENT (First order)
[/TD]
[TD="class: xl71, bgcolor: yellow"]SHIPPED
[/TD]
[TD="class: xl103, bgcolor: yellow"][/TD]
[TD="class: xl104, bgcolor: yellow"][/TD]
[TD="class: xl116, bgcolor: yellow"][/TD]
[TD="class: xl117, bgcolor: yellow"][/TD]
[TD="class: xl117, bgcolor: yellow"][/TD]
[TD="class: xl117, bgcolor: yellow"][/TD]
[TD="class: xl117, bgcolor: yellow"][/TD]
[TD="class: xl117, bgcolor: yellow"][/TD]
[TD="class: xl117, bgcolor: yellow"][/TD]
[TD="class: xl117, bgcolor: yellow"][/TD]
[TD="class: xl117, bgcolor: yellow"][/TD]
[TD="class: xl118, bgcolor: yellow"][/TD]
[TD="class: xl104, bgcolor: yellow"][/TD]
[TD="class: xl104, bgcolor: yellow"][/TD]
[TD="class: xl70, bgcolor: yellow"][/TD]
[TD="class: xl71, bgcolor: yellow"]Awaiting disposition
[/TD]
[TD="class: xl67, bgcolor: yellow"]ON Assembly
[/TD]
[TD="class: xl68, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl104, bgcolor: yellow"]640032
[/TD]
[TD="class: xl109"]SHIPPED
[/TD]
[TD="class: xl114"]NRFI
[/TD]
[TD="class: xl72, bgcolor: transparent"]Enter Test Date
[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl73"]SAT
[/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"]3
[/TD]
[TD="class: xl75, bgcolor: yellow"]ABC-00103
[/TD]
[TD="class: xl69, bgcolor: yellow"]11051070
[/TD]
[TD="class: xl68, bgcolor: yellow"]151-11-049
[/TD]
[TD="class: xl69, bgcolor: yellow"]EQUIPMENT (First order)
[/TD]
[TD="class: xl71, bgcolor: yellow"]SHIPPED
[/TD]
[TD="class: xl103, bgcolor: yellow"][/TD]
[TD="class: xl104, bgcolor: yellow"][/TD]
[TD="class: xl119, bgcolor: yellow"][/TD]
[TD="class: xl120, bgcolor: yellow"][/TD]
[TD="class: xl120, bgcolor: yellow"][/TD]
[TD="class: xl120, bgcolor: yellow"][/TD]
[TD="class: xl120, bgcolor: yellow"][/TD]
[TD="class: xl120, bgcolor: yellow"][/TD]
[TD="class: xl120, bgcolor: yellow"][/TD]
[TD="class: xl120, bgcolor: yellow"][/TD]
[TD="class: xl120, bgcolor: yellow"][/TD]
[TD="class: xl121, bgcolor: yellow"][/TD]
[TD="class: xl104, bgcolor: yellow"][/TD]
[TD="class: xl104, bgcolor: yellow"][/TD]
[TD="class: xl70, bgcolor: yellow"][/TD]
[TD="class: xl71, bgcolor: yellow"]Awaiting disposition
[/TD]
[TD="class: xl67, bgcolor: yellow"]ON Assembly
[/TD]
[TD="class: xl68, bgcolor: yellow"][/TD]
[/TR]
</TBODY>[/TABLE]
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
try this in your Server sheet module.
(right-click Server tab, select View Code)

Code: 
Option Explicit
Dim sOrig As String

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Columns("B")) Is Nothing Then
        Dim shSrc As Worksheet, shDes As Worksheet
        Dim nextRow As Long
        Set shSrc = Sheets("Server")
        Set shDes = Sheets("Servers Pulled")
        If UCase(Target) = "SHIPPED" Then
            Application.EnableEvents = False
            If MsgBox("Move " & Target.Offset(, -1) & " to Servers Pulled?", vbYesNo, "Confirm Shipped") = vbYes Then
                nextRow = shDes.Cells(Rows.Count, "A").End(xlUp).Row + 1
                shSrc.Cells(Target.Row, "A").Copy shDes.Cells(nextRow, "A")
                shSrc.Cells(Target.Row, "I").Resize(1, 23).Copy shDes.Cells(nextRow, "B")
                Target.EntireRow.Delete
            Else
                Target = sOrig
            End If
            Application.EnableEvents = True
        End If
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Application.Intersect(Target, Columns("B")) Is Nothing Then sOrig = Target
End Sub
 
Upvote 0
Hello Warship,

Thank you for responding and for your help. I inserted the code into the Server module and tried running it. I get an error message - Run-time error '9' Subscript out of range. I get this same error on whatever I choose from the drop-down in Column B.

When I hit debug, the the code appears and a line is highlighted in yellow. - Set scScr = Sheets ("Server")

Again, thanks for your reply and your help.

r/ m0aje
 
Upvote 0
m0aje said:
When I hit debug, the the code appears and a line is highlighted in yellow. - Set scScr = Sheets ("Server")
Click to expand...

Unless you changed the variables (or maybe just a type-o in your reply) the line should be: Set shSrc = Sheets("Server")

But in any case, error '9' is telling you it can't find the "Server" sheet, double check your sheet name spelling on its tab.
 
Upvote 0
Hello Warship,
The problem was that the tabs were in CAPS vice upper and lower case. When I changed the tabs to exactly like they are labeled in the code, it worked! THANK YOU!!!!
One question though, if a record from "Server" is moved to "Servers Pulled" by mistake, can the record be put back? The "undo" icon in excel is grayed out.
Again, thank you for your help. I am most grateful!!

r/ m0aje
 
Upvote 0
Excel's user Undo will not work for VBA actions.
Undo ability for VBA actions has to be coded in VBA.

Perhaps for your situation, the ability to transfer back & forth between "SERVERS" & "SERVERS PULLED" will suffice.
The advantage would be that you could Undo any server regardless of when it was originally moved.
A possible dis-advantage may be that the "Undo" will transfer the server to the bottom of the sheet.
A sort routine could be added if a particular order would help.

I also defaulted the "No" button on the "Confirmation" question, which makes transferring a more deliberate act.

I don't know the of Status choices you use.
For this code I used "Not Shipped" - you'll need to change that to what ever Status indicator you are actually using.

Place this code in your ThisWorkbook module.
This code handles both sheets so delete the previous sheet mod code.
Code: 
Option Explicit
Dim sOrig As String

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "SERVERS" Or Sh.Name = "SERVERS PULLED" Then
        If Not Application.Intersect(Target, Columns("B")) Is Nothing And Selection.Cells.Count = 1 Then
            Dim shSrc As Worksheet, shDes As Worksheet
            Dim nextRow As Long, sKeyWd As String
            Set shSrc = Sh
            Select Case Sh.Name
                Case "SERVERS"
                    Set shDes = Sheets("SERVERS PULLED")
                    sKeyWd = "SHIPPED"
                Case "SERVERS PULLED"
                    Set shDes = Sheets("SERVERS")
                    sKeyWd = "NOT SHIPPED"
            End Select
            If UCase(Target) = sKeyWd Then
                Application.EnableEvents = False
                If MsgBox("Move " & Target.Offset(, -1) & " to " & shDes.Name & "?", vbYesNo + vbDefaultButton2, "Confirm") = vbYes Then
                    nextRow = shDes.Cells(Rows.Count, "A").End(xlUp).Row + 1
                    Cells(Target.Row, 1).Resize(1, 31).Copy shDes.Cells(nextRow, "A")
                    Target.EntireRow.Delete
                Else
                    Target = sOrig
                End If
                Application.EnableEvents = True
            End If
        End If
    End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "SERVERS" Or Sh.Name = "SERVERS PULLED" Then
        If Not Application.Intersect(Target, Columns("B")) Is Nothing And Selection.Cells.Count = 1 Then sOrig = Target
    End If
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name = "SERVERS" Or Sh.Name = "SERVERS PULLED" Then
        If Not Application.Intersect(ActiveCell, Columns("B")) Is Nothing And Selection.Cells.Count = 1 Then sOrig = ActiveCell
    End If
End Sub

Private Sub Workbook_Open()
    If ActiveSheet.Name = "SERVERS" Or ActiveSheet.Name = "SERVERS PULLED" Then
        If Not Application.Intersect(ActiveCell, Columns("B")) Is Nothing And Selection.Cells.Count = 1 Then sOrig = ActiveCell
    End If
End Sub
 
Last edited:
Upvote 0
Hello again Warship,

Thank you again for helping me with this spreadsheet. The latest code you wrote for me works when I select "SHIPPED in column B from the "SERVERS" sheet. It does move the record to the "SERVERS PULLED". However it moves the entire line. In the previous code you wrote for me, it deleted the column B (shipping status) and the columns that pertained to "testing" which was columns C thru H. The strings of code you used to do this I believe was:
nextRow = shDes.Cells(Rows.Count, "A").End(xlUp).Row + 1
shSrc.Cells(Target.Row, "A").Copy shDes.Cells(nextRow, "A")
shSrc.Cells(Target.Row, "I").Resize(1, 23).Copy shDes.Cells(nextRow, "B")
Somehow that code took column I and shifted everything to the left and closed the hole.
This worked beautifully as it deleted columns B (shipping status which is a drop down list), columns C thru H, and shifted everything else to the left, where column B now became the "Part No". This was exactly what I wanted in the "Servers Pulled" sheet. The "Servers Pulled" sheet goes from column A and ends at column X. I spent several hours trying to insert and manipulate those lines from the previous code into the latest one, but I couldn't get it to work right. The shipping status in the "Servers Pulled" sheet is in column X where I enter a shipping date, pending, delayed, or CANCELLED. Column X would be where I would need to undo the original move if I chose "CANCELLED". If it moves it back to the bottom, it is no big deal. If the data information is the original, I can move it back to the proper location within the "Servers" sheet where I need it if necessary.

THANK YOU again for your assistance with my spreadsheet Warship. I am most grateful for your help.

Kind regards,

m0aje
 
Upvote 0
You're welcome.

The reason I kept the whole line was to accomodate an undo.
The unwanted columns will have to be stored somewhere to be recalled for the undo.
The last was the easy way.


Would simply hiding the unwanted columns suffice?
Select Entire unwanted columns|right-click|hide.
The code will still wk as is
 
Upvote 0
Hello again Warship,

Yes, the hiding of the columns will be OK. I changed the "NOT SHIPPED" in the code to "CANCELLED". And it works! The only issue I had was that in column "B" (Shipping status") I am using the drop-down list to select various status situations. When I choose "SHIPPED" it prompts me, I click OK, and it's done. However, also copies the drop-down list over to the "Servers Pulled" and I lose my choices. I have to get rid of the drop down list as I am unable to use it if I have to move the record back. I guess that can be typed in manually. I am very please with how it is working. Again, THANK YOU for your time and patience with me and helping me with this sheet. I have another sheet on a smaller scale, that I am going play around to see if I can manipulate this code to make the smaller one work too. It's the same type of sheet, just with laptops and workstations.
I appreciate all you did for me Warship. You guys on this forum are the greatest. THANKS!

r/ m0aje
 
Upvote 0
You're welcome and
Thank you for your kind feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
238
SBExcel123
S
larinda4
  • Question Question
VBA: Work Around for moving from one sheet to another based on cell value?
Replies
0
Views
590
larinda4
larinda4
K
  • Solved
Check & Update existing rows of data to fill in the blanks
Replies
1
Views
621
Kemidan2014
K
D
  • Question Question
Moving Data (entire rows) from one Sheet to Another Based on Criteria
Replies
4
Views
1K
DOC1021
D
H
  • Question Question
VBA Code required - copy data from one sheet to another
2
Replies
10
Views
1K
hrayani
H

Forum statistics

Threads
1,224,590
Messages
6,179,753
Members
452,940
Latest member
rootytrip

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top