Help with find, select and paste

Matt1989

New Member
Joined
Aug 14, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Evening all,

I am pretty new to VBA and I could really do with some help with a spread sheet.

I need to copy data from multiple rows (I only need up to offset 0,10 from these rows) when a cell in the range A1:A30000 in the sheet entitled DATA equals the value of TextBox7, to another sheet entitled REVIEW.

The sheet entitled REVIEW is same structure (column headings) as the sheet entitled DATA.

When the data is pasted on the sheet REVIEW I need the first row to be entered on the next empty row then each consecutive row entered after that to be entered on an empty row.

I can’t seem to get any variation of VBA code to do this.

Any help will be greatly received.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
(I only need up to offset 0,10 from these rows)
Not sure what this means. When you say "up to" it indicates the copy range could be less than 10 rows, so what determines the number of rows to be copied?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,477
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Assuming that it is an ActiveX TextBox and you mean 10 columns across by the Offset (so 11 columns in total) then maybe try...

VBA Code:
Sub Filterit()
    Application.ScreenUpdating = False
    
    With Sheets("Data").Range("A1:A30000")
    
        .AutoFilter Field:=1, Criteria1:=Sheets("DATA").OLEObjects("TextBox7").Object.Text
        
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1, 11).SpecialCells(12).Copy _
        Sheets("REVIEW").Range("A" & Rows.Count).End(xlUp).Offset(1)
        On Error GoTo 0
        .AutoFilter
    
    End With
    
    Application.ScreenUpdating = True
End Sub
 

Matt1989

New Member
Joined
Aug 14, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Assuming that it is an ActiveX TextBox and you mean 10 columns across by the Offset (so 11 columns in total) then maybe try...

VBA Code:
Sub Filterit()
    Application.ScreenUpdating = False
   
    With Sheets("Data").Range("A1:A30000")
   
        .AutoFilter Field:=1, Criteria1:=Sheets("DATA").OLEObjects("TextBox7").Object.Text
       
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1, 11).SpecialCells(12).Copy _
        Sheets("REVIEW").Range("A" & Rows.Count).End(xlUp).Offset(1)
        On Error GoTo 0
        .AutoFilter
   
    End With
   
    Application.ScreenUpdating = True
End Sub

Thanks a lot this worked
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,477
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You're welcome
 
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,167,817
Messages
5,855,806
Members
431,765
Latest member
RedleoUK

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