Find a Specific Column Title, Copy the corresponding (varying data) and Paste

**sperry2565** · 07-03-2013, 11:48 AM

Hopefully the title is specific enough....

So I'm trying to write code to search through a file I've loaded, which will never be the same in terms of data (width, length, whats actually in it). I want it to find the title of a column of data, copy the title all the way to the last row of data for that column and then paste it into another sheet of the workbook. I've tried many different ways of searching, copying, pasting but I can never get it to go past just finding the title and copying/pasting only the title into the next spreadsheet. In the following code I've specified a column to pull the data from but I am unsure if it will always be the same column. This is my nth time of rewriting multiple different codes, unfortunately I haven't saved any of my previous attempts (you live and learn) so this one is just basic....anyone have any ideas?

Please Login or Register  to view this content.

**codeslizer** · 07-04-2013, 09:10 AM

Hi Sperry,

"One way to do that is simply find the name of the column. Select till bottom, copy it, then navigate to the sheet which you wish to paste and paste it there." - I know that you're aware of how to do it, but I actually gave you the instructions of how to make it work in VBA. There is a "Record Macro" button under Developer tab in the ribbon. If you can't find, simply go to options and make it view from there. After hitting it, simply do the same instructions as told, and the macro will be recorded. Just make right changes to make the recorded macro a consistent macro.

Well, for your query. Below is the code. Please Note, the code is not tested, but that's how I do these operations in my reports. I considered the text you're trying to search, "Text_To_Find" is hard-coded. Also the cell in sheet2 from which you want to paste the values is starting from B2.

Note: If you want you can actually give a cell reference for the Search Text - telling the macro to take the text from the specified cell in the sheet to make the search. Then you just have to make sure you put the right value in "After:=" parameter so that it doesn't take the same cell into consideration while searching - if its in the same sheet.

Please Login or Register  to view this content.

**Norie** · 07-04-2013, 09:15 AM

What's the name of the title you want to find?

Which row will it be in?

**sperry2565** · 07-08-2013, 11:25 AM

Sorry for the delayed response. Thank you! This works perfectly. I keep forgetting about the macro button and pulling examples from there. I'm still slowly learning how to navigate this part of excel/VB. Thank you again though! I've been able to adapt this to a few things in my code.