Hello everyone.
I've been reading the forum for a few days before asking any help. I'm a total VBA n00b.
it would be great if someone could help me.
I've used some code from several posts to meet my needs, but I now need an integrated approach.
Here's what I need to do:
Here's the code I have so far. Thank you all
I've been reading the forum for a few days before asking any help. I'm a total VBA n00b.
it would be great if someone could help me.
I've used some code from several posts to meet my needs, but I now need an integrated approach.
Here's what I need to do:
- I have a master sheet with suppliers name on B column
- I have a template sheet which is replicated whenever I add a new supplier on the master sheet and the new sheet is created with that supplier's name on the press of a button.
- After the creation of the new sheet I would like to also copy the supliers name to a cell in the created sheet, let's say on B1.
- On the created sheet I would like to manually fill some cells and get those cells copied over to the Master list.
Here's the code I have so far. Thank you all
Code:
Sub SheetsFromTemplate()
Dim wsMASTER As Worksheet, wsTEMP As Worksheet, wasVISIBLE As Boolean
Dim shNAMES As Range, Nm As Range
With ThisWorkbook 'mantém foco nesta folha
Set wsTEMP = .Sheets("Template") 'folha a ser copiada
wasVISIBLE = (wsTEMP.Visible = xlSheetVisible) 'verificar se está oculta
If Not wasVISIBLE Then wsTEMP.Visible = xlSheetVisible 'torná-la visível
Set wsMASTER = .Sheets("Master") 'folha mestre
'intervalo a verificar
Set shNAMES = wsMASTER.Range("B3:B" & Rows.Count).SpecialCells(xlConstants) 'or xlFormulas
Application.ScreenUpdating = False 'acelerar macro
For Each Nm In shNAMES 'verificar um nome de cada vez
If Not Evaluate("ISREF('" & CStr(Nm.Text) & "'!A1)") Then 'se a folha ainda não existe...
wsTEMP.Copy After:=.Sheets(.Sheets.Count) '...criar a partir do template
ActiveSheet.Name = CStr(Nm.Text) '...renomear a folha
End If
Next Nm
wsMASTER.Activate 'regressar à folha mestre
If Not wasVISIBLE Then wsTEMP.Visible = xlSheetHidden 'esconder o template se necessário
Application.ScreenUpdating = True 'atualizar o ecrã
End With
MsgBox "New supplier added"
End Sub