Copy rows to a new sheet using command button

Question

I am attempting to use a command button to copy rows to a different sheet in my excel workbook. I hope to type in the row number and have the VBA copy the row into a worksheet called "6.2022 Basis". My issue is "6.2022 Basis" has over 150 rows and my previous codes have overwritten some rows that I need. I am trying to use rngToCopy but I keep getting errors. Any ideas or advice would be greatly appreciated.

Private Sub CommandButton3_Click()

Dim rngToCopy As Range

Dim rngToPaste As Range

Set rngToCopy = Application.InputBox("Select range in Updates", Type:=64)

Windows("6.2022 Basis").Activate

Set rngToPaste = Application.InputBox("Select range to Paste in 6.2022 Basis", Type:=64)

rngToCopy.Copy

rngToPaste.Activate

ActiveSheet.Paste Link:=True

End Sub

Private Sub CommandButton3_Click()

Dim rngToCopy As Range

Dim rngToPaste As Range

Set rngToCopy = Application.InputBox("Select range in Updates", Type:=8)

ThisWorkbook.Worksheets("6.2022 Basis").Activate

Set rngToPaste = Application.InputBox("Select range to Paste in 6.2022 Basis", Type:=8)

rngToCopy.Copy

rngToPaste.Activate

ActiveSheet.Paste Link:=True

End Sub

I think the new issue is I need to paste the row in a table for my sort functions to work, and then it tries to overwrite the cell.

Answer 1

1. InputBox Type 64 is an array of values. If you're trying to save the ranges, you need to set Type = 8.
2. For worksheets, use the Thisworkbook.Worksheets or ActiveWorkbook.Worksheets collection instead of the Application.Windows collection. If you have a second workbook that you are working on, you can also use WB.Worksheets where WB is any valid workbook object. Same as before, you can reference the worksheet by name or by number: ThisWorkbook.Worksheets("6.2022 Basis").Activate
3. Pasting data onto a table causes an error with Link:=True. You will need to switch methods from using Worksheet.Paste to directly assigning the values & formulas, cell by cell. I would suggest using a loop like the following:

Private Sub CommandButton3_Click()

Dim rngToCopy As Range
On Error Resume Next
Set rngToCopy = Application.InputBox("Select range in Updates", Type:=8)
If rngToCopy Is Nothing Then Exit Sub
On Error GoTo 0

ThisWorkbook.Worksheets("6.2022 Basis").Activate

Dim rngToPaste As Range
On Error Resume Next
Set rngToPaste = Application.InputBox("Select range to Paste in 6.2022 Basis", Type:=8)
If rngToPaste Is Nothing Then Exit Sub
On Error GoTo 0

rngToPaste.ClearContents

Dim r As Long, c As Long
For r = 1 To rngToCopy.Rows.Count
    For c = 1 To rngToCopy.Columns.Count
        If rngToCopy.Cells(r, c) <> "" Then
            rngToPaste.Cells(r, c).Formula = "=" & rngToCopy.Cells(r, c).Address(External:=True)
        End If
    Next
Next
End Sub