VBA help to copy data from one spreadsheet to another and transpose it

[eacounts]

I have a spreadsheet that is attached to a database. The first sheet has the data that is to be copied into a second spreadsheet. The values in columns C through I have to be copied and repeated in the second spreadsheet repeatedly. The data in these columns are to be used to store the data into the database from the second spreadsheet. This application is a payroll type application and the data should be copied for each Employee Number,Business Area,Paytype,CostCenter,Company,Department and profit center. The other columns have the monthly calculations per employee. Each section has different accounts that is pulled based on properties for each employee and business area combination. So for the other columns of data I have to copy the differing account numbers and the monthly data that is to be sent back into the database by employee. So the monthly data and the accounts need to be copied into the second spreadsheet and transposed from columns to rows. So that the monthly data and the accounts are lined up with the repeating rows of the Employee Number,Business Area,Paytype,CostCenter,Company,Department and profit center. The complexity comes in because the data in the first spreadsheet could have multiple thousands of rows and columns of data and I need to figure out how to determine the last row of data in each section that is copied from the first spreadsheet into the second one.

So each new section of monthly data that has to be copied and transposed I need to know the last row that was copied for the previous section into the second spreadsheet.

Example: For the Dental/Medical section in the first spreadsheet starts at column (C10:I29) which is (Employee Number,Business Area,Paytype,CostCenter,Company,Department and profit center). This is the first section that has to be copied into the second spreadsheet starting at column (A5:G54). Once that is copied over I need the data in columns (AW10:BO29) copied over to the second spreadsheet. But I need the data for the FIN Account column (AW10:AW29) in the first spreadsheet to be pasted to Columns (H5:H54) in the second spreadsheet and the monthly data for dental/medical columns (AW10:BO29) in the first spreadsheet pasted into columns (J5:AA54) in the second spreadsheet. Once that is done I need the data for the next section to be copied from the first sheet into the second sheet the same way along with the repeat of the data in columns (C10:I29) the Employee Number,Business Area,Paytype,CostCenter,Company,Department and profit center. But I need to figure out dynamically what the last row of data that was copied from sheet 1 into sheet 2 so that the next section of data will not copy over the first section of data. This has to occur for multiple employees and accounts so there could be thousands of rows of data to be copied and moved to the second spreadsheet. Any help would be appreciated.

[mrice]

Try this macro.

Please Login or Register  to view this content.

You will need to create a sheet named Output.