Copying Data in a Range and pasting Values on Next Avaiable Row in another Sheet

    I am try to copy and paste values from each personnel in the Two Week Work Plan to each specific personal summary sheet to log their activities through out the year with the click of a button. I am having trouble with the data keeps on pasting over the existing data and not moving to the next available row in Column A.


    Here is what I have so far:


    Sub Record()

    '

    ' Record Macro

    '

    'Get the last used row into a variable

    Dim lastRow As Long

    lastRow = ThisWorkbook.Sheets("Sammy").Cells(Rows.Count, "A").End(xlUp).Row + 1


    'Copy Summary Data Sheet F3

    Sheets("Two Week Work Plan").Range("B11:I24").Copy


    'And paste it into the last row (column A) of "Personal Log" sheet

    Sheets("Sammy").Range("A" & lastRow).Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

    :=False, Transpose:=False


    End Sub



    See attached Excel document if you need to play around in it.

    Hello and Welcome to the Forum :)


    In your Column A ... you have decided to Merge 14 Rows for each person ...


    Merged Cells are a true NIGHTMARE ...:evil: They should be FORBIDDEN ... !!!


    Would highly encourage you to totally get rid of them ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Edited 2 times, last by Carim ().

    Welcome to Ozgrid.


    I have added code tags to your post. Please read the Forum Rules to understand why.


    Working with data in Excel has a few basic rules. All data should be stored in a table format with a one row header for titles (like your individual sheets. The data should contain no completely empty rows or columns. You have formatted the data as a Table which is good but you need to remove those unused rows.


    Where you have merged cells for Sammy, etc is completely wrong - those cells should be separate and contain the name on each line - once established excel will autofill the names as you type.


    Splitting the data from the main sheet to individual sheets is a waste of effort. If properly set up you can simply AutoFilter the data by name.

    I've tried cleaning the data up but I don't really understand what you are recording.

    I want to be able to log whatever was inputted in Sammy's Rows on the Two Week Work Plan. Basically, copy/paste it into his log sheet ("Sammy") with the click of a macro button. That button would do the same for the other employees. Logging the data would be able to allow us track what our employees have been working on through out the year. After logging what they did we can then clear the Two Week Work Plan, change the start date, and plan the next two weeks.

    I really think it would be better to start again, I don't know what you have done but I can't create a proper table.


    The code that you asked about is sinmple but more important you need to get the main data layout correct.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login