Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying one work sheet to another
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("A4:D300") '<< change as required .Formula = mydata 'convert formula to text .Copy .PasteSpecial Paste:=xlPasteValues End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying one work sheet to another
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 D4:D800, 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("A4:D300") '<< change as required .Formula = mydata 'convert formula to text .Copy .PasteSpecial Paste:=xlPasteValues End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying one work sheet to another
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: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:D" & 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("A4:D300") '<< change as required .Formula = mydata 'convert formula to text .Copy .PasteSpecial Paste:=xlPasteValues End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying one work sheet to another
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").Va lue Worksheets("Reviews").Range("A4:D300").Value = mydata '<< change as required End Sub Regards Steve Dalton "Withnails" wrote in message ... 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("A4:D300") '<< change as required .Formula = mydata 'convert formula to text .Copy .PasteSpecial Paste:=xlPasteValues End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with copying multiple ranges and paste it on a work sheet based on conditions | Excel Discussion (Misc queries) | |||
copying date from one sheet to another work sheet | Excel Worksheet Functions | |||
Copying a work sheet cell reference as relative not absolute? | Excel Discussion (Misc queries) | |||
copying and paste data from each worksheet to a summary work sheet | Excel Programming | |||
Copying a sheet to new work sheet | Excel Programming |