Macro taking rows of data and creating unique sheets

[jharvey87]

Hi everyone,

I've been searching the internet for assistance with a macro I'm looking to create. I'm not having much luck, so if anyone can point me in the right direction, it would be greatly appreciated.

I receive a spreadsheet each month with transaction information for each card holder. I'm looking for a macro that can take this information, create a spreadsheet and paste the information a certain way.

For example, if the first listed name is John Doe, i would want it to create a sheet with his name, and title it with his name. It would then keep the same format and simply place the listed charged on that spreadsheet.

Another option is to just to manually create the sheets and have the macro update the new transaction (basically we replace Sheet 1 within the file).

TransactionsCapture.PNG

As you can see from the attached image, the information is listed and each charge is combined with the individuals name in every row in Column B.

How can I have these transactions transferred over to the sheet, "Doe, John" with the refresh of a macro?

NewSheetCapture.PNG

The sheet would look similar to above? Is this possible without writing some crazy macro? Also allowing the entries to expand without messing up the sum function and signature field?

Any help would be greatly appreciated.

[mikeTRON]

Are you familiar with pivot tables? this would be rather simple with a pivot table.

[jharvey87]

I'm somewhat familiar with Pivot tables. I can give a try. I thought the Pivot table would mess up if I were to simply Delete Sheet 1 and added a new one. I thought even with the same name it would still error out. Also, does it matter that sometimes someone could have 2 rows and other times they could have 50? Depending on the amount of charges they have, everyones data can be found in different rows each month.

[mikeTRON]

Post a sample workbook and I can give you a starting point and pointers.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[mikeTRON]

I'm somewhat familiar with Pivot tables. I can give a try. I thought the Pivot table would mess up if I were to simply Delete Sheet 1 and added a new one. I thought even with the same name it would still error out. Also, does it matter that sometimes someone could have 2 rows and other times they could have 50? Depending on the amount of charges they have, everyones data can be found in different rows each month.

Long story short I would crab the ENTIRE column for all the columns with data, so when the data is changed or updated and has more rows, you can simple REFRESH ALL pivots and they all have the most up to date data.

[jharvey87]

Long story short I would crab the ENTIRE column for all the columns with data, so when the data is changed or updated and has more rows, you can simple REFRESH ALL pivots and they all have the most up to date data.

Please use this link to download an example:
https://www.dropbox.com/s/q8lv189k95...mple.xlsm?dl=0

Basically Sheet 1 stores all the transactions for all individuals. Column B has their name which is listed in every row that applies to them. When I use the Macro, I would like it to take all transactions associated with them and copy them to their individuals sheets (which we then send to them individually).

I just need an easy way to copy this data into the individual sheets in the format we need each month. I also need it to sum below as shown in the two examples.

If this would require a complicated macro then it would probably not be worth it. I was hoping there would be some easy way to search column B for their name, then something similar to a vlookup to pull that data in the listed order and place in the columns we want.

[jharvey87]

Long story short I would crab the ENTIRE column for all the columns with data, so when the data is changed or updated and has more rows, you can simple REFRESH ALL pivots and they all have the most up to date data.

I can't get a pivot table to work with this. I'm wanting titles of charge, etc. Not just a sum of charges. Each merchant name changes. I can't get it to work.

[mikeTRON]

Pivot works fine.
Click in any pivot table and hit REFRESH ALL under the analyze tab. This will bring in the mikeTRON district stuff...


Let me know if you have any questions.

Also if it were me I would purge the SUM and BLANK rows as well as the header rows, then your pivot table will be even easier to manage, as you see I filtered out data with (BLANK) in district, since it was nonsensical for the two people.

[mikeTRON]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[jharvey87]

I was actually hoping someone might show me how I can still do this with a macro. The pivot table doesn't quite format like I was hoping.

[vcoolio]

Hello JHarvey,

Are you still scratching your head on this one? MikeTron's pivot idea does work but,if you still don't have a solution and if you would still prefer some vba coding, then let us know.

Cheerio,
vcoolio.

[jharvey87]

I was hoping it could be done via VBA if someone has the type to assist. The pivot table doesn't quite look like how I wanted.

[vcoolio]

Hello JH,

I'll have a look at it tonight for you.

Cheerio,
vcoolio.

[vcoolio]

Hello JH,

Following are the two codes I believe will do the task for you. One code calls the other:-

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The first one, as its title suggests, creates sheets from a template sheet for each individual named in the Master sheet. The second one collects the data and transfers it to each individual sheet.

You'll notice that I've renamed the source sheet "Master". I've attached your sample file with the codes implemented. Click on the button at the top of the source sheet to see it all work. If this source sheet is deleted every month, then we could move the button to the Template sheet.
When data is added or deleted from the source sheet, clicking on the button will refresh all the individual sheets also. We could ,if you prefer, put a refresh button on each individual sheet. Each individual sheet is named after each individual in the Master sheet and this name is also placed in cell B3 of each individual sheet. There are two formulae in cells B10 and B11 (of each individual sheet) which place the individuals' first and last names where required. The formulae use cell B3 as the source of information. The formulae are as follows:-

First name:-

Please Login or Register  to view this content.

Last name:-

Please Login or Register  to view this content.

I don't see where the Transaction Number (Column A of each individual sheet) comes from. Perhaps you could enlighten us on this.

If it is more convenient for you to leave the source sheet name as Sheet1, then we can change this in the code to suit.

I hope that this helps.

Cheerio,
vcoolio.