copy column data from multiple tabs onto specified tab, transpose, repeat

Hello,

I have been looking at this quite a bit and am hoping somebody can advise on how I can do the following (workbook attached).

1. The idea is that the macro will start on sheet1, look at column "Jon", copy the values down to the last active cell as well as the dates and paste special values and transpose them onto the "Jon" tab starting in B8 and B9. Each day all of the dates and values on the "Jon" tab should be overwritten with the data on sheets1 and 2, instead of it being cumulative.
2. The macro will then go to sheet2, look at column "Jon", copy the values to the last active as well as the dates and paste special values and transpose them onto the "Jon" tab starting in B18 and B19. Each day all of the dates and values on the "Jon" tab should be overwritten with the data on sheets1 and 2, instead of it being cumulative.
3. The macro would then save the "Jon" tab as a separate pdf in a specified location.
4. The process would then repeat for "Mike" and "Paul". Each day the number of columns can fluctuate, so it may be 3 one day (Jon, Mike, Paul) and 8 the next. The number of rows also may vary from day to day, and column to column.

I have attached a workbook that contains the data for the scenario above as well as the output (Jon, Mike, Paul tabs). Ideally there will not be new tabs that remain for Jon, Mike, Paul, just a default tab (e.g. "output" tab) that would receive the transposed data from the columns for Jon, Mike, Paul. I put all 3 in the workbook so you could see how each of them would output.

Any and all help on this one would greatly be appreciated!

Cheers!

jmp_sample.xlsm

For step 3, the saving to .pdf is not a necessity as I can manage to figure that out myself since I have done something a a bit similar to that before, but the rest would be a big help. Thanks.

Maybe:

Please Login or Register  to view this content.

Assumes the sheets are already created.

Thank you, this is more efficient than what I had come up with. Can you kindly explain what the ".End(3) (1)" means? I'm familiar with .End in the context of range and (xlUP) but I'm just trying to learn more.


Thanks

You're welcome. Glad to help out and thanks for the feedback. The .End(3)(1) selects the last filled cell in the column. The (3) means (xlUp) it's just a shorter version. (1) is an offset command which is equal to .Offset(0) in this case. (2) would equal .Offset(1) the more commonly used code. If you consider your problem solved, please mark this thread as solved.