'Find value in a specific column and use it for for create new sheets
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
