I have a most illogical problem that I cannot wrap my head around.
This is what I want to do:
1. Create a list of unique values from a range of data -> No problem using Advanced filter and copy
2. Loop through the list of unique values and create as many copies of a specific sheet as there are items in the list.
#2 gives me problems. I have tried doing this both by copying an existing sheet in the workbook, and by adding a .xltx-file from a different lokaction. Both ways however give me the same illogical problem.
Everything works fine until I enter the loop (marked in red below). The code works fine a random number of iterations but eventually always gives me the following error messages:
"File not found: 'C:\Users\mikaelku\AppData\Local\Temp\VB31E2.tmp" - the .tmp-file always varies
and when I close the first error message I also get:
"Run-time error '1004: Application-defined or object-defined error"
What baffles me is that the loop sometimes manage to create 20 copies before the error messages arrive, and sometimes only 1 copy. Does anyone know why the universe is messing with me?
Public Sub Test() Dim i As Integer Dim WS As Worksheet i = 0 For Each WS In ThisWorkbook.Worksheets i = i + 1 Next WS Sheets("Gauge RnR data").Range("E:E").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Sheets("Sheet2").Range("A1"), Unique:=True ActiveWorkbook.Worksheets("Sheet2").Activate Range("A2").Select [COLOR=#FF0000]Do Until IsEmpty(ActiveCell) Sheets.Add Type:="C:\Users\mikaelku\Desktop\Test.xltx" Worksheets("Sheet2").Activate ActiveCell.Offset(1, 0).Select Loop[/COLOR] End Sub