PureNovic:(
09-10-2015, 07:07 AM
Hi guys,
Very new here and very new to VBA so all help is incredibly appreciated.
I've been tasked with a little and probably rather simple project of running some Toolpak analytics on some data.
I have installed the add-ins and i have been able to get this to run on my source data rather easily.
Sub RunStatistics()
Sheets("Source").Select
Columns("A:A").Select
Application.Run "ATPVBAEN.XLAM!Descr", ActiveSheet.Range("$A:$A"), _
"Results", "C", True, True, 2, 2
End Sub
However as i need to turn this into a workbook for complete novices i need to do some tidy up in the code to make sure that sheets are being deleted etc. So if the Results sheet exists i want it deleted (causes error if not as is being used for the output of ATPVBAEN.XLAM)
The following code gets the error " Compile Error: Type Mismatch" on the function header Sub Cleanup() but i dont know why as im not passing anything in??
Sub Cleanup()
Dim sheet As Worksheet
Dim row_min As Integer
Dim row_max As Integer
Dim col_min As Integer
Dim col_max As Integer
' Select the used range.
Set sheet = "Results"
Range("A1:ZZ200").Select
Selection.Copy
Sheets("Desc_Results").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'now delete Results tab so can be used again
Sheets("Desc_Results").Delete
End Sub
Thanks everyone so much in advance, i appreciate the elementary nature of this question!
Very new here and very new to VBA so all help is incredibly appreciated.
I've been tasked with a little and probably rather simple project of running some Toolpak analytics on some data.
I have installed the add-ins and i have been able to get this to run on my source data rather easily.
Sub RunStatistics()
Sheets("Source").Select
Columns("A:A").Select
Application.Run "ATPVBAEN.XLAM!Descr", ActiveSheet.Range("$A:$A"), _
"Results", "C", True, True, 2, 2
End Sub
However as i need to turn this into a workbook for complete novices i need to do some tidy up in the code to make sure that sheets are being deleted etc. So if the Results sheet exists i want it deleted (causes error if not as is being used for the output of ATPVBAEN.XLAM)
The following code gets the error " Compile Error: Type Mismatch" on the function header Sub Cleanup() but i dont know why as im not passing anything in??
Sub Cleanup()
Dim sheet As Worksheet
Dim row_min As Integer
Dim row_max As Integer
Dim col_min As Integer
Dim col_max As Integer
' Select the used range.
Set sheet = "Results"
Range("A1:ZZ200").Select
Selection.Copy
Sheets("Desc_Results").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'now delete Results tab so can be used again
Sheets("Desc_Results").Delete
End Sub
Thanks everyone so much in advance, i appreciate the elementary nature of this question!