CODEWITHSUNDEEP
vbaexcel
Dora
Dora

Reputation: 63

Automatically Copy Rows Between Worksheets to Last Row on Workbook Open

I have been trying, for two weeks now, to write a code which will be able to do the following: The first 21 rows of worksheet "Time Cards" are copied and inserted into the first blank row at the bottom of the same sheet "Time Cards." I also would like, once the rows are copied, the cell that houses the date of the selection that was just copied (at the bottom of the sheet) to be selected, so that the user does not have to scroll down 6500 rows.

The code would have to select sheet ("Time Cards"), copy selection C3:N23, and paste selection to the last and first blank row to the bottom of the sheets("Time Cards"). Then the cell (in the selection that was just pasted) that house the "date" has to be automatically selected (write code to search the range for "date"?). Once all that is done, I'd like a code, linked to a macro button, that the user can click to clear the data validations, so that only formulas remain in the newly pasted cells, and all the VLOOKUPS, and DATA VALIDATIONS have been cleared.

Please, can someone please please please help me write a code. I've really did a lot a research, tried several different approaches, and have not been able to make anything work. I'm not really proficient with VBA, so any help would be greatly appreciated.

Here's my first attempt:

Sub CopyInfo()

On Error GoTo Err_Execute

    Sheets("Time Cards").Select
    Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(1, 0).Select


    Sheets("TC-Start Here").Select
    Range("C5:N25").Select
    Selection.Copy

    Sheets("Time Cards").Select
    'Range("C5:N25").Select
    Selection.Insert

Err_Execute:
    MsgBox "The Data has been successfully Copied"

End Sub

Then I tried to select the last row and paste (2nd attempt):

Sub CopyInfo()

On Error GoTo Err_Execute

    Sheets("Time Cards").Select
    Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(1, 0).Select


    Sheets("TC-Start Here").Select
    Range("C5:N25").Select
    Selection.Copy


    Sheets("Time Cards").Select
    Cells(Application.Rows.Count, 1).End(xlUp).Offset(1, 0).Select
    Selection.Insert Shift:=xlDown

    Sheets("Time Cards").Select
    'Range("C5:N25").Select
    Selection.Insert

Err_Execute:
    MsgBox "The Data has been successfully Copied"

End Sub

It doesn't seem to work and I could never get it to select the cell containing the date nor clear the validations.

Upvotes: 0

Views: 3223

Answers (2)

Tim Williams
Tim Williams

Reputation: 166196

Sub CopyInfo()

    With Sheets("Time Cards")
         .Range("C5:N25").Copy .Cells(Rows.Count,3).End(xlUp).Offset(1, 0)
         .Cells(Rows.Count,3).End(xlUp).Select
    End With
    'need some info on exactly what should be cleared from the copied range...
    MsgBox "The Data has been successfully Copied"

End Sub

Upvotes: 1

scott
scott

Reputation: 2275

I think this is what you want, if not please clarify your question further

    Dim lrow as long
    lrow = sheets("time cards").range("C5").end(xlup).row + 1
    sheets("TC-Start Here").range("C5:N25").copy
    sheets("time cards").range("C" & lrow).pastespecial paste:=xlpastevalues, _
                                                        operation:=xlnone, _
                                                        skipblanks:=False, _
                                                        Transpose:=false
    sheets("time cards").range("C5").end(xlup).select

Upvotes: 1

Related Questions