VBA Help - unless something else works

[Shauntethemt]

Hey y'all!

I am in charge of the payment system of 50 people and am using excel. All of the information is on a master spreadsheet and I want to autopopulate individual sheets that I can print out and give to each individual. For reference, on my Master sheet, A1 is my header "name" and B1:AU1 has all of the names of the individuals.

A2:A47 have all of the categories of things they are paying for. The cost of the items are in cells 2-47, but in columns B-AU. (I hope I worded that correctly)

I need a formula that not only creates individual sheets based on the names in B1:AU1, but i also need it to auto-populate the information for each individual. Currently, I have a code to create the individual sheets. But when I have to populate the information I have to go to each page and use the formula =Master!(letter)1 and then double click on the corner to make it populate everything in that respective column. There has to be an easier way.

here is the code that I have from a previous workbook that helped me understand how to copy the sheets but not how to populate the rest of the information

Option Explicit
Sub CopySheet()

Dim wsM As Worksheet
Dim wsNames As Range, c As Range

Set wsM = Sheets("Amirah")
Set wsNames = Sheets("Full Team").Range("A2:A" & Rows.Count).SpecialCells(2)

Application.ScreenUpdating = False

For Each c In wsNames
If Not Evaluate("ISREF('" & c.Value & "'!A2)") Then
wsM.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = c.Value
End If
Next c

wsM.Select
Application.ScreenUpdating = True

End Sub

 

[Joe4]

Welcome to the Board!

I think it would be extremely beneficial for us to see a sample of your data and expected output (just be sure to replace any "sensitive" data with dummy data).

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I am thinking that the new FILTER function available in Excel 365 may be able to be used here.
See here: FILTER function - Microsoft Support

 

[Shauntethemt]

Welcome to the Board!

I think it would be extremely beneficial for us to see a sample of your data and expected output (just be sure to replace any "sensitive" data with dummy data).

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I am thinking that the new FILTER function available in Excel 365 may be able to be used here.
See here: FILTER function - Microsoft Support

Unfortunately, the XL2BB file can't be downloaded onto my computer because this a work computer - and there are the firewalls around it. However, I have attached a screenshot that may help.

Again, I have all the information in the master spreadsheet. I want to make individual price sheets (new sheets) based on the name of the individual, formulated like the individual template and filled in, so all i have to do is press print and each worksheet comes out for the individual.

 

[Joe4]

Can you show us what one of these individual sheets looks like?

 

[Shauntethemt]

Can you show us what one of these individual sheets looks like?

Here is a copy of an individual template. When you look at cell B1 - it shows the formula that I used to link the individual sheet to the master.

 

[Joe4]

Is there any way you can upload the file to a file sharing site and provide us a link, since you cannot use the XL2BB tool, and I don't really want to type all that stuff out to set-up your situation so I can play around with it.

Note: What you are describing is a relational database relationship, where databases programs like Microsoft Access and SQL would handle it so much better, as long as you set it up properly and normalized your tables! But I am guessing either those tools are not available to you, or you are not familiar with them.

 

[Shauntethemt]

Is there any way you can upload the file to a file sharing site and provide us a link, since you cannot use the XL2BB tool, and I don't really want to type all that stuff out to set-up your situation so I can play around with it.

Note: What you are describing is a relational database relationship, where databases programs like Microsoft Access and SQL would handle it so much better, as long as you set it up properly and normalized your tables! But I am guessing either those tools are not available to you, or you are not familiar with them.

You're absolutely right, I am not familiar with either of those tools BUT I am interested in it - assuming they are easier to manage and do what I need them to do. Thanks for sending me down that rabbit hole lol. Below, I uploaded the file to dropbox. Hopefully the way I setup the file is easy to navigate.

24-25 Cheer Fees File

 

[Joe4]

Is there some reason why after row 7, the values in column A do not match between your "Master" sheet and "Individual template" sheet?

 

[Shauntethemt]

Is there some reason why after row 7, the values in column A do not match between your "Master" sheet and "Individual template" sheet?

View attachment 114092

That's weird, on my version it does. Maybe it's a previous version from me thinking it through of how I wanted it to look? I saved it again and reuploaded it.

24-25 Cheer Fees File

 

[Shauntethemt]

You're absolutely right, I am not familiar with either of those tools BUT I am interested in it - assuming they are easier to manage and do what I need them to do. Thanks for sending me down that rabbit hole lol. Below, I uploaded the file to dropbox. Hopefully the way I setup the file is easy to navigate.

24-25 Cheer Fees File

After doing a bit more research, from my understanding a relational database relationship means that while I have a master sheet, the individual template changes based on the Unique key (in this case the customer's name in the B1 cell) and pulls all the data related to that particular customer onto the one template sheet?

If I understood that correctly, that is exactly what I am looking for. So not only does it pull the information per customer based on B1, the additive is that there is also an individual template sheet labeled for each customer with their data on it.