Excel/vba help

[woods2007]

Hi,

In Excel 2010 I have a macro button on my Quick access tool bar to run the below code:


Sub DataPrep()

Dim finalrow As Long

finalrow = Cells(Rows.Count, 1).End(xlUp).Row

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "DataRef"
Range("A2").Select
ActiveWindow.SplitRow = 1
ActiveWindow.FreezePanes = True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"

Range("a2").AutoFill Destination:=Range(Cells(2, 1), Cells(finalrow, 1)), Type:=xlFillSeries


Rows("1:1").Font.Bold = True

'show finalrow

[a2].Select

Selection.End(xlDown).Select

'remove any blank workSheets

Dim Ws As Worksheet
For Each Ws In Worksheets
If WorksheetFunction.CountA(Ws.Cells) = 0 Then
Application.DisplayAlerts = False
Ws.Delete
Application.DisplayAlerts = True
End If
Next Ws

End Sub

However it won't run the code saying that the 'Macro is not available' but I can still run the macro if i go through 'view macros'

can anyone provide a solution?

Many thanks

 

[krkilsdonk]

I believe that the problem is that the button needs to specify the full path name to the macro, including the file it is located in.