Copying and Pasting from One Sheet to Another But Data is Getting Cut Off

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    • New

    I have a macro that copies and pastes data from one sheet to another sheet. This macro was working perfectly fine for awhile. However, now it is cutting off the number of rows that get copied from my "CP" sheet into my "Data" sheet based on how many rows I have in my "Data" sheet.


    For example, I have 180,000 rows of data in my "Data" sheet as of now. My "CP" sheet now has 182,000 rows. Only 180,000 rows are being copied from my "CP" sheet but I am needing all the rows to be copied and pasted over.


    Here is the macro I am working with:

    Sheets("CP").Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Copy Destination:=Sheets("DATA").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)


    What I am assuming is happening is that the macro is counting the number of rows on the "Data" sheet and then pasting that number of rows from the "CP" sheet. However, I don't know what is wrong with it because I need to count the rows so that it pastes the "CP" data below the current data in the "Data" sheet.


    Does anyone have any idea of what I need to adjust to get this to work properly?

    • New
    • Best Answer

    Your code is determining the last copy row based on the active sheet at the time. You should specify the CP sheet:


    Code
    Sheets("CP").Range("A2:A" & Sheets("CP").Cells(Rows.Count, "A").End(xlUp).Row).Copy Destination:=Sheets("DATA").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Theo2023

    Selected a post as the best answer.
    • New

    Glad we could help. :)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

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

Register Yourself Login