I am trying to paste some copied data from a group of cells on one sheet to a group of cells in another workbook. I keep getting the "Invalid procedure call or argument" error
I am working with names from the name manager in both workbooks
The error appears on the red area of code
Suggestions?
I am working with names from the name manager in both workbooks
The error appears on the red area of code
Suggestions?
Rich (BB code):
Sub SetupSheet()
'Alicia 7/31/2015: Based on code by Paul to copy data to setup sheets
'Alicia 8/4/2015: Code works to copy from tooling library to setup sheet
On Error GoTo Errorcatch
'data collection button
Dim cell As Range
Dim rownumber As Long
Dim Insert As Range
Dim Radius As Range
Dim Grade As Range
Dim ErrFlag As Integer
Dim strSheet As String
Dim Tools As Workbook
Set Insert = Range("Insert_Number")
Set Radius = Range("Radius")
Set Grade = Range("Grade")
Set Tools = ThisWorkbook
'Activates this workbook so module runs only in this workbook
If ActiveWorkbook.Name <> Tools.Name Then
Tools.Activate
End If
strSheet = ActiveSheet.Name
ErrFlag = 0 'code will run
'Inputbox
On Error Resume Next 'For "False" return from cancel on input box
Set cell = Application.InputBox("Select a cell in the Row of the tool you want to COPY", Title:="Copy to Setup Sheet", Default:=ActiveCell.Address, Type:=8)
If cell = "" Then
'Set a timed msg box for cancel
Dim wshMsgBox As IWshRuntimeLibrary.WshShell
Dim wshCallMsgBox As Long
Set wshMsgBox = New IWshRuntimeLibrary.WshShell
wshCallMsgBox = wshMsgBox.Popup(Text:="Canceling: Empty cell selection was made", secondstowait:=3, _
Title:="Canceling", Type:=vbOKOnly)
Exit Sub
End If
On Error GoTo 0
'counts row number based on selected cell
rownumber = cell.Row - 2 'save row number
'Sheet error handling
If ErrFlag = 1 Then
MsgBox ("Error: '" & strSheet & "'" & " sheet doesn't have this cut and paste option")
Exit Sub
End If
'Sets range to paste into
Windows("Milling-General 07-22-151").Activate
On Error Resume Next 'For "False" return from cancel on input box
Set DEST = Application.InputBox("Select a cell in the Row of the tool you want to PASTE", Title:="Copy to Setup Sheet", Default:=ActiveCell.Address, Type:=8)
If DEST <> "" Then
'Set a timed msg box for cancel
Windows("Milling-General 07-22-151").Activate
Dim wshMsgBoxD As IWshRuntimeLibrary.WshShell
Dim wshCallMsgBoxD As Long
Set wshMsgBoxD = New IWshRuntimeLibrary.WshShell
wshCallMsgBoxD = wshMsgBoxD.Popup(Text:="Canceling: Empty cell selection was made", secondstowait:=3, _
Title:="Canceling", Type:=vbOKOnly)
Exit Sub
End If
On Error GoTo 0
rownumberD = DEST.Row 'save row number
Dim userResponce As Range
Dim Desc As Range
Dim Mat As Range
Dim Tip As Range
Union(Application.WorksheetFunction.Index(Insert, rownumber), Application.WorksheetFunction.Index(Grade, rownumber), Application.WorksheetFunction.Index(Radius, rownumber)).Copy
Union(Application.WorksheetFunction.Index(Desc, rownumberD), Application.WorksheetFunction.Index(Mat, rownumberD), Application.WorksheetFunction.Index(Tip, rownumberD)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E12").Select
Errorcatch:
MsgBox Err.Description
End Sub