Hi guys. I am trying to fuly automate a cut and paste operation. Basically the data is dragged off the net, formatted using a macro in word then copied from word and pasted into Excel.
From there I have an Excel macro called "FinalStep" which converts the data into a table format.
the trouble is I cannot get the macro to run at all. The code is posted below.
Sub LastStepTest()
'Macro purpose: Use the application.run method to execute
'a macro without arguments from another workbook
Dim PathToFile As String, _
NameOfFile As String, _
wbTarget As Workbook, _
CloseIt As Boolean
'Set file name and location. You will need to update this info!
NameOfFile = "Personal.x ls"
PathToFile = "H:\profile\App lication Data\Microsoft\ Excel\XLSTART\"
'Macro purpose: Use the application.run method to execute
'a macro without arguments from another workbook
'Attempt to set the target workbook to a variable. If an error is
'generated, then the workbook is not open, so open it
On Error Resume Next
Set wbTarget = Workbooks(NameO fFile)
If Err.Number <> 0 Then
'Open the workbook
Err.Clear
Set wbTarget = Workbooks.Open( PathToFile & "\" & NameOfFile)
CloseIt = True
End If
'Check and make sure workbook was opened
If Err.Number = 1004 Then
MsgBox "Sorry, but the file you specified does not exist!" _
& vbNewLine & PathToFile & "\" & NameOfFile
Exit Sub
End If
On Error GoTo 0
'Run the macro! (You will need to update "MacroName" to the
'name of the macro you wish to run)
Application.Run (wbTarget.Name & "!FinalStepTest ")
If CloseIt = True Then
'If the target workbook was opened by the macro, close it
wbTarget.Close savechanges:=Fa lse
Else
'If the target workbook was already open, reactivate this workbook
ThisWorkbook.Ac tivate
End If
End Sub
I realise that not all of this code is required but as I am new to this I am not sure what to cut out. Either way the error I am getting is "run time error Unable to run the specified macro" and this line is highlighted in yellow: Application.Run (wbTarget.Name & "!FinalStepTest ")
Thanks for your help, I have been on this all day!
Michael
I know that more is required of the Application