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?
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?