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]

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.

Yes, that is where relational database software handles this kind of stuff so much better. I usually use Microsoft Access for this kind of thing.

Basically, in Access you would have one table that has all your data (Master), and then you would create a Parameter Query, which has your "template", and you just enter the user you want to see the data for, and it returns it dynamically, real-time! So you don't need need a separate query for each person, just one that asks for the user at the time you run it. Much more efficient and simple.

 

[Shauntethemt]

A

Yes, that is where relational database software handles this kind of stuff so much better. I usually use Microsoft Access for this kind of thing.

Basically, in Access you would have one table that has all your data (Master), and then you would create a Parameter Query, which has your "template", and you just enter the user you want to see the data for, and it returns it dynamically, real-time! So you don't need need a separate query for each person, just one that asks for the user at the time you run it. Much more efficient and simple.

Although Access might be easier and/or better suited, is there no way to do this in Excel? Since everything is already in excel, it might be easier for me to just run the code and then work with access a little later.

 

[Joe4]

A

Although Access might be easier and/or better suited, is there no way to do this in Excel? Since everything is already in excel, it might be easier for me to just run the code and then work with access a little later.

There is, though it is much clunkier, inefficient, and cumbersome.

I will see if I can take a look on it on my home computer (where I downloaded the files) later today. We had a storm last night, and my computer went a little berserk. I am hoping it was just a power surge, and not a computer crash!

 

[Joe4]

Just thought before we come up with VBA code. If your goal is to print out each person's data for each other, could you simply just hide all the columns except column A and the one for the user you want to see, and print that? We could come up with VBA code that could loop through each user and do that for each one.

If that is not an option, and you need separate tabs for each person, do you need them to have formulas linking back to the Master sheet, or can we just copy the hard-coded values of each one?
It won't make it dynamic should you change any of the data, but it should help the performance of the workbook. When you have a lot of worksheets with a lot of formulas, that is where things can really start to bog down performance.

 

[Shauntethemt]

Just thought before we come up with VBA code. If your goal is to print out each person's data for each other, could you simply just hide all the columns except column A and the one for the user you want to see, and print that? We could come up with VBA code that could loop through each user and do that for each one.

If that is not an option, and you need separate tabs for each person, do you need them to have formulas linking back to the Master sheet, or can we just copy the hard-coded values of each one?
It won't make it dynamic should you change any of the data, but it should help the performance of the workbook. When you have a lot of worksheets with a lot of formulas, that is where things can really start to bog down performance.

I would prefer a tab for each athlete mainly because I can't mentally imagine hiding all the other columns and printing. In my head, (and I did it with last year's team) if I print the workbook, it would include all the pages for the individuals and they can be stored in a hard copy form as well as digitally. The formula would link back to the master sheet in that if a person (let's say Amani) is B1 - then everything in the B column would be on her page with the formula =Master!B1 all the way to =Master!B48.

Although a little more cumbersome, even if the VBA code just copied all the pages and labeled them with the names, i could go in and put the formula that links it back to the master sheet and drag the corner of the box down so that all the information in that column on the master sheet is on the individual sheet. In the end, I want to be able to update the master sheet and it automatically updates the individual templates. I'm just not sure if the VBA code can do both - or maybe I need to run 2 codes - or maybe it's just the one. I really need to look more into Microsoft Access for next season lol.

 

[Joe4]

OK, give this VBA code a shot:

Sub MyCopyMacro()

    Dim mws As Worksheet, tws As Worksheet, nws As Worksheet
    Dim lc As Long, c As Long
    Dim nm As String
    Dim ad As String, cl As String
    Dim vArr
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

'   Set master and template worksheets
    Set mws = Sheets("Master")
    Set tws = Sheets("Individual template")
    
'   Find last column with data on row 1 of master sheet
    lc = mws.Cells(1, mws.Columns.Count).End(xlToLeft).Column

'   Loop through all columns on master sheet starting with column B
    For c = 2 To lc
'       Get current column letter
        ad = Cells(1, c).Address
        vArr = Split(ad, "$")
        cl = vArr(1)
'       Get person's name
        nm = mws.Cells(1, c)
'       Add new sheet at end
        Sheets.Add After:=Sheets(Sheets.Count)
'       Capture new worksheet
        Set nws = ActiveSheet
'       Name sheet
        nws.Name = nm
'       Copy template over to new sheet
        tws.Select
        Cells.Copy
        nws.Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
'       Populate cell B1 with name
        nws.Range("B1").Value = nm
'       Replace column reference in formulas
        nws.Columns("B:B").Replace What:="Master!B", Replacement:="Master!" & cl, LookAt:= _
            xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Next c
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub

I think it should do all that you want.

 

[Shauntethemt]

OK, give this VBA code a shot:

Sub MyCopyMacro()

    Dim mws As Worksheet, tws As Worksheet, nws As Worksheet
    Dim lc As Long, c As Long
    Dim nm As String
    Dim ad As String, cl As String
    Dim vArr
   
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

'   Set master and template worksheets
    Set mws = Sheets("Master")
    Set tws = Sheets("Individual template")
   
'   Find last column with data on row 1 of master sheet
    lc = mws.Cells(1, mws.Columns.Count).End(xlToLeft).Column

'   Loop through all columns on master sheet starting with column B
    For c = 2 To lc
'       Get current column letter
        ad = Cells(1, c).Address
        vArr = Split(ad, "$")
        cl = vArr(1)
'       Get person's name
        nm = mws.Cells(1, c)
'       Add new sheet at end
        Sheets.Add After:=Sheets(Sheets.Count)
'       Capture new worksheet
        Set nws = ActiveSheet
'       Name sheet
        nws.Name = nm
'       Copy template over to new sheet
        tws.Select
        Cells.Copy
        nws.Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
'       Populate cell B1 with name
        nws.Range("B1").Value = nm
'       Replace column reference in formulas
        nws.Columns("B:B").Replace What:="Master!B", Replacement:="Master!" & cl, LookAt:= _
            xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Next c
   
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
   
    MsgBox "Macro complete!"
   
End Sub

I think it should do all that you want.

This is PERFECT! Thank you so much!

 

[Joe4]

You are welcome.
Glad I was able to help!