Paste/Select VBA error

[Carl Stephens]

Hello All,

The below code is doing pretty much everything that it should, except the last line (firstBlankCellRT.Select) which should be selecting the first blank cell in column C on the 'RT' tab, but I get a runtime error 1004 message citing "Select method of range class failed" which should not happen as there are plenty of blank cells in column C, and for the life of cannot fix it, hence my appeal for help with the professionals, you. Can anyone help? Thank you in advance.


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:C" & 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:C14").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 first blank cell in column C on the RT sheet
firstBlankCellRT.Select

End Sub

 

[StephenCrump]

If rtSheet is not the ActiveSheet, you'll need to activate it before you can select a cell on that sheet.

 

[My Aswer Is This]

Here is a way to GoTo a cell on another sheet if that is what you want to do.
Not using select.

Sub RectangleRoundedCorners1_Click()
Dim Lastrow As Long
Lastrow = Sheets("Alpha").Cells(Rows.Count, "A").End(xlUp).Row + 1
Application.Goto Sheets("Alpha").Cells(Lastrow, 1)
End Sub