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

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

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