If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below. If you don't like Google AdSense in the posts, register or log in above.

Macro to Copy and Paste Specific Columns into New Worksheet

The tasks I need to do in Excel today are just are a bit more taxing than usual. Hence, the flood of questions apologies.

I have started to write a macro to automate a component of the task I need to do repetitively for the next couple of weeks. So, it is an exercise for myself as well to learn more code for future tasks.

My source data is in the Base_Data worksheet within the Source.xlsx workbook. This worksheet has multiple columns of which some are not necessary for the analysis/macro. I would like the macro to copy the relevant data from this source data worksheet into new worksheets of Output.xlsx workbook based on the headers (these are manually copied and pasted in by myself). However, the issue is that the source data was one huge horrific spreadsheet with multiple entities in the same column. I need these entities to be split out into different worksheets in this new Output.xlsx workbook during this copying and pasting task. These entities are all in column B. For instance, there are entities such as Table, Chairs, Stairs etc. in the Base_Data worksheet. I need these to be split out into separate Table and Chairs and Stairs etc. worksheets in the Output.xlsx workbook.

The necessary columns for each worksheet are to copy and paste from the source column into the target column as follows:

I know this might be a difficult piece of code to write up (well, it is for me!!), and take some of your precious time, so lots of thanks for any help received but I am sure this will be of immense assistance for me in learning more VBA.

Re: Macro to Copy and Paste Specific Columns into New Worksheet

Thank you for your time spent on coding this up. Also I am really grateful that you have commented out the code with each step so that I could understand it; as some previous users just gave me the macro with no explanations. I have learnt lots of new syntax and methods to change the object properties from your code!

I have one further question that has now come up (but not to do with your code), which I dont really understand and gives me the subscript out of range error box popping up.

After this sorting macro completes, I need to copy and paste the newly sorted out data into a new workbook. So, at the end of your code (but before the End Sub), I added this: