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.

What errors do you get? Which line of code is highlighted when the error occurs? — Toddleson
– Toddleson, Commented Jun 21, 2022 at 13:53
I get Run Time error '9": Subscript out of range, it highlights Windows("6.2022 Basis").Activate The row that I am selecting is A:R if that could be part of the issue? — jmowalker
– jmowalker, Commented Jun 21, 2022 at 14:04

Toddleson · Accepted Answer · 2022-06-21 14:51:15Z

0

InputBox Type 64 is an array of values. If you're trying to save the ranges, you need to set Type = 8.
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
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

edited Jun 21, 2022 at 14:51

answered Jun 21, 2022 at 13:59

Toddleson

4,4852 gold badges9 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

jmowalker Over a year ago

I tried that, but I got a run-time error. So I tried 64 both didn't work.

jmowalker Over a year ago

Private Sub CommandButton3_Click() Dim rngToCopy As Range Dim rngToPaste As Range Set rngToCopy = Application.InputBox("Select range in Updates", Type:=8) Windows("6.2022 Basis.xlsm").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

jmowalker Over a year ago

Like this? I just ran this and I got the same error "Run-time error '9'"

Toddleson Over a year ago

Is your file named "6.2022 Basis.xlsm" ? Or is one of the worksheets named "6.2022 Basis"? A Window is when you open a file to view it, a worksheet is one of the tabs inside that file.

jmowalker Over a year ago

I am copying from "Updates" to "6.2022 Basis" in my workbook. They are both worksheets, sorry!

|

Collectives™ on Stack Overflow

Copy rows to a new sheet using command button

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related