VBA: Searching For Value in Column & Copy Row

[PB7]

Hello,

All I need to do in Excel VBA is:

- probably using For / Next and the If / Then structures
- loop through a block of data, and if VBA finds a number
- then copy /paste the contents of the specific row to another area

The number would be a integer in a single given column, like for example 4, representing the 4th batch of work, that I am processing, and so on. Have dabbled in VBA, but evidently don't know enough to do this one. Many thanks.

 

[texasalynn]

Welcome to MrExcel board...

so where would "another area" be?

 

[PB7]

Texasalynn,

Ideally, if looping finds, say 9 rows among 100 rows that meet the criteria, VBA copy/pastes the data from those 9 rows into another worksheet, forming 1 contiguous new block of data. Does that help?

Houston's a very cool town. Lived there, years ago.

 

[PB7]

Thank you for whatever help you can provide on this one.

 

[texasalynn]

sounds like if you are looking for a filter? Then you can copy the data of the filtered rows to "another area". But since you haven't given us the details of how the "filter" would be decided and where to place the data. That's all I can give until you give a little more details.

 

[PB7]

Yes, now that you mention it, that's what I am doing, though I didn't think of it that way initially.

VBA in this case would be essentially filtering on a column with integers. So I recorded a macro that filters on that column, and get this.

Range("A1:B11").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$B$11").AutoFilter Field:=2, Criteria1:="4"

Now, once this is done, can I select the filter results, and copy them to another area, like another worksheet tab?

Thanks for the very nice, responsive replies.