CODEWITHSUNDEEP
excelvba
Dora
Dora

Reputation: 63

Copy and Insert rows from one sheet to another by pushing a macro button

I have this workbook: I need to have a macro button that my users can click that say "Click Here to Copy", then i need a code that copies rows number 5-25 of sheet titled "TC-Star Here", and i need the copied rows to be inserted (i need the pasted rows to not auto delete the ones that were there previously, so the inserted rows would have to shift the previous ones down) into another sheet named "Time Cards". The inserted rows, i need to have to code insert them starting at cell A1. So everytime the macro button is clicked, rows are copied, and inserted with the previous data unmodified/deleted.

I have this code so far: 

Sub CopyInfo()
On Error GoTo Err_Execute
    Sheet2.Range("A1:F11").Value = Sheet1.Range("A1:F11").Value
    Sheet1.Range("A1:F11").Copy
    Sheet2.Range("A1").InsertCopiedCells
Err_Execute:
    MsgBox "All have been copied!"

End Sub

But everytime the button is clicked, it pastes the rows over the existing rows.

Please Help.

Upvotes: 2

Views: 87229

Answers (5)

kumar
kumar

Reputation: 1

Sub Test()
    With ActiveSheet
    lastrow = .Cells(.rows.Count, "A").End(xlUp).Row
    lastrow = lastrow - 1
    MsgBox lastrow
    End With
End Sub

Upvotes: -1

Han
Han

Reputation: 45

Thx for the post dude~, each answer provider solve my problem~.
Here are my coding use in excel, for each selection will insert to Sheet 2, 

  Private Sub CommandButton2_Click()

      Selection.Copy
      Sheet2.Range("A4").Rows("1:1").Insert Shift:=xlDown
      Sheets("Sheet1").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlUp

  End Sub

Upvotes: -1

HelpNeeded
HelpNeeded

Reputation: 61

Simple - Use a loop (Also, turn off ScreenUpdating to greatly speed up the processing time) The following will copy all rows from the sheet named "Sheet2" over to the same row in sheet named "Sheet1"

Sub CopyOver()
    Application.ScreenUpdating = False
    For j = 1 To 1560 'Or however many rows u have
        On Error GoTo Err_Execute
        Sheets("Sheet2").Rows(j).Copy
        Sheets("Sheet1").Rows(j).Insert Shift:=xlDown
    Next j
    Application.ScreenUpdating = True
    Err_Execute:
        If Err.Number = 0 Then MsgBox "All have been copied!" Else _
        MsgBox Err.Description
End Sub

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149295

Is this what you are trying?

Sub CopyInfo()
    On Error GoTo Err_Execute

    Sheet1.Range("A1:F11").Copy
    Sheet2.Range("A1").Rows("1:1").Insert Shift:=xlDown

Err_Execute:
    If Err.Number = 0 Then MsgBox "All have been copied!" Else _
    MsgBox Err.Description
End Sub

Upvotes: 5

DGH
DGH

Reputation: 11539

This line

Sheet2.Range("A1:F11").Value = Sheet1.Range("A1:F11").Value

is setting the value of those cells on Sheet2 to the values on Sheet1 - that is, it's copying the values over and replacing what was there. THEN, you're doing your copy-insert operation.

Delete/comment out that line and see what happens.

Also, I believe the copy area and the paste area have to be the same size. Try making it Sheet2.Range("A1:F11").InsertCopiedCells

Upvotes: 1

Related Questions