Hi - i am copying one worksheet to another and i notice that in its
destination only 255 characters of column D are being copied accross. In
some cases there are 700 characters that need to be copied accross. Help -
can this be resolved in any way?

The code that i am using is:
Dim mydata As String
'data location & range to copy
mydata = "='\\Macro\[Sedol_vlookup_reviews.xls]Paras'!$A$4:$D$300" '<<
change as required

'link to worksheet
With Worksheets("Reviews").Range("A4300") '<< change as required
.Formula = mydata

'convert formula to text
.Copy
.PasteSpecial Paste:=xlPasteValues
End With

Hi,
in the code given the selected range to be copied is from row 4 to 300, you
have to change the range there for example from D4800, if you see in your
code there is a paragraph saying change as required

"Withnails" wrote:

> Hi - i am copying one worksheet to another and i notice that in its
> destination only 255 characters of column D are being copied accross. In
> some cases there are 700 characters that need to be copied accross. Help -
> can this be resolved in any way?
>
> The code that i am using is:
> Dim mydata As String
> 'data location & range to copy
> mydata = "='\\Macro\[Sedol_vlookup_reviews.xls]Paras'!$A$4:$D$300" '<<
> change as required
>
> 'link to worksheet
> With Worksheets("Reviews").Range("A4300") '<< change as required
> .Formula = mydata
>
> 'convert formula to text
> .Copy
> .PasteSpecial Paste:=xlPasteValues
> End With

As already mentioned, you are copying a static range with your formulas – you
either have to change the range manually or update the code to expand it
dynamically. To do this though, you will need a “helper” cell in the target
workbook worksheet.

In your workbook Sedol_vlookup_reviews.xls place this formula:
=COUNTA(D) in range F1 of the worksheet you are copying. Save and close
the workbook. If Range F1 is being used on your worksheet, amend the code to
another unused Cell in the worksheet.

Now try this updated code & see if it does what you want.

Sub GetData()
Dim mydata As String
Dim lr As Variant

'your helper cell
lr = "='\\Macro\[Sedol_vlookup_reviews.xls]Paras'!$F$1"


'link to worksheet
With Worksheets("Reviews")

With .Range("F1")
.Formula = lr
'convert formula to text
.Value = .Value
lr = .Value
End With

'data location & range to copy
mydata = "='\\Macro\[Sedol_vlookup_reviews.xls]Paras'!$A$4:$D$" & lr

With .Range("A4" & lr)
.Formula = mydata

'convert formula to text
.Value = .Value

End With

‘remove helper value
.Range("F1").Value = ""

End With

--
jb


"Withnails" wrote:

> Hi - i am copying one worksheet to another and i notice that in its
> destination only 255 characters of column D are being copied accross. In
> some cases there are 700 characters that need to be copied accross. Help -
> can this be resolved in any way?
>
> The code that i am using is:
> Dim mydata As String
> 'data location & range to copy
> mydata = "='\\Macro\[Sedol_vlookup_reviews.xls]Paras'!$A$4:$D$300" '<<
> change as required
>
> 'link to worksheet
> With Worksheets("Reviews").Range("A4300") '<< change as required
> .Formula = mydata
>
> 'convert formula to text
> .Copy
> .PasteSpecial Paste:=xlPasteValues
> End With

Private Sub TryThisInstead()
Dim mydata As Variant
'data location & range to copy
mydata =
Workbooks("'\\Macro\[Sedol_vlookup_reviews.xls]").Worksheets("Paras").Range("$A$4:$D$300").Value
Worksheets("Reviews").Range("A4300").Value = mydata '<< change as
required
End Sub

Regards

Steve Dalton


"Withnails" <(E-Mail Removed)> wrote in message
news:C41E3A82-8DD9-49FD-8F5E-(E-Mail Removed)...
> Hi - i am copying one worksheet to another and i notice that in its
> destination only 255 characters of column D are being copied accross. In
> some cases there are 700 characters that need to be copied accross.
> Help -
> can this be resolved in any way?
>
> The code that i am using is:
> Dim mydata As String
> 'data location & range to copy
> mydata = "='\\Macro\[Sedol_vlookup_reviews.xls]Paras'!$A$4:$D$300" '<<
> change as required
>
> 'link to worksheet
> With Worksheets("Reviews").Range("A4300") '<< change as required
> .Formula = mydata
>
> 'convert formula to text
> .Copy
> .PasteSpecial Paste:=xlPasteValues
> End With