VBA to search one celule A1 and find inside all sheets

[ronald54457874]

Hello guys, my first post here haha, I hope you all invited well.?



Here's the thing I have a spreadsheet in which it registers values that are contained in cell A2, that is, it takes the value in that cell and creates a new spreadsheet and renames it using the name of cell A2. Understood so far right? So he took the name of cell A2, created the sheets and renamed it.



but what I ended up seeing is that it's complicated to make a condition...



If (Search all spreadsheets for cell A2 value to see if you already have a spreadsheets created with cell A2 name)

Found spreadsheet with that name?;

add in the last blank line new values copied previously, as this part is just a piece of code.



If you didn't find it (Create a new spreadsheet named cell A2)



As you can see I don't know how to program IF in vba, I'm pretty new.



I can paste the values to be filled in the lines where there is nothing. But I can't get the cell name on all the other sheets.

If you have doubts I'll post something else, I didn't find the need to create an example plan, if necessary I create and play here lol.



I just finished my Example sheets, I believe what I now need an example is to download and read the comments in the VBA I created.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Thanks

 

[Alex Blakenburg]

If you just need the loop to see if the worksheet name per Cadastro A2 exists, then this might get you started.

Sub Cadastro()
    Dim controlSht As Worksheet
    Dim newSheet As Worksheet
    
    Set controlSht = Worksheets("Cadastro")
    On Error Resume Next
    Set newSheet = Worksheets(controlSht.Range("A2").Value)
    If newSheet Is Nothing Then
            Sheets("PLANMESTRE").Copy After:=Sheets(ThisWorkbook.Worksheets.Count)
            Set newSheet = ActiveSheet
            newSheet.Name = controlSht.Range("A2")
    End If
    On Error GoTo 0
    
    ' You action code goes here but all the references to Sheets("PLANMESTRE (2)") need to be replaced with newSheet
END SUB

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you enter a value in Range("A2") the script will run and make you a new sheet with the name you entered.
There is check to make sure sheet does not already exist.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/27/2021  1:51:58 AM  EDT
On Error GoTo M
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$2" Then
Dim ThisSheet As String
ThisSheet = ActiveSheet.Name
Dim ans As String
ans = Target.Value
Dim Sheetname As String
Sheetname = ans

If Evaluate("isref('" & Sheetname & "'!A1)") Then
    MsgBox "YesThe sheet named" & vbNewLine & Sheetname & vbNewLine & "Exist"
Else
MsgBox "No The sheet named" & vbNewLine & Sheetname & vbNewLine & "Does Not Exist"
Sheets.Add(After:=Sheets(Sheets.Count)).Name = ans
Sheets(ThisSheet).Activate
End If
End If
Exit Sub
M:
MsgBox "We had a problem" & vbNewLine & "You entered  " & Target.Value & vbNewLine & "Which is not a Proper sheet name"
End Sub

 

[ronald54457874]

I will try all methods, a moment

 

[ronald54457874]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you enter a value in Range("A2") the script will run and make you a new sheet with the name you entered.
There is check to make sure sheet does not already exist.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/27/2021  1:51:58 AM  EDT
On Error GoTo M
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$2" Then
Dim ThisSheet As String
ThisSheet = ActiveSheet.Name
Dim ans As String
ans = Target.Value
Dim Sheetname As String
Sheetname = ans

If Evaluate("isref('" & Sheetname & "'!A1)") Then
    MsgBox "YesThe sheet named" & vbNewLine & Sheetname & vbNewLine & "Exist"
Else
MsgBox "No The sheet named" & vbNewLine & Sheetname & vbNewLine & "Does Not Exist"
Sheets.Add(After:=Sheets(Sheets.Count)).Name = ans
Sheets(ThisSheet).Activate
End If
End If
Exit Sub
M:
MsgBox "We had a problem" & vbNewLine & "You entered  " & Target.Value & vbNewLine & "Which is not a Proper sheet name"
End Sub

Friends, you were amazing in the solution, you two helped me. However, I would like to mention something, if possible could you make a small edit like this?

The My Answer code, I can choose between Do, Re, Mi, Fa, Sol, La, Si so in the spreadsheet, right? when selecting in the data validation of cell D5 and choosing Dó, for example, if it is created it will give me an alert informing me that this spreadsheet has already been created, if it will not create it for me, but I need the following:

Select the scale I want, example Dó
And then copy the values of cells A4: E4 and then duplicate the PLANMESTRE spreadsheet because it is already formatted according to my preferences and finally paste, and where in the PLANMESTRE spreadsheet (2) "Dó" In cell A2: E2 only values without formatting and after, rename the PLANMESTRE (2) to Dó.

And if this worksheet has already been created, it will insert the same values from the Register worksheet into the already created worksheet, that is, in this example Dó, but it will create a new line below it by copying the formatting from the top row and inserting the previously copied values .

In theory it's easy, but doing so is very complicated...
But in advance thank you for your help!


I updated the spreadsheet to make it easier just go to the link below

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[ronald54457874]

If you just need the loop to see if the worksheet name per Cadastro A2 exists, then this might get you started.

Sub Cadastro()
    Dim controlSht As Worksheet
    Dim newSheet As Worksheet
   
    Set controlSht = Worksheets("Cadastro")
    On Error Resume Next
    Set newSheet = Worksheets(controlSht.Range("A2").Value)
    If newSheet Is Nothing Then
            Sheets("PLANMESTRE").Copy After:=Sheets(ThisWorkbook.Worksheets.Count)
            Set newSheet = ActiveSheet
            newSheet.Name = controlSht.Range("A2")
    End If
    On Error GoTo 0
   
    ' You action code goes here but all the references to Sheets("PLANMESTRE (2)") need to be replaced with newSheet
END SUB

Thanks friend, see my next comment

 

[My Aswer Is This]

Friends, you were amazing in the solution, you two helped me. However, I would like to mention something, if possible could you make a small edit like this?

The My Answer code, I can choose between Do, Re, Mi, Fa, Sol, La, Si so in the spreadsheet, right? when selecting in the data validation of cell D5 and choosing Dó, for example, if it is created it will give me an alert informing me that this spreadsheet has already been created, if it will not create it for me, but I need the following:

Select the scale I want, example Dó
And then copy the values of cells A4: E4 and then duplicate the PLANMESTRE spreadsheet because it is already formatted according to my preferences and finally paste, and where in the PLANMESTRE spreadsheet (2) "Dó" In cell A2: E2 only values without formatting and after, rename the PLANMESTRE (2) to Dó.

And if this worksheet has already been created, it will insert the same values from the Register worksheet into the already created worksheet, that is, in this example Dó, but it will create a new line below it by copying the formatting from the top row and inserting the previously copied values .

In theory it's easy, but doing so is very complicated...
But in advance thank you for your help!


I updated the spreadsheet to make it easier just go to the link below

Dropbox - File Deleted - Simplify your life

www.dropbox.com

I really do not understand any of this part of your new request.
Like: Select the scale I want, example Dó
Define scale
This is a lot more then your original request

 

[Alex Blakenburg]

Try this:

Sub Cadastro()
    Dim controlSht As Worksheet
    Dim outSht As Worksheet
    Dim outRow As Long
    Dim outScala As String
    
    Set controlSht = Worksheets("Registration")
    outScala = controlSht.Range("D5").Value
    
    On Error Resume Next
    Set outSht = Worksheets(outScala)
    On Error GoTo 0
    
    If outSht Is Nothing Then
        ' Create new worksheet
        Sheets("PLANMESTRE").Copy After:=Sheets(ThisWorkbook.Worksheets.Count)
        Set outSht = ActiveSheet
        outSht.Name = outScala
        outRow = 2
    Else
        ' Existing worksheet
        outRow = outSht.Cells(Rows.Count, "A").End(xlUp).Row + 1
        outSht.Range("A2:E2").Copy
        outSht.Range(Cells(outRow, "A"), Cells(outRow, "E")).PasteSpecial xlPasteFormats
    End If
    
    outSht.Range(Cells(outRow, "A"), Cells(outRow, "E")).Value = controlSht.Range("A4:E4").Value
    
End Sub

 

[ronald54457874]

Alex, antes de mais nada muito obrigado!

Seu código é 50% da minha necessidade, ele criou e renomeou a planilha agora, mas preciso que esta parte funcione:

Caso a planilha já exista, é necessário criar uma linha abaixo, copiando a formatação do PLANMESTRE na linha superior e inserindo os dados copiados do Cadastro na linha abaixo da planilha criada, ou seja, PLANMESTRE (2) que será renomeado de acordo com minha seleção na validação de dados.

Atualmente ele me deu um erro e apontou esta linha em amarelo

outSht.Range (Cells (outRow, "A"), Cells (outRow, "E")). PasteSpecial xlPasteFormats

Ele apenas selecionou toda a planilha criada e parou por aí destacando a linha que mencionei em amarelo. Atualmente com meus códigos cheguei aonde você foi, agora essa parte fica difícil identificar uma planilha criada e fazer o resto.

 

[ronald54457874]

Try this:

Sub Cadastro()
    Dim controlSht As Worksheet
    Dim outSht As Worksheet
    Dim outRow As Long
    Dim outScala As String
   
    Set controlSht = Worksheets("Registration")
    outScala = controlSht.Range("D5").Value
   
    On Error Resume Next
    Set outSht = Worksheets(outScala)
    On Error GoTo 0
   
    If outSht Is Nothing Then
        ' Create new worksheet
        Sheets("PLANMESTRE").Copy After:=Sheets(ThisWorkbook.Worksheets.Count)
        Set outSht = ActiveSheet
        outSht.Name = outScala
        outRow = 2
    Else
        ' Existing worksheet
        outRow = outSht.Cells(Rows.Count, "A").End(xlUp).Row + 1
        outSht.Range("A2:E2").Copy
        outSht.Range(Cells(outRow, "A"), Cells(outRow, "E")).PasteSpecial xlPasteFormats
    End If
   
    outSht.Range(Cells(outRow, "A"), Cells(outRow, "E")).Value = controlSht.Range("A4:E4").Value
   
End Sub

Alex, first of all thank you very much!

Your code is 50% of my need, he created and renamed the spreadsheet now, but I need this part to work:

If the spreadsheet already exists, it is necessary to create a line below, copying the PLANMESTRE formatting in the top line and inserting the data copied from the Registration in the line below the created spreadsheet, that is, PLANMESTRE (2) which will be renamed according to my selection in data validation.

Currently he gave me an error and pointed this line in yellow

outSht.Range(Cells(outRow, "A"), Cells(outRow, "E")). PasteSpecial xlPasteFormats

He just selected the entire created spreadsheet and stopped there highlighting the line I mentioned in yellow. Currently with my codes I got where you went, now this part is difficult to identify a spreadsheet created and do the rest.

Ignore the sentence above was in my original language