Macro to transpose rows into columns and paste to specific columns in another sheet

Hi, I would appreciate some help with creating a macro that will handle a couple of different variables including:

• varying number of rows
• varying row criteria
• paste in specific columns in another sheet
• keep collective order number information together during the transpose + column paste process (very important). The collective data is separated by varying number of empty rows. In the attached example, there are 4 collective data sets. Collective data sets will increase daily when form is refreshed
• data that will be updated daily and in turn require ability to update macro as needed.

Currently, I have a file (using queries & connections) that combines all of the data from files saved into a specific folder when I click on the "refresh all" option. This works well, but now I would like to transpose sets of data from rows into columns, and then have that pasted in another sheet according to specific columns.

The data in C:D are what need to be manipulated. Because the data in columns f and g can be varying number of rows, the spacing between collective order numbers can also have varying number of empty rows.


The file attached shows an example of what I currently have in sheet 1 and sheet 2 shows what I would like the macro to give me.

On sheet 2, the columns needed are in row 1.

Thanks, but I get an error when running this macro:
run-time error '1004':
application-define or object-defined error

Despite the error, it does manage to transpose the data on a second sheet but it is only transposing 1 of the 4 collective data sets and they're not in the specified columns. in the example shown, the "collective data" is c3:d16, c30:d43, c57:d77,and c91:d:112.

the number of rows and quantity of collective data sets varies as the data is updated. if you notice, depending on the order and item number, certain criteria appear (1-5, A-L, or AA-MM).

This is a bit confusing, but let me know if you need further clarification

Originally Posted by Mumps1

The macro assumes the data starts on row 1. What row does your data start?

the data begins on row 4.

try

Please Login or Register  to view this content.

Originally Posted by Mumps1

Try:

Please Login or Register  to view this content.

This works! THANK YOU