trouble using Union and Index, Invalid procedure call or argument

ams5852

New Member
Joined
Aug 4, 2015
Messages
7
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?

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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top