PDA

View Full Version : Can't get code to loop through worksheets



whistler
01-23-2017, 08:41 AM
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
01-23-2017, 10:59 AM
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
01-23-2017, 02:44 PM
Cross posted
https://www.mrexcel.com/forum/excel-questions/987158-cant-loop-through-multiple-sheets.html

whistler
01-23-2017, 06:28 PM
"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
01-23-2017, 08:14 PM
solved at the above link ^^^