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).Resize(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

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).Resize(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
>
>
>

Hi Tom,

That did it. Thanks for the help, I appreciate it.

This line:

> vaData = wbkSource.Sheets(i).Range("IV16").End(xlToLeft).Resize(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" <lhkittle@comcast.net> wrote in message
news:8cWdneedtvVAIYTZRVn-qg@comcast.com...
>
> 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).Resize(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
>