Take another look at VBA's help:

Destination Optional Variant. A Range object that specifies where the Clipboard
contents should be pasted. If this argument is omitted, the current selection is
used. This argument can be specified only if the contents of the Clipboard can
be pasted into a range. If this argument is specified, the Link argument cannot
be used.

Link Optional Variant. True to establish a link to the source of the pasted
data. If this argument is specified, the Destination argument cannot be used.
The default value is False.


You get one or the other (destination or link--not both).

Something like this might work:

..CurrentRegion.Offset(3, 0).Resize(, 47).Copy
With wsCategoryTab
.Application.Goto .Range("b22")
.Paste Link:=True
End With

But don't you have to move down before you paste--else you're overwriting B22
each time.

Option Explicit

Function GenerateCategoryTabs1()
Dim wsCategoryTab As Worksheet
Dim wsDetailsSheet As Worksheet
Dim arrJobTitles As Variant
Dim rngData As Range
Dim DestCell As Range
Dim RngToCopy As Range
Dim i As Long

Set wsCategoryTab = ThisWorkbook.Worksheets("Job Categoryn")
Set wsDetailsSheet = ThisWorkbook.Worksheets("Details Tab")
arrJobTitles = Array("Title1", "Programmer/Developer")
Set rngData = wsDetailsSheet.Range("B3:AS14")

Application.ScreenUpdating = False

Set DestCell = wsCategoryTab.Range("b22")

For i = LBound(arrJobTitles) To UBound(arrJobTitles)
With rngData
.AutoFilter Field:=3, Criteria1:=arrJobTitles(i), _
VisibleDropDown:=False
Set RngToCopy = .CurrentRegion.Offset(3, 0).Resize(, 47)
End With

RngToCopy.Copy
With wsCategoryTab
.Application.Goto DestCell
.Paste Link:=True
End With
Set DestCell = DestCell.Offset(RngToCopy.Rows.Count)
Next i

rngData.AutoFilter

Application.ScreenUpdating = True

End Function


xcelion wrote:

Hi All,

I have a function like the one listed below.When iam using the Link:=
True in the Paste method (fourth statement from Last) the fuction is
failing.But it's working fine when i remove that argument .Can any body
help me on this

Thanks in advance


Function GenerateCategoryTabs1()
Dim wsCategoryTab As Worksheet
Dim wsDetailsSheet As Worksheet
Set wsCategoryTab = ThisWorkbook.Worksheets("Job Categoryn")
Set wsDetailsSheet = ThisWorkbook.Worksheets("Details Tab")
arrJobTitles = Array("Title1", "Programmer/Developer")
Set rngData = wsDetailsSheet.Range("B3:AS14")

Application.ScreenUpdating = False
n = 1

For i = 0 To UBound(arrJobTitles)

With rngData
AutoFilter Field:=3, Criteria1:=arrJobTitles(i),
VisibleDropDown:=False
CurrentRegion.Offset(3, 0).Resize(, 47).Copy
wsCategoryTab.Paste Destination:=wsCategoryTab.Range("B22"),
Link:=True

End With

Next i
rngData.AutoFilter

End Function

--
xcelion
------------------------------------------------------------------------
xcelion's Profile: http://www.excelforum.com/member.php...o&userid=16287
View this thread: http://www.excelforum.com/showthread...hreadid=319261

--

Dave Peterson