Copy data until last row in a new sheet and always exclude the last 4 rows

Multi tool use
Multi tool use












1















I'm having a hard time trying to create a macro that copy the table from sheet 1 witch is defined by the area D21:O21 until the end – (minus) the last 4 lines.



I'm also having problems to paste results because in sheet 2 I have a table that feeds pivot charts. After I delete previous filled rows (with values) and paste new ones (new values) the table format seems to extend much further than it is supposed to do (meaning it adds blank cells downstream as if the copyed sheet 1 had a lot more rows with values on it).



For example: Imagine that my table (in sheet 1) happens to have 600 rows with values. If I copy to the table (table format in sheet 2) I end up by extend the table in sheet 2 much further than 600 rows (aprox 10000). And this is not what i want... Instead of adding multiple empty lines i want the opposite: from those 600 i want to copy all except last 4 lines. = from top to 596 rows.



I have this code. It's pretty effective apart from those two caveats - not beeing able to disregard last 4 lines and extend more than it should when copied:



    Sub Prime()

Dim Last_Row1 As Long, Last_Row2 As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Enter DATA here")
Set ws2 = Sheets("DATA")
Application.ScreenUpdating = False
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
ws1.Range("D21:O21" & Last_Row1).Copy ws2.Range("A" & Last_Row2)
Application.ScreenUpdating = True

End Sub


Can you help me please?










share|improve this question


















  • 1





    Have you looked at Resize method on the Range object? Also D21:O21 is a single row of data so not sure what you mean by discarding the last four rows?

    – Alex P
    Jan 2 at 15:50













  • ws1.Range("D21:O21" & Last_Row1) is probably wrong, did you mean ws1.Range("D21:O" & Last_Row1)? You can just subtract 4 from Last_Row1 then.

    – Vincent G
    Jan 2 at 15:57











  • Vincent G Thaks It worked.

    – jps17183
    Jan 2 at 16:14
















1















I'm having a hard time trying to create a macro that copy the table from sheet 1 witch is defined by the area D21:O21 until the end – (minus) the last 4 lines.



I'm also having problems to paste results because in sheet 2 I have a table that feeds pivot charts. After I delete previous filled rows (with values) and paste new ones (new values) the table format seems to extend much further than it is supposed to do (meaning it adds blank cells downstream as if the copyed sheet 1 had a lot more rows with values on it).



For example: Imagine that my table (in sheet 1) happens to have 600 rows with values. If I copy to the table (table format in sheet 2) I end up by extend the table in sheet 2 much further than 600 rows (aprox 10000). And this is not what i want... Instead of adding multiple empty lines i want the opposite: from those 600 i want to copy all except last 4 lines. = from top to 596 rows.



I have this code. It's pretty effective apart from those two caveats - not beeing able to disregard last 4 lines and extend more than it should when copied:



    Sub Prime()

Dim Last_Row1 As Long, Last_Row2 As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Enter DATA here")
Set ws2 = Sheets("DATA")
Application.ScreenUpdating = False
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
ws1.Range("D21:O21" & Last_Row1).Copy ws2.Range("A" & Last_Row2)
Application.ScreenUpdating = True

End Sub


Can you help me please?










share|improve this question


















  • 1





    Have you looked at Resize method on the Range object? Also D21:O21 is a single row of data so not sure what you mean by discarding the last four rows?

    – Alex P
    Jan 2 at 15:50













  • ws1.Range("D21:O21" & Last_Row1) is probably wrong, did you mean ws1.Range("D21:O" & Last_Row1)? You can just subtract 4 from Last_Row1 then.

    – Vincent G
    Jan 2 at 15:57











  • Vincent G Thaks It worked.

    – jps17183
    Jan 2 at 16:14














1












1








1








I'm having a hard time trying to create a macro that copy the table from sheet 1 witch is defined by the area D21:O21 until the end – (minus) the last 4 lines.



I'm also having problems to paste results because in sheet 2 I have a table that feeds pivot charts. After I delete previous filled rows (with values) and paste new ones (new values) the table format seems to extend much further than it is supposed to do (meaning it adds blank cells downstream as if the copyed sheet 1 had a lot more rows with values on it).



For example: Imagine that my table (in sheet 1) happens to have 600 rows with values. If I copy to the table (table format in sheet 2) I end up by extend the table in sheet 2 much further than 600 rows (aprox 10000). And this is not what i want... Instead of adding multiple empty lines i want the opposite: from those 600 i want to copy all except last 4 lines. = from top to 596 rows.



I have this code. It's pretty effective apart from those two caveats - not beeing able to disregard last 4 lines and extend more than it should when copied:



    Sub Prime()

