0

I need to create a Macro on excel that perform a quiet strange action but let me explain: I need to search in a specific column if there's some value (they are eventually all number) and for each value that the macro finds I need to create a copy of an already existing sheets named "template" with the same value that the macro found.

For example, if the macro find the value "3, 9, and 12" I need it to create the sheets "template3, template9, and template12, that must be a copy of "template" that have only a different name based on what the macro founds.

I'm starting from here

   Dim FoundArea As Range
   Set FoundArea = Range("I17:I85").Find()

I've dected the column in which I want to search for the values, but now I don't know how to tell the program the rest of the actions to perform. Any suggest? Thanks

UPDATE: I've done it, the first part at least, here's my code if can interest someone

Sub AggiornaColliTEST()

'Crea un nuovo foglio di nome "Collo"+valore (se non esiste già) per ogni valore trovato nella colonna i a partire dalla riga 18

Dim sheets_count As Integer
Dim sheet_name As String
Dim j As Integer

sheet_count = Range("I1:I850").Rows.Count
For j = 1 To sheet_count

    sheet_name = Sheets("LISTA MATERIALE").Range("I18:I850").Cells(j, 1).Value
    
    If SheetCheck(sheet_name) = False And sheet_name <> "" Then
    Worksheets.Add().Name = "Collo" & sheet_name
    Worksheets("Collo" & sheet_name).Move After:=Worksheets(Worksheets.Count)
    
    ElseIf SheetCheck(sheet_name) = True Then
    
    Else
    End If
    
 Next j
    
    
Dim xTable As ListObject
Dim xSheet As Worksheet
Dim i As Integer

For i = 3 To ThisWorkbook.Sheets.Count
        'For Each xSheet In Worksheets
        For Each xTable In Sheets(i).ListObjects

            Sheets(2).ListObjects("Tabella10").Range.Copy _
                Destination:=Sheets(i).Range("A17")

            'Sheets(i).Range("D1").Value = xTable.Name
            Sheets(i).ListObjects(xTable.Name).Range.AutoFilter Field:=9, Criteria1:= _
            i - 2
        Next xTable
Next

End Sub

here's the custom function used (found this on the internet and work as well)

Function SheetCheck(sheet_name As String) As Boolean
'Questa funzione verifica se un foglio con un dato nome esiste già oppure no, ritorna un valore di tipo bool (true o false)


Dim ws As Worksheet
SheetCheck = False
 
For Each ws In ThisWorkbook.Worksheets

    If ws.Name = "Collo" & sheet_name Then
        SheetCheck = True
    End If
Next
 
End Function

The only part I need now is how to copy my template in every new sheet that the Macro create but I'm not so far from the solution. Thanks everybody for helping.

Silvio Di Palma
  • 1
  • 1
  • 1
    Create an `Array` of the numbers to find, and loop through it, trying to find each number. – BigBen Mar 23 '22 at 14:01
  • Thanks , I'm going to create a dynamic array of the found values but how to find and store them in it? – Silvio Di Palma Mar 23 '22 at 14:13
  • First you can use the macro recorder to get an idea how something would look like as VBA code. Just make sure you read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and re-work the recorded code as it is usually full of `.Select` statements. • Also reading the manual of the methods you use will help you to understand how they work. For example the [Range.Find method](https://learn.microsoft.com/en-us/office/vba/api/excel.range.find). – Pᴇʜ Mar 23 '22 at 14:20
  • Thanks. The thing I'm stucked with now is how to use the .Find method in order to search for generic integer value. Every example that I can found search for some specific value. If I want to use .find to find all the generic integer in the Area I decided how can I do? PS I'm not sure if .find is available to do it, maybe I should look for something else, is there a method that permit to search for a generic integer? Thanks all for the support – Silvio Di Palma Mar 23 '22 at 14:44
  • Sounds like you'll just need to loop through and check. – Rdster Mar 23 '22 at 14:47
  • You mean a loop that search for number 1 the first time, number 2 the second time and so on? Maybe I can do something similar. The problem is that I don't know the maximum number that I will be able to find in the table but I think going until 999 is enough. – Silvio Di Palma Mar 23 '22 at 14:54
  • nope guys I'm not able to do it, any help with the code would be very useful to me. I'm trying to create a dynamic array where I want to store the found value but without any success. – Silvio Di Palma Mar 23 '22 at 15:49
  • No, loop through, see what is a number and process from there, no need to do multiple loops. – Rdster Mar 23 '22 at 16:36
  • I'm going check for that, thanks man – Silvio Di Palma Mar 24 '22 at 07:45

0 Answers0