Can't get code to loop through worksheets

[whistler]

Hey,


I am analysing data. Taking 3 columns, and putting them into a new workbook. Saving, then closing. the code works fine, but I can't get it to loop through worksheets.
I've tried several of the suggested methods and none of them have worked on my system. I tested them using: "range("A1").value = "banana"", to print in each sheet but the code will only run once and stop.
I can get
Worksheets(ActiveSheet.Index + 1).Select
to work but then the code doesn't loop.




What I have from a macro recording is:

Sub workingnoloop()
    
Dim Path As String
Dim filename As String
Path = "C:\Users\Whistler\Desktop\"
filename = Range("A2")
'
    Columns("H:I").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("L1").Select
    ActiveSheet.Paste
    Range("K1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Time"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("K3").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-6]-R[-1]C[-6]"
    Range("K3").Select
    Selection.AutoFill Destination:=Range("K3:K2997")
    Range("K3:K2997").Select
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "%missing"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTBLANK(RC[-2]:R[2999]C[-2])/COUNT(RC[-3]:R[2999]C[-3])*100"
    Columns("K:N").Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close

Each workbook has between 27-31 sheets in them to loop through. Any help here would be great.




The data is compiled with a PIVOT table, and I need one colum to be sorted from smallest to largest, and have the numbers in other colums follow their trend. I couldn't seem to code that sort function in though, since it's lock to the pivot. Does anyone know how to include that in the above code too?

[Leith Ross]

Hello Whistler,

What is the name of the sheet you are copying the data from?

Will each worksheet have the new file name in "A1" ?

[mikerickson]

Cross posted
https://www.mrexcel.com/forum/excel-...le-sheets.html

[whistler]

"A2" contains a participant number which is unique to that worksheet. I am using it to save the file as the participant number.

column E is a recording timestamp (330495, 330498, 330501), but needs to be turned into a time value (0,3,6). it's placed in the pivot table from highest to lowest, but i need it lowest to highest, I've been doing this by hand.
Column H and I are X and Y coordinates. they don't need to be changed.
the %missing part is meant to count all the cells and tell me what % is missing. There is usually a lot missing.
then I need all that in a new workbook, auto save it based off their participant number, and then do the next worksheet from the original workbook. I have about 1600 of these to do. I did the first 200 by hand. T_T

What I have currently does the job fine, but doesn't cycle worksheets.

[whistler]

solved at the above link ^^^