Dim Last_Row1 As Long, Last_Row2 As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Enter DATA here")
Set ws2 = Sheets("DATA")
Application.ScreenUpdating = False
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
ws1.Range("D21:O21" & Last_Row1).Copy ws2.Range("A" & Last_Row2)
Application.ScreenUpdating = True

End Sub


Can you help me please?










share|improve this question














I'm having a hard time trying to create a macro that copy the table from sheet 1 witch is defined by the area D21:O21 until the end – (minus) the last 4 lines.



I'm also having problems to paste results because in sheet 2 I have a table that feeds pivot charts. After I delete previous filled rows (with values) and paste new ones (new values) the table format seems to extend much further than it is supposed to do (meaning it adds blank cells downstream as if the copyed sheet 1 had a lot more rows with values on it).



For example: Imagine that my table (in sheet 1) happens to have 600 rows with values. If I copy to the table (table format in sheet 2) I end up by extend the table in sheet 2 much further than 600 rows (aprox 10000). And this is not what i want... Instead of adding multiple empty lines i want the opposite: from those 600 i want to copy all except last 4 lines. = from top to 596 rows.



I have this code. It's pretty effective apart from those two caveats - not beeing able to disregard last 4 lines and extend more than it should when copied:



    Sub Prime()

Dim Last_Row1 As Long, Last_Row2 As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Enter DATA here")
Set ws2 = Sheets("DATA")
Application.ScreenUpdating = False
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
ws1.Range("D21:O21" & Last_Row1).Copy ws2.Range("A" & Last_Row2)
Application.ScreenUpdating = True

End Sub


Can you help me please?







excel vba excel-vba






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Jan 2 at 15:48









jps17183jps17183

155




155








  • 1





    Have you looked at Resize method on the Range object? Also D21:O21 is a single row of data so not sure what you mean by discarding the last four rows?

    – Alex P
    Jan 2 at 15:50













  • ws1.Range("D21:O21" & Last_Row1) is probably wrong, did you mean ws1.Range("D21:O" & Last_Row1)? You can just subtract 4 from Last_Row1 then.

    – Vincent G
    Jan 2 at 15:57











  • Vincent G Thaks It worked.

    – jps17183
    Jan 2 at 16:14














  • 1





    Have you looked at Resize method on the Range object? Also D21:O21 is a single row of data so not sure what you mean by discarding the last four rows?

    – Alex P
    Jan 2 at 15:50













  • ws1.Range("D21:O21" & Last_Row1) is probably wrong, did you mean ws1.Range("D21:O" & Last_Row1)? You can just subtract 4 from Last_Row1 then.

    – Vincent G
    Jan 2 at 15:57











  • Vincent G Thaks It worked.

    – jps17183
    Jan 2 at 16:14








1




1





Have you looked at Resize method on the Range object? Also D21:O21 is a single row of data so not sure what you mean by discarding the last four rows?

– Alex P
Jan 2 at 15:50







Have you looked at Resize method on the Range object? Also D21:O21 is a single row of data so not sure what you mean by discarding the last four rows?

– Alex P
Jan 2 at 15:50















ws1.Range("D21:O21" & Last_Row1) is probably wrong, did you mean ws1.Range("D21:O" & Last_Row1)? You can just subtract 4 from Last_Row1 then.

– Vincent G
Jan 2 at 15:57





ws1.Range("D21:O21" & Last_Row1) is probably wrong, did you mean ws1.Range("D21:O" & Last_Row1)? You can just subtract 4 from Last_Row1 then.

– Vincent G
Jan 2 at 15:57













Vincent G Thaks It worked.

– jps17183
Jan 2 at 16:14





Vincent G Thaks It worked.

– jps17183
Jan 2 at 16:14












3 Answers
3






active

oldest

votes


















0














Try this:



Sub Prime()
Dim Last_Row1 As Long, Last_Row2 As Long, table As Range
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Enter DATA here")
Set ws2 = Sheets("DATA")

Set table = ws1.Range("D21:O28") // I arbitrarily set this to 8 rows

Application.ScreenUpdating = False

Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
table.Resize(table.Rows.Count - 4, table.Columns.Count).Copy ws2.Range("A" & Last_Row2)

Application.ScreenUpdating = True
End Sub





share|improve this answer































    0














       Sub Prime()

    Dim Last_Row1 As Long, Last_Row2 As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Sheets("Enter DATA here")
    Set ws2 = Sheets("DATA")
    Application.ScreenUpdating = False
    Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
    Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
    ws1.Range("D21:O" & Last_Row1-4).Copy ws2.Range("A" & Last_Row2)
    Application.ScreenUpdating = True

    End Sub





    share|improve this answer































      0














      You can use Offset function to exclude the last 4 rows.



      Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).offset(-4,0).Row 


      When you copy the range, you should delete row indicator "21" behind column "O", otherwise, the code would not select till the last row. Like following:



      ws1.Range("D21:O" & Last_Row1).Copy ws2.Range("A" & Last_Row2)





      share|improve this answer























        Your Answer






        StackExchange.ifUsing("editor", function () {
        StackExchange.using("externalEditor", function () {
        StackExchange.using("snippets", function () {
        StackExchange.snippets.init();
        });
        });
        }, "code-snippets");

        StackExchange.ready(function() {
        var channelOptions = {
        tags: "".split(" "),
        id: "1"
        };
        initTagRenderer("".split(" "), "".split(" "), channelOptions);

        StackExchange.using("externalEditor", function() {
        // Have to fire editor after snippets, if snippets enabled
        if (StackExchange.settings.snippets.snippetsEnabled) {
        StackExchange.using("snippets", function() {
        createEditor();
        });
        }
        else {
        createEditor();
        }
        });

        function createEditor() {
        StackExchange.prepareEditor({
        heartbeatType: 'answer',
        autoActivateHeartbeat: false,
        convertImagesToLinks: true,
        noModals: true,
        showLowRepImageUploadWarning: true,
        reputationToPostImages: 10,
        bindNavPrevention: true,
        postfix: "",
        imageUploader: {
        brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
        contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
        allowUrls: true
        },
        onDemand: true,
        discardSelector: ".discard-answer"
        ,immediatelyShowMarkdownHelp:true
        });


        }
        });














        draft saved

        draft discarded


















        StackExchange.ready(
        function () {
        StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54009244%2fcopy-data-until-last-row-in-a-new-sheet-and-always-exclude-the-last-4-rows%23new-answer', 'question_page');
        }
        );

        Post as a guest















        Required, but never shown

























        3 Answers
        3






        active

        oldest

        votes








        3 Answers
        3






        active

        oldest

        votes









        active

        oldest

        votes






        active

        oldest

        votes









        0














        Try this:



        Sub Prime()
        Dim Last_Row1 As Long, Last_Row2 As Long, table As Range
        Dim ws1 As Worksheet, ws2 As Worksheet

        Set ws1 = Sheets("Enter DATA here")
        Set ws2 = Sheets("DATA")

        Set table = ws1.Range("D21:O28") // I arbitrarily set this to 8 rows

        Application.ScreenUpdating = False

        Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
        Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
        table.Resize(table.Rows.Count - 4, table.Columns.Count).Copy ws2.Range("A" & Last_Row2)

        Application.ScreenUpdating = True
        End Sub





        share|improve this answer




























          0














          Try this:



          Sub Prime()
          Dim Last_Row1 As Long, Last_Row2 As Long, table As Range
          Dim ws1 As Worksheet, ws2 As Worksheet

          Set ws1 = Sheets("Enter DATA here")
          Set ws2 = Sheets("DATA")

          Set table = ws1.Range("D21:O28") // I arbitrarily set this to 8 rows

          Application.ScreenUpdating = False

          Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
          Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
          table.Resize(table.Rows.Count - 4, table.Columns.Count).Copy ws2.Range("A" & Last_Row2)

          Application.ScreenUpdating = True
          End Sub





          share|improve this answer


























            0












            0








            0







            Try this:



            Sub Prime()
            Dim Last_Row1 As Long, Last_Row2 As Long, table As Range
            Dim ws1 As Worksheet, ws2 As Worksheet

            Set ws1 = Sheets("Enter DATA here")
            Set ws2 = Sheets("DATA")

            Set table = ws1.Range("D21:O28") // I arbitrarily set this to 8 rows

            Application.ScreenUpdating = False

            Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
            Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
            table.Resize(table.Rows.Count - 4, table.Columns.Count).Copy ws2.Range("A" & Last_Row2)

            Application.ScreenUpdating = True
            End Sub





            share|improve this answer













            Try this:



            Sub Prime()
            Dim Last_Row1 As Long, Last_Row2 As Long, table As Range
            Dim ws1 As Worksheet, ws2 As Worksheet

            Set ws1 = Sheets("Enter DATA here")
            Set ws2 = Sheets("DATA")

            Set table = ws1.Range("D21:O28") // I arbitrarily set this to 8 rows

            Application.ScreenUpdating = False

            Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
            Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
            table.Resize(table.Rows.Count - 4, table.Columns.Count).Copy ws2.Range("A" & Last_Row2)

            Application.ScreenUpdating = True
            End Sub






            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered Jan 2 at 15:58









            Alex PAlex P

            10k44562




            10k44562

























                0














                   Sub Prime()

                Dim Last_Row1 As Long, Last_Row2 As Long
                Dim ws1 As Worksheet, ws2 As Worksheet
                Set ws1 = Sheets("Enter DATA here")
                Set ws2 = Sheets("DATA")
                Application.ScreenUpdating = False
                Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
                Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
                ws1.Range("D21:O" & Last_Row1-4).Copy ws2.Range("A" & Last_Row2)
                Application.ScreenUpdating = True

                End Sub





                share|improve this answer




























                  0














                     Sub Prime()

                  Dim Last_Row1 As Long, Last_Row2 As Long
                  Dim ws1 As Worksheet, ws2 As Worksheet
                  Set ws1 = Sheets("Enter DATA here")
                  Set ws2 = Sheets("DATA")
                  Application.ScreenUpdating = False
                  Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
                  Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
                  ws1.Range("D21:O" & Last_Row1-4).Copy ws2.Range("A" & Last_Row2)
                  Application.ScreenUpdating = True

                  End Sub





                  share|improve this answer


























                    0












                    0








                    0







                       Sub Prime()

                    Dim Last_Row1 As Long, Last_Row2 As Long
                    Dim ws1 As Worksheet, ws2 As Worksheet
                    Set ws1 = Sheets("Enter DATA here")
                    Set ws2 = Sheets("DATA")
                    Application.ScreenUpdating = False
                    Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
                    Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
                    ws1.Range("D21:O" & Last_Row1-4).Copy ws2.Range("A" & Last_Row2)
                    Application.ScreenUpdating = True

                    End Sub





                    share|improve this answer













                       Sub Prime()

                    Dim Last_Row1 As Long, Last_Row2 As Long
                    Dim ws1 As Worksheet, ws2 As Worksheet
                    Set ws1 = Sheets("Enter DATA here")
                    Set ws2 = Sheets("DATA")
                    Application.ScreenUpdating = False
                    Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
                    Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
                    ws1.Range("D21:O" & Last_Row1-4).Copy ws2.Range("A" & Last_Row2)
                    Application.ScreenUpdating = True

                    End Sub






                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    answered Jan 2 at 16:15









                    jps17183jps17183

                    155




                    155























                        0














                        You can use Offset function to exclude the last 4 rows.



                        Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).offset(-4,0).Row 


                        When you copy the range, you should delete row indicator "21" behind column "O", otherwise, the code would not select till the last row. Like following:



                        ws1.Range("D21:O" & Last_Row1).Copy ws2.Range("A" & Last_Row2)





                        share|improve this answer




























                          0














                          You can use Offset function to exclude the last 4 rows.



                          Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).offset(-4,0).Row 


                          When you copy the range, you should delete row indicator "21" behind column "O", otherwise, the code would not select till the last row. Like following:



                          ws1.Range("D21:O" & Last_Row1).Copy ws2.Range("A" & Last_Row2)





                          share|improve this answer


























                            0












                            0








                            0







                            You can use Offset function to exclude the last 4 rows.



                            Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).offset(-4,0).Row 


                            When you copy the range, you should delete row indicator "21" behind column "O", otherwise, the code would not select till the last row. Like following:



                            ws1.Range("D21:O" & Last_Row1).Copy ws2.Range("A" & Last_Row2)





                            share|improve this answer













                            You can use Offset function to exclude the last 4 rows.



                            Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).offset(-4,0).Row 


                            When you copy the range, you should delete row indicator "21" behind column "O", otherwise, the code would not select till the last row. Like following:



                            ws1.Range("D21:O" & Last_Row1).Copy ws2.Range("A" & Last_Row2)






                            share|improve this answer












                            share|improve this answer



                            share|improve this answer










                            answered Jan 2 at 16:23









                            G.CG.C

                            32




                            32






























                                draft saved

                                draft discarded




















































                                Thanks for contributing an answer to Stack Overflow!


                                • Please be sure to answer the question. Provide details and share your research!

                                But avoid



                                • Asking for help, clarification, or responding to other answers.

                                • Making statements based on opinion; back them up with references or personal experience.


                                To learn more, see our tips on writing great answers.




                                draft saved


                                draft discarded














                                StackExchange.ready(
                                function () {
                                StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54009244%2fcopy-data-until-last-row-in-a-new-sheet-and-always-exclude-the-last-4-rows%23new-answer', 'question_page');
                                }
                                );

                                Post as a guest















                                Required, but never shown





















































                                Required, but never shown














                                Required, but never shown












                                Required, but never shown







                                Required, but never shown

































                                Required, but never shown














                                Required, but never shown












                                Required, but never shown







                                Required, but never shown







                                PV5DrLZMG4AJfwH8RVOBLGA6T47kyMMnrmf,IyaA5LCM8U cyc8uZoftYCUut8LsW 8
                                MX26 AJjr

                                Popular posts from this blog

                                Monofisismo

                                Angular Downloading a file using contenturl with Basic Authentication

                                Olmecas