Copy and Paste Error between Application instances

[malik641]

Awesome! So far, so good. I'll do some testing before I mark this thread solved.

Here's what I ended up with:

Public Sub ImportDataNow(ByVal xlApp As Excel.Application, ByVal xlWB As Excel.Workbook, _
ByVal xlSh As Excel.Worksheet, ByVal xlDataRange As Excel.Range)
' This will perform the actual importing process to a new workbook.
Dim wbData As Excel.Workbook
Dim shData As Excel.Worksheet
' Create new workbook to import data
Set wbData = Application.Workbooks.Add
Set shData = wbData.Sheets("Sheet1")
shData.Name = "Scheduling Import - " & MonthName(Month(Now()), False)
' Temporarily create new sheet in Production workbook and copy and paste data there
xlWB.Worksheets(xlSh.Name).Range(xlDataRange.Address).Copy
With xlWB.Sheets.Add
    .Paste
    .UsedRange.Copy
    ' .PasteSpecial (3) = "Microsoft Excel 8.0 Format"
    wbData.Worksheets(shData.Name).Range("A1").PasteSpecial (3)
    .Delete
End With
Call ClearClipBoard
End Sub

Now it's time to format and edit my data

Thanks Tom for the help, much appreciated