VBA Find value, copy and paste values to set range

drewhx15

New Member
Joined
Jun 6, 2019
Messages
31
Hello VBA Masters,

Really hoping you can help me, I have been trying to get my code below to work and managed to get it working slightly.

I am trying to have the code check and find "North" in sheet1 column A.
Then it copies the row from columns B:E.
It then pastes the values of all rows with "North" to Sheet2 in a specified cell range e.g., "D5"

I hope this makes sense, if not please let me know.

The below code is moving it, but pushes it down to the bottom or half way down the page in column "A" rather than in where I need it "D5"

VBA Code:
Sub MoveData()
a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(x1Up).Row

For i = 2 To a

    If Worksheets("Sheet1").Cells(i, 3).Value = "North" Then

    Worksheets("Sheet1").Rows(i).Copy
    Worksheets("Sheet2").Activate
    b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(x1Up).Row
    Worksheets("Sheet2").Cells(b + 1, 1).Select
    ActiveSheet.Paste
    Worksheets("Sheet1").Activate
        
    End If

Next

Application.CutCopyMode = False

ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select

End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,596
Office Version
  1. 2013
Platform
  1. Windows
See if this works for you ...

VBA Code:
Public Sub drewhx15()
    Dim Rng As Range
    
    With ThisWorkbook.Worksheets("Sheet1")
        Set Rng = .Range("A1:E" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With
    With Rng
        .AutoFilter Field:=1, Criteria1:="=*North*"
        .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1).Copy ThisWorkbook.Worksheets("Sheet2").Range("D5")
        .Parent.AutoFilterMode = False
    End With
End Sub
 
Solution

drewhx15

New Member
Joined
Jun 6, 2019
Messages
31
GWteB, that has worked perfectly! Thank you so much! Hours, I tried to get that working. Thank you again and have a great evening.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,596
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for letting me know (y)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,032
Messages
5,767,739
Members
425,429
Latest member
MMMMMM

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
Top