Extracting data from one sheet to another, not in same rows or columns

[mindrumk424]

I have now been searching all afternoon, and have not found an answer to what I need accomplished. It is quite complicated and I am sure the code will be a pain. Any assistance would be greatly appreciated!

I have a command button that needs to extract data from the worksheet that it is in, and paste it to another worksheet in the same workbook. However, the data is scattered.
I have information in rows 45-83 that has data that needs extraction. However most of the time not all the rows are utilized. So how do I extract data from just the rows that are being used? Also, not all the data in that row is needed, just columns A,B,F,H,J,N and P. And when extracted they have to be put into a different order in the other worksheet. To add to the complication of this, there is a certain range of cells that correspond to those rows, but are not in those rows. R89 corresponds to row 45 and down the line.

Is there any possible way to accomplish this? I have attached the workbook. Tab Zone 1 is where the information is kept. Tab PA Data is where the information needs to be sent to.
In the Zone 1, you will notice that rows are labeled with HIDEROW. This is what is used when the row is not going to be in use, there is a button to hide those rows. Essentially i will need the data that is above the HIDEROW, and below the Row45.

Thanks in advance for all the help in this!! test.xlsx

[Richard Buttrey]

This task would be very simple if your data was laid out better. You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

You need to ensure that your data has a single row of unique column labels at the top of every column. In addition don't merge columns like you have done with B:E. Merged columns are always a pain in the backside and invariably cause problems with other aspects of Excel. Avoid them at all cost.

Once you have a simple 2 dimensional table of columns with labels and rows then it's simple matter to analyse it. Either with a Pivot Table or using Data Filtering. With the latter you can repeat the column labels on another sheet in whichever order you want, and then use Data Advanced Filter to extract the records from your data that match any criteria that you define in a separate criteria range.

If you want to set out your data as described above and let us know what the selection criteria is then I'm sure someone will be along to help you.

[mindrumk424]

This task would be very simple if your data was laid out better. You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

You need to ensure that your data has a single row of unique column labels at the top of every column. In addition don't merge columns like you have done with B:E. Merged columns are always a pain in the backside and invariably cause problems with other aspects of Excel. Avoid them at all cost.

Once you have a simple 2 dimensional table of columns with labels and rows then it's simple matter to analyse it. Either with a Pivot Table or using Data Filtering. With the latter you can repeat the column labels on another sheet in whichever order you want, and then use Data Advanced Filter to extract the records from your data that match any criteria that you define in a separate criteria range.

If you want to set out your data as described above and let us know what the selection criteria is then I'm sure someone will be along to help you.

I would love to but I am in a position where I really cant. The data I am capturing is coming from something I cannot change, given that I receive it from a company that works for us. Also, the data is going into that spreadsheet so it can be queryed into a database, and the way the information needs to be set up is also unchangeable. Think of me has a middle man for data capturing. I do not have control of either end. I am having to input all that data in manually, and was hoping I could simplify it a little bit.
I however see your point, and if I can change things I will try.
Maybe build a simple data capture worksheet for them that can then be copied to the more fancy one that they already have? Then I can capture data from their simple one, close to being in the same format as what I am trying to send off?