Copy and paste data below a specific header

[londa_vba]

Hello
I am in need of a macro that searches for the text "Position : B1" and copies all the data below it from A to J to another sheet "B1" until it reaches a blank row (the number of rows in each position will vary).
I will need another macro that searches for "Position : C1" and copies that to sheet "C1" as well.
As always any help is greatly appreciated.

 

[Joe4]

Are these values you are looking for in column A?
Can you post a small sample of your data so we can see how it is all structured?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[londa_vba]

Are these values you are looking for in column A?
Can you post a small sample of your data so we can see how it is all structured?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual post

 

[londa_vba]

Are these values you are looking for in column A?
Can you post a small sample of your data so we can see how it is all structured?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I have tried in the past and have been unable to add this to my work computer.

 

[Joe4]

Then can you at least post an image of the beginning of your data?
And at least answer the first question I asked?

 

[londa_vba]

Then can you at least post an image of the beginning of your data?
And at least answer the first question I asked?

Yes; the both headers are merged cells from column A-J.
the format of this imported data will always be the same. the quantity of items under each header will differ each time.

 

[Joe4]

Ughh... merged cells are just about the worst feature of Excel and can cause all sorts of issues with things like VBA, sorting, etc.
They should really never be used. Many VBA programmers will not touch any data that contains merged cells.
It could prove to be extremely challenging to program against that kind of data.

Do you have any control over the format of the data?
If so, can you set it up without using merged cells?

Note that when dealing with merged columns, you can get the exact same visual effect without all the issues merged cells causes by using the "Center Across Selection" formatting option, as shown here: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis
Unfortunately, there is no similar formatting option for merged rows.

 

[londa_vba]

Ughh... merged cells are just about the worst feature of Excel and can cause all sorts of issues with things like VBA, sorting, etc.
They should really never be used. Many VBA programmers will not touch any data that contains merged cells.
It could prove to be extremely challenging to program against that kind of data.

Do you have any control over the format of the data?
If so, can you set it up without using merged cells?

Note that when dealing with merged columns, you can get the exact same visual effect without all the issues merged cells causes by using the "Center Across Selection" formatting option, as shown here: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis
Unfortunately, there is no similar formatting option for merged rows.

This data is generated by an instrument. We copy and paste it directly off an instrument straight into a sheet in our workbook. I doubt I would be allowed to mess with that instrument/computer's settings. The work-around has been using a ton of if statements and lookup functions. I was hoping there was an easier solution. Either way I appreciate you taking a look at this issue for me.

 

[Joe4]

This data is generated by an instrument. We copy and paste it directly off an instrument straight into a sheet in our workbook. I doubt I would be allowed to mess with that instrument/computer's settings. The work-around has been using a ton of if statements and lookup functions. I was hoping there was an easier solution. Either way I appreciate you taking a look at this issue for me.

Since the formatting is pretty wacky, I think we would need a copy of it, so we can be sure that any VBA code we are writing will actually work on the data file, as it really exists.
Would you be able to post a copy up to a file sharing site and provide a link to it here for us to download?
Just be sure that you "dummy up" (remove or replace) any sort of sensitive/private/personal data first.