Paste Special Values - VBA code

[Carl Stephens]

Hello All,

I am trying to adjust the below code from paste to paste special values with the second code, but it is not working. What am I doing wrong with the second line of code?

targetRange.PasteSpecial Transpose:=True
targetRange.PasteSpecial xlPasteValues, Transpose:=True

Thank you.

 

[jolivanes]

What is targetRange?

If you substitute "targetRange" for a cell address it works.

 

[Carl Stephens]

What is targetRange?

Sorry, I should have included the whole code. The below code works with this code (targetRange.PasteSpecial Transpose:=True) but not when I change it to (targetRange.PasteSpecial xlPasteValues, Transpose:=True).

Sub CopyDataFromDFToRT()
Dim dfSheet As Worksheet
Dim rtSheet As Worksheet
Dim lastRowDF As Long
Dim lastRowRT As Long
Dim sourceRange As Range
Dim targetRange As Range
Dim firstBlankCellRT As Range

' Set the sheets
Set dfSheet = ThisWorkbook.Sheets("DF")
Set rtSheet = ThisWorkbook.Sheets("RT")

' Find the last used row in the source range (DF sheet)
lastRowDF = dfSheet.Cells(Rows.Count, "C").End(xlUp).Row

' Set the source range
Set sourceRange = dfSheet.Range("C3:C15" & lastRowDF)

' Find the first empty row in the target range (RT sheet)
lastRowRT = rtSheet.Cells(Rows.Count, "C").End(xlUp).Row + 1

' Set the target range
Set targetRange = rtSheet.Cells(lastRowRT, "C").Resize(1, sourceRange.Rows.Count)

' Copy and transpose the data
sourceRange.Copy
targetRange.PasteSpecial Transpose:=True

' Clear the source range (DF sheet)
dfSheet.Range("C3:C15").ClearContents

' Find the first blank cell in column C on the RT sheet, starting from C1
Set firstBlankCellRT = rtSheet.Range("C1").End(xlDown).Offset(1, 0)

' Select the "RT" tab first
Sheets("RT").Select

' Select cell C3 on the "RT" tab
Range("C3").Select

' Select the last non-empty cell in column C on the "RT" tab
Selection.End(xlDown).Select


End Sub

 

[jolivanes]

I was just going to post this when I saw your new post.
I'll have a look at your code. In the meantime have a look at this and see if it makes sense.

Actually I very seldom use PasteSpecial xlPasteValues
My preference is

Sub Without_Paste_Special()
Dim rng
Set rng = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
Range("G20").Resize(, rng.Rows.Count).Value = Application.Transpose(rng)
End Sub

 

[jolivanes]

Should this

Set sourceRange = dfSheet.Range("C3:C15" & lastRowDF)

be

Set sourceRange = dfSheet.Range("C3:C" & lastRowDF)

Change this

sourceRange.Copy
targetRange.PasteSpecial Transpose:=True

to this

targetRange.Value = Application.Tanspose(sourceRange)

 

[Carl Stephens]

Should this

Set sourceRange = dfSheet.Range("C3:C15" & lastRowDF)

be

Set sourceRange = dfSheet.Range("C3:C" & lastRowDF)

Change this

sourceRange.Copy
targetRange.PasteSpecial Transpose:=True

to this

targetRange.Value = Application.Tanspose(sourceRange)

Thank you. Appreciate your response.

"Should this be?" - Yes, I made the change, however, the below code is still giving an error for some reason.

targetRange.Value = Application.Tanspose(sourceRange)

 

[jolivanes]

Change references to suit your scenario.

Sub Try_So()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim lr1 As Long, lr2 As Long
Dim sr As Range, tr As Range
Set sh1 = Worksheets("DF")
Set sh2 = Worksheets("RT")
lr1 = sh1.Cells(sh1.Rows.Count, 3).End(xlUp).Row
lr2 = sh2.Cells(sh2.Rows.Count, 3).End(xlUp).Row
Set sr = sh1.Range("C3:C" & lr1)
sh2.Cells(lr2 + 1, 3).Resize(, sr.Rows.Count).Value = Application.Transpose(sr.Value)
sr.ClearContents
End Sub

In your code in Post #3 you don't need the lines after the clearcontents line.
BTW, using xlDown can give wrong expected results if there are empty cells in the range.