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 i
failing.But it's working fine when i remove that argument .Can any bod
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 Functio

--
xcelio
-----------------------------------------------------------------------
xcelion's Profile: http://www.excelforum.com/member.php...fo&userid=1628
View this thread: http://www.excelforum.com/showthread.php?threadid=31926

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

Hi Dave :) ,

That problem is solved .Thanks for fixing the code .But one more
problem when iam pasting as link i am loosing the format of cells
pasted compared to the source cells.Also zeroes are coming for the
empty cellsHow should i paste as link with out loosing format of source
cells.What iam doing now is first i will do the normal paste .Then
overwrite it with paste as link option.Then formmating remains same
But the problem with zeroes still remains.Is there any aother way
out?.can you advice

Thanks
Xcelion


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

Pasting a link is like building a formula:
=sheet2!c1
You could revise the formula:
=if(sheet2!c1="","",sheet2!c1)

In code, something like this:

Dim fCell As Range
Dim tCell As Range

Set tCell = Worksheets("sheet1").Range("a1")
Set fCell = Worksheets("sheet2").Range("c1")

With tCell
.Formula = "=if(" & fCell.Address(external:=True) & "="""",""""," _
& fCell.Address(external:=True) & ")"
End With


But formulas don't bring formats with them.

You could change the code to copy|paste special|formats and then build your own
formula (, though. But if you change the format in the original data, the
formats won't automatically update.





xcelion wrote:

Hi Dave :) ,

That problem is solved .Thanks for fixing the code .But one more
problem when iam pasting as link i am loosing the format of cells
pasted compared to the source cells.Also zeroes are coming for the
empty cellsHow should i paste as link with out loosing format of source
cells.What iam doing now is first i will do the normal paste .Then
overwrite it with paste as link option.Then formmating remains same
But the problem with zeroes still remains.Is there any aother way
out?.can you advice

Thanks
Xcelion

--
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