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.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

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
13,886
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
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,123,337
Messages
5,601,026
Members
414,422
Latest member
acegreen

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