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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
(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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

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