Hi everyone,
I am not a coder, but I am really interested in vba; i recently created a file which is essentially a list of names, each name in a new row and next that payment amount for each month. Now i found a code searching through your forums which essentialy creates a sheet for each name on the list of master and names as per the name in the list. I now have two questions:
1) When i add a new row (a new customer) to the master list, do i need to run the vba again? Can i add something to the code that just tells it "if a tab already exists move on, if not create new tab"?
2) Each new sheet is a detailed payment card showing exact date of payment, who received the payment and the amount. Can i insert a code that tells my file to automatically show the range (B3 : K3) on the named sheet next to that specific name in the master list? This would show me at any given time who has payed their subscription and who hasn;t.
The code i am already running is this:
Sub SheetsFromTemplate()
Dim wsMASTER As Worksheet, wsTEMP As Worksheet, wasVISIBLE As Boolean
Dim shNAMES As Range, Nm As Range
With ThisWorkbook 'keep focus in this workbook
Set wsTEMP = .Sheets("Template") 'sheet to be copied
wasVISIBLE = (wsTEMP.Visible = xlSheetVisible) 'check if it's hidden or not
If Not wasVISIBLE Then wsTEMP.Visible = xlSheetVisible 'make it visible
Set wsMASTER = .Sheets("Master") 'sheet with names
'range to find names to be checked
Set shNAMES = wsMASTER.Range("A2:A" & Rows.Count).SpecialCells(xlConstants) 'or xlFormulas
Application.ScreenUpdating = False 'speed up macro
For Each Nm In shNAMES 'check one name at a time
If Not Evaluate("ISREF('" & CStr(Nm.Text) & "'!A1)") Then 'if sheet does not exist...
wsTEMP.Copy After:=.Sheets(.Sheets.Count) '...create it from template
ActiveSheet.Name = CStr(Nm.Text) '...rename it
End If
Next Nm
wsMASTER.Activate 'return to the master sheet
If Not wasVISIBLE Then wsTEMP.Visible = xlSheetHidden 'hide the template if necessary
Application.ScreenUpdating = True 'update screen one time at the end
End With
MsgBox "All sheets created"
End Sub
I am not a coder, but I am really interested in vba; i recently created a file which is essentially a list of names, each name in a new row and next that payment amount for each month. Now i found a code searching through your forums which essentialy creates a sheet for each name on the list of master and names as per the name in the list. I now have two questions:
1) When i add a new row (a new customer) to the master list, do i need to run the vba again? Can i add something to the code that just tells it "if a tab already exists move on, if not create new tab"?
2) Each new sheet is a detailed payment card showing exact date of payment, who received the payment and the amount. Can i insert a code that tells my file to automatically show the range (B3 : K3) on the named sheet next to that specific name in the master list? This would show me at any given time who has payed their subscription and who hasn;t.
The code i am already running is this:
Sub SheetsFromTemplate()
Dim wsMASTER As Worksheet, wsTEMP As Worksheet, wasVISIBLE As Boolean
Dim shNAMES As Range, Nm As Range
With ThisWorkbook 'keep focus in this workbook
Set wsTEMP = .Sheets("Template") 'sheet to be copied
wasVISIBLE = (wsTEMP.Visible = xlSheetVisible) 'check if it's hidden or not
If Not wasVISIBLE Then wsTEMP.Visible = xlSheetVisible 'make it visible
Set wsMASTER = .Sheets("Master") 'sheet with names
'range to find names to be checked
Set shNAMES = wsMASTER.Range("A2:A" & Rows.Count).SpecialCells(xlConstants) 'or xlFormulas
Application.ScreenUpdating = False 'speed up macro
For Each Nm In shNAMES 'check one name at a time
If Not Evaluate("ISREF('" & CStr(Nm.Text) & "'!A1)") Then 'if sheet does not exist...
wsTEMP.Copy After:=.Sheets(.Sheets.Count) '...create it from template
ActiveSheet.Name = CStr(Nm.Text) '...rename it
End If
Next Nm
wsMASTER.Activate 'return to the master sheet
If Not wasVISIBLE Then wsTEMP.Visible = xlSheetHidden 'hide the template if necessary
Application.ScreenUpdating = True 'update screen one time at the end
End With
MsgBox "All sheets created"
End Sub
