VBA move selection to different sheet and insert based on selection

MrGeek1

New Member
Joined
Jul 24, 2019
Messages
29
Hi,

I am working on below to move a selection of rows in sheet x given the string "Dead" to a next sheet (sheet y) and subsequently insert the amount of rows selected in that next sheet. Can anyone give an example how to show this in the code?

Many thanks!


Sub Moverow()


Dim i As Long


For i = 2 To 100
If Worksheets("x").Range("B" & i) = "Dead" Then
Range(Range("B" & i), Range("B" & i).End(xlToRight)).Select
Selection.Copy
Worksheets("y").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
End If
Next
End Sub
 
Last edited:

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,394
Office Version
  1. 365
Platform
  1. Windows
Do you have any data in col A, or is it completely blank?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,394
Office Version
  1. 365
Platform
  1. Windows
In that case try
Rich (BB code):
Sub MrGeek1()
   With Sheets("x")
      .Range("B1").AutoFilter 1, "Dead"
      .AutoFilter.Range.Offset(1).EntireRow.Copy Sheets("y").Range("B" & Rows.count).End(xlUp).Offset(1, -1)
      .AutoFilterMode = False
   End With
End Sub
 

MrGeek1

New Member
Joined
Jul 24, 2019
Messages
29

ADVERTISEMENT

Thanks, that works. As a next step do you also know how the copied selection rows can be erased from sheet x after pasting it in sheet y?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,394
Office Version
  1. 365
Platform
  1. Windows
Like
Rich (BB code):
Sub MrGeek1()
   With Sheets("x")
      .Range("B1").AutoFilter 1, "Dead"
      .AutoFilter.Range.Offset(1).EntireRow.Copy Sheets("y").Range("B" & Rows.count).End(xlUp).Offset(1, -1)
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub
 

MrGeek1

New Member
Joined
Jul 24, 2019
Messages
29
I notice that my macro button jumps from sheet x to the specified sheet y after pressing the macro button. Do you have any clue why that is? Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,394
Office Version
  1. 365
Platform
  1. Windows
Probably because it's not in row 1
 

Watch MrExcel Video

Forum statistics

Threads
1,109,085
Messages
5,526,762
Members
409,720
Latest member
Antwain

This Week's Hot Topics

Top