Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special Values Syntax
Hello Excel Experts and Users, I got this code from GS yesterday and it works fine. I am now trying to adapt it to a new use elsewhere, where it does not transpose, but instead do a paste special values. I've tried replacing the "Transpose" in last line of code with the "paste special values" code from a recorded macro. It ain't having anything to do with that, line goes all RED. Someone know the correct syntax to make this happen? Sub Reconstruct_To_Models() ' Copies a range in wbkSource to a location in wbkTarget ' Data is transposed from vertical (source) to horizontal (target) Dim wbkSource As Workbook, wbkTarget As Workbook Dim rngSource As Range, rngTarget As Range Dim i As Long, lShts As Long Dim sName As String, vaData As Variant Set wbkSource = ThisWorkbook Set wbkTarget = Workbooks("Test Models.xls") lShts = wbkSource.Sheets("Carroll").Index ' - 1 For i = 1 To lShts sName = wbkSource.Sheets(i).Name vaData = wbkSource.Sheets(i).Range("IV16").End(xlToLeft).Re size(2, 1) Set rngTarget = wbkTarget.Sheets(sName).Range("IV29") _ .End(xlToLeft).Offset(0, 1).Resize(2, 1) rngTarget = Application.WorksheetFunction.Transpose(vaData) Next End Sub Thanks for any help. Regards, Howard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special Values Syntax
rngTarget = Application.WorksheetFunction.Transpose(vaData)
to rngTarget = vaData should be all you need. rngTarget and vaData are already 2 rows by 1 col from what I can see. -- Regards, Tom Ogilvy "L. Howard Kittle" wrote: Hello Excel Experts and Users, I got this code from GS yesterday and it works fine. I am now trying to adapt it to a new use elsewhere, where it does not transpose, but instead do a paste special values. I've tried replacing the "Transpose" in last line of code with the "paste special values" code from a recorded macro. It ain't having anything to do with that, line goes all RED. Someone know the correct syntax to make this happen? Sub Reconstruct_To_Models() ' Copies a range in wbkSource to a location in wbkTarget ' Data is transposed from vertical (source) to horizontal (target) Dim wbkSource As Workbook, wbkTarget As Workbook Dim rngSource As Range, rngTarget As Range Dim i As Long, lShts As Long Dim sName As String, vaData As Variant Set wbkSource = ThisWorkbook Set wbkTarget = Workbooks("Test Models.xls") lShts = wbkSource.Sheets("Carroll").Index ' - 1 For i = 1 To lShts sName = wbkSource.Sheets(i).Name vaData = wbkSource.Sheets(i).Range("IV16").End(xlToLeft).Re size(2, 1) Set rngTarget = wbkTarget.Sheets(sName).Range("IV29") _ .End(xlToLeft).Offset(0, 1).Resize(2, 1) rngTarget = Application.WorksheetFunction.Transpose(vaData) Next End Sub Thanks for any help. Regards, Howard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special Values Syntax
Hi Tom,
That did it. Thanks for the help, I appreciate it. This line: vaData = wbkSource.Sheets(i).Range("IV16").End(xlToLeft).Re size(2, 1) refers to 2 cells whose values are derived from formulas. So it appears if you set the cells value to a variable you can overlook the "paste special values"?? Again, thanks for the help. Regards, Howard "L. Howard Kittle" wrote in message ... Hello Excel Experts and Users, I got this code from GS yesterday and it works fine. I am now trying to adapt it to a new use elsewhere, where it does not transpose, but instead do a paste special values. I've tried replacing the "Transpose" in last line of code with the "paste special values" code from a recorded macro. It ain't having anything to do with that, line goes all RED. Someone know the correct syntax to make this happen? Sub Reconstruct_To_Models() ' Copies a range in wbkSource to a location in wbkTarget ' Data is transposed from vertical (source) to horizontal (target) Dim wbkSource As Workbook, wbkTarget As Workbook Dim rngSource As Range, rngTarget As Range Dim i As Long, lShts As Long Dim sName As String, vaData As Variant Set wbkSource = ThisWorkbook Set wbkTarget = Workbooks("Test Models.xls") lShts = wbkSource.Sheets("Carroll").Index ' - 1 For i = 1 To lShts sName = wbkSource.Sheets(i).Name vaData = wbkSource.Sheets(i).Range("IV16").End(xlToLeft).Re size(2, 1) Set rngTarget = wbkTarget.Sheets(sName).Range("IV29") _ .End(xlToLeft).Offset(0, 1).Resize(2, 1) rngTarget = Application.WorksheetFunction.Transpose(vaData) Next End Sub Thanks for any help. Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste Special Values | Excel Programming | |||
paste special values | Excel Worksheet Functions | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming | |||
Paste special values | Excel Programming | |||
paste as values syntax | Excel Programming